Skip to content

postgresql table index usage info

postgresql table index usage info published on Комментариев к записи postgresql table index usage info нет
    
   SELECT 
            pg_stat_all_tables.schemaname,relid::regclass AS table, 
            indexrelid::regclass AS index, 
            pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS index_size, 
            pg_stat_user_indexes.idx_tup_read,            
                round (100*(
                    sum( pg_stat_user_indexes.idx_tup_read)/
                    ( sum(pg_stat_user_indexes.idx_tup_read) over ()) ),1) as pct_idx_tup_read,
            pg_stat_user_indexes.idx_tup_fetch,
             round(100*(
                    sum(pg_stat_user_indexes.idx_tup_fetch)/
                    (sum(pg_stat_user_indexes.idx_tup_fetch) over ()) ),1) as pct_idx_tup_fetch,
            pg_stat_user_indexes.idx_scan,
            round(100*(
                    sum(   pg_stat_user_indexes.idx_scan)/
                    (     sum(pg_stat_user_indexes.idx_scan) over ()) ),1) as pct_idx_scan,
            pg_get_indexdef(pg_index.indexrelid) as indexdef
        FROM 
            pg_stat_user_indexes 
            JOIN pg_index USING (indexrelid)
            join pg_stat_all_tables using (relid) 
        WHERE 
             (relid::regclass)::text ='table_name' 
             group by idx_tup_read,pg_stat_all_tables.schemaname,relid,indexrelid,pg_stat_user_indexes.idx_tup_fetch,pg_stat_user_indexes.idx_scan,pg_index.indexrelid
             order by idx_scan desc ;
schemaname |     table      |                   index                    | index_size | idx_tup_read | pct_idx_tup_read | idx_tup_fetch | pct_idx_tup_fetch | idx_scan  | pct_idx_scan |                                                     indexdef
------------+----------------+--------------------------------------------+------------+--------------+------------------+---------------+-------------------+-----------+--------------+------------------------------------------------------------------------------------------------------------------
 public     | fin_bill_steps | fin_bill_steps_bill_id_customer_id_idx     | 1462 MB    |    395213398 |             83.7 |     197898196 |              88.7 | 158647275 |         89.8 | CREATE INDEX fin_bill_steps_bill_id_customer_id_idx ON fin_bill_steps USING btree (bill_id, customer_id)
 public     | fin_bill_steps | fin_bill_steps_bill_id_srv_rendered_id_idx | 5061 MB    |     66673035 |             14.1 |      23200795 |              10.4 |  16720920 |          9.5 | CREATE INDEX fin_bill_steps_bill_id_srv_rendered_id_idx ON fin_bill_steps USING btree (bill_id, srv_rendered_id)
 public     | fin_bill_steps | fin_bill_steps_spec_item_id_idx            | 4999 MB    |      1198864 |              0.3 |       1187406 |               0.5 |   1187537 |          0.7 | CREATE INDEX fin_bill_steps_spec_item_id_idx ON fin_bill_steps USING btree (spec_item_id)
 public     | fin_bill_steps | fin_bill_steps_bill_id_case_id_idx         | 5087 MB    |      8697564 |              1.8 |        748173 |               0.3 |     90822 |          0.1 | CREATE INDEX fin_bill_steps_bill_id_case_id_idx ON fin_bill_steps USING btree (bill_id, case_id)
 public     | fin_bill_steps | fin_bill_steps_bill_id_steps_id_idx        | 5692 MB    |       143034 |              0.0 |          4065 |               0.0 |      4055 |          0.0 | CREATE INDEX fin_bill_steps_bill_id_steps_id_idx ON fin_bill_steps USING btree (bill_id, step_id)
(5 rows)

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Primary Sidebar

Яндекс.Метрика