파티션 테이블 정보를 가져오는 PL/pgSQL을 만들어 봤습니다.
정보를 하나하나 찾다 보니 귀찮아서 ...
티스토리가 예전에는 원노트 붙여넣기 하면 이쁘게 잘 붙었는데, 이제는 글꼴도 별로 맘에 안들고 --;
터미널 데이터 긁어서 붙여도 이상하게 나오네요 --;
-- PL/pgSQL
do
$$
declare
v_main record ;
v_parent record ;
v_child record ;
v_index record ;
v_check record ;
v_p_table_oid oid ;
v_loop_main int := 1 ;
v_loop_idx int := 1 ;
v_p_cnt int ;
v_c_cnt int ;
begin
for v_main in (select * from pg_inherits order by inhparent , inhrelid) loop
-- get parent table id
if v_loop_main = 1 then
v_p_table_oid := v_main.inhparent ;
v_loop_main := v_loop_main + 1 ;
select relname
, concat(case relhasrules when 't' then '(inherits with rule)' end, case relhastriggers when 't' then '(inherits with trigger)' end, case when relhasrules = 'f' and relhastriggers = 'f' then '(Declarative)' end ) as addition
into v_parent
from pg_class where oid = v_p_table_oid
and relkind in ('p' , 'r') ;
GET DIAGNOSTICS v_p_cnt = ROW_COUNT;
if v_p_cnt >= 1 then
RAISE NOTICE '% %' , v_parent.relname , v_parent.addition ;
end if;
else
if v_p_table_oid != v_main.inhparent then
v_p_table_oid := v_main.inhparent ;
select relname
, concat(case relhasrules when 't' then '(inherits with rule)' end, case relhastriggers when 't' then '(inherits with trigger)' end, case when relhasrules = 'f' and relhastriggers = 'f' then '(Declarative)' end) as addition
into v_parent
from pg_class where oid = v_p_table_oid
and relkind in ('p' , 'r');
GET DIAGNOSTICS v_p_cnt = ROW_COUNT;
if v_p_cnt >= 1 then
RAISE NOTICE ' ' ;
RAISE NOTICE '% %' , v_parent.relname , v_parent.addition ;
end if;
end if;
end if;
-- get child table info
select *
,(select count(*) from pg_indexes i where tablename = c.relname) as index_cnt
,pg_size_pretty (pg_table_size(c.oid)) as table_size
,pg_size_pretty (pg_indexes_size(c.oid)) as index_size
into v_child
from pg_class c
where relfilenode = v_main.inhrelid
and relkind = 'r' ;
GET DIAGNOSTICS v_c_cnt = ROW_COUNT;
if v_c_cnt >= 1 then
RAISE NOTICE ' |';
RAISE NOTICE ' \-- % : T/I size ( % / % ) pages ( % ) rows ( % ) indexes ( % ) ' , v_child.relname , v_child.table_size, v_child.index_size, v_child.relpages , v_child.reltuples , v_child.index_cnt;
end if ;
-- get index info
if v_child.relhasindex = 't' then
for v_index in (select indexname , indexdef , pg_size_pretty (pg_table_size(c.oid)) as index_size , si.*
from pg_indexes i , pg_class c , pg_stat_all_indexes si
where i.tablename = v_child.relname
and i.indexname = c.relname
and i.indexname = si.indexrelname) loop
if v_loop_idx = 1 then
RAISE NOTICE ' |' ;
v_loop_idx := v_loop_idx + 1 ;
end if;
RAISE NOTICE ' \-- % : size ( % ) idx_scan ( % ) % ' , v_index.indexname , v_index.index_size, v_index.idx_scan, v_index.indexdef ;
end loop;
end if;
v_loop_idx := 1 ;
end loop;
end $$ ;
-- 수행 결과
NOTICE: part_test_tri (inherits with trigger)
NOTICE: |
NOTICE: \-- part_test_tri_202201 : T/I size ( 13 MB / 6816 kB ) pages ( 1676 ) rows ( 310000 ) indexes ( 1 )
NOTICE: |
NOTICE: \-- part_test_tri_202201_idx : size ( 6816 kB ) idx_scan ( 0 ) CREATE INDEX part_test_tri_202201_idx ON public.part_test_tri_202201 USING btree (ci)
NOTICE: |
NOTICE: \-- part_test_tri_202202 : T/I size ( 12 MB / 6168 kB ) pages ( 1514 ) rows ( 280000 ) indexes ( 1 )
NOTICE: |
NOTICE: \-- part_test_tri_202202_idx : size ( 6168 kB ) idx_scan ( 0 ) CREATE INDEX part_test_tri_202202_idx ON public.part_test_tri_202202 USING btree (ci)
NOTICE: |
NOTICE: \-- part_test_tri_202203 : T/I size ( 13 MB / 6816 kB ) pages ( 1676 ) rows ( 310000 ) indexes ( 1 )
NOTICE: |
NOTICE: \-- part_test_tri_202203_idx : size ( 6816 kB ) idx_scan ( 0 ) CREATE INDEX part_test_tri_202203_idx ON public.part_test_tri_202203 USING btree (ci)
NOTICE: |
NOTICE: \-- part_test_tri_202204 : T/I size ( 13 MB / 6600 kB ) pages ( 1622 ) rows ( 300000 ) indexes ( 1 )
NOTICE: |
NOTICE: \-- part_test_tri_202204_idx : size ( 6600 kB ) idx_scan ( 0 ) CREATE INDEX part_test_tri_202204_idx ON public.part_test_tri_202204 USING btree (ci)
NOTICE: |
NOTICE: \-- part_test_tri_202205 : T/I size ( 13 MB / 6600 kB ) pages ( 1622 ) rows ( 300000 ) indexes ( 1 )
NOTICE: |
NOTICE: \-- part_test_tri_202205_idx : size ( 6600 kB ) idx_scan ( 0 ) CREATE INDEX part_test_tri_202205_idx ON public.part_test_tri_202205 USING btree (ci)
NOTICE:
NOTICE: part_test_rule (inherits with rule)
NOTICE: |
NOTICE: \-- part_test_rule_202201 : T/I size ( 13 MB / 0 bytes ) pages ( 1676 ) rows ( 310000 ) indexes ( 0 )
NOTICE: |
NOTICE: \-- part_test_rule_202202 : T/I size ( 12 MB / 0 bytes ) pages ( 1514 ) rows ( 280000 ) indexes ( 0 )
NOTICE: |
NOTICE: \-- part_test_rule_202203 : T/I size ( 13 MB / 0 bytes ) pages ( 1676 ) rows ( 310000 ) indexes ( 0 )
NOTICE: |
NOTICE: \-- part_test_rule_202204 : T/I size ( 13 MB / 0 bytes ) pages ( 1622 ) rows ( 300000 ) indexes ( 0 )
NOTICE: |
NOTICE: \-- part_test_rule_202205 : T/I size ( 13 MB / 0 bytes ) pages ( 1622 ) rows ( 300000 ) indexes ( 0 )
NOTICE:
NOTICE: part_test (Declarative)
NOTICE: |
NOTICE: \-- part_test_202201 : T/I size ( 13 MB / 13 MB ) pages ( 1676 ) rows ( 310000 ) indexes ( 2 )
NOTICE: |
NOTICE: \-- part_test_202201_ci_idx : size ( 6816 kB ) idx_scan ( 0 ) CREATE INDEX part_test_202201_ci_idx ON public.part_test_202201 USING btree (ci)
NOTICE: \-- part_test_202201_cj_idx : size ( 6816 kB ) idx_scan ( 0 ) CREATE INDEX part_test_202201_cj_idx ON public.part_test_202201 USING btree (cj)
NOTICE: |
NOTICE: \-- part_test_202202 : T/I size ( 12 MB / 12 MB ) pages ( 1514 ) rows ( 280000 ) indexes ( 2 )
NOTICE: |
NOTICE: \-- part_test_202202_ci_idx : size ( 6168 kB ) idx_scan ( 0 ) CREATE INDEX part_test_202202_ci_idx ON public.part_test_202202 USING btree (ci)
NOTICE: \-- part_test_202202_cj_idx : size ( 6168 kB ) idx_scan ( 0 ) CREATE INDEX part_test_202202_cj_idx ON public.part_test_202202 USING btree (cj)
NOTICE: |
NOTICE: \-- part_test_202203 : T/I size ( 13 MB / 13 MB ) pages ( 1676 ) rows ( 310000 ) indexes ( 2 )
NOTICE: |
NOTICE: \-- part_test_202203_ci_idx : size ( 6816 kB ) idx_scan ( 0 ) CREATE INDEX part_test_202203_ci_idx ON public.part_test_202203 USING btree (ci)
NOTICE: \-- part_test_202203_cj_idx : size ( 6816 kB ) idx_scan ( 0 ) CREATE INDEX part_test_202203_cj_idx ON public.part_test_202203 USING btree (cj)
NOTICE: |
NOTICE: \-- part_test_202204 : T/I size ( 13 MB / 13 MB ) pages ( 1622 ) rows ( 300000 ) indexes ( 2 )
NOTICE: |
NOTICE: \-- part_test_202204_ci_idx : size ( 6600 kB ) idx_scan ( 0 ) CREATE INDEX part_test_202204_ci_idx ON public.part_test_202204 USING btree (ci)
NOTICE: \-- part_test_202204_cj_idx : size ( 6600 kB ) idx_scan ( 0 ) CREATE INDEX part_test_202204_cj_idx ON public.part_test_202204 USING btree (cj)
NOTICE: |
NOTICE: \-- part_test_202205 : T/I size ( 13 MB / 13 MB ) pages ( 1622 ) rows ( 300000 ) indexes ( 2 )
NOTICE: |
NOTICE: \-- part_test_202205_ci_idx : size ( 6600 kB ) idx_scan ( 0 ) CREATE INDEX part_test_202205_ci_idx ON public.part_test_202205 USING btree (ci)
NOTICE: \-- part_test_202205_cj_idx : size ( 6600 kB ) idx_scan ( 0 ) CREATE INDEX part_test_202205_cj_idx ON public.part_test_202205 USING btree (cj)