반응형

파티션 테이블 정보를 가져오는 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)

반응형

+ Recent posts