Show de bola, muito obrigado ! *Márcio Ribeiro de Oliveira* * - (69) 9231-1557* *Analista de Tecnologia da Informação - DGTI* *Instituto Federal de Rondônia - Reitoria*
2014-08-26 18:09 GMT-04:00 Bruno Silva <[email protected]>: > > 2014-08-26 19:07 GMT-03:00 Marcio Ribeiro de Oliveira < > [email protected]>: > > Boa Noite, estou querendo consultar todos os index do meu banco, como >> posso fazer, tem algum tipo de comando no postgresql para isso ? > > > WITH table_scans as ( > SELECT relid, > tables.idx_scan + tables.seq_scan as all_scans, > ( tables.n_tup_ins + tables.n_tup_upd + tables.n_tup_del ) as > writes, > pg_relation_size(relid) as table_size > FROM pg_stat_user_tables as tables > ), > all_writes as ( > SELECT sum(writes) as total_writes > FROM table_scans > ), > indexes as ( > SELECT idx_stat.relid, idx_stat.indexrelid, > idx_stat.schemaname, idx_stat.relname as tablename, > idx_stat.schemaname || '.' || idx_stat.indexrelname as indexname, > idx_stat.idx_scan, > pg_relation_size(idx_stat.indexrelid) as index_bytes, > indexdef ~* 'USING btree' AS idx_is_btree > FROM pg_stat_user_indexes as idx_stat > JOIN pg_index > USING (indexrelid) > JOIN pg_indexes as indexes > ON idx_stat.schemaname = indexes.schemaname > AND idx_stat.relname = indexes.tablename > AND idx_stat.indexrelname = indexes.indexname > WHERE pg_index.indisunique = FALSE > ), > index_ratios AS ( > SELECT schemaname, tablename, indexname, > idx_scan, all_scans, > round(( CASE WHEN all_scans = 0 THEN 0.0::NUMERIC > ELSE idx_scan::NUMERIC/all_scans * 100 END),2) as index_scan_pct, > writes, > round((CASE WHEN writes = 0 THEN idx_scan::NUMERIC ELSE > idx_scan::NUMERIC/writes END),2) > as scans_per_write, > pg_size_pretty(index_bytes) as index_size, > pg_size_pretty(table_size) as table_size, > idx_is_btree, index_bytes > FROM indexes > JOIN table_scans > USING (relid) > ), > index_groups AS ( > SELECT 'Never Used Indexes' as reason, *, 1 as grp > FROM index_ratios > WHERE > idx_scan = 0 > and idx_is_btree > UNION ALL > SELECT 'Low Scans, High Writes' as reason, *, 2 as grp > FROM index_ratios > WHERE > scans_per_write <= 1 > and index_scan_pct < 10 > and idx_scan > 0 > and writes > 100 > and idx_is_btree > UNION ALL > SELECT 'Seldom Used Large Indexes' as reason, *, 3 as grp > FROM index_ratios > WHERE > index_scan_pct < 5 > and scans_per_write > 1 > and idx_scan > 0 > and idx_is_btree > and index_bytes > 100000000 > UNION ALL > SELECT 'High-Write Large Non-Btree' as reason, index_ratios.*, 4 as grp > FROM index_ratios, all_writes > WHERE > ( writes::NUMERIC / ( total_writes + 1 ) ) > 0.02 > AND NOT idx_is_btree > AND index_bytes > 100000000 > ORDER BY grp, index_bytes DESC ) > SELECT reason, schemaname, tablename, indexname, > index_scan_pct, scans_per_write, index_size, table_size > FROM index_groups; > > Bruno E. A. Silva. > > > _______________________________________________ > pgbr-geral mailing list > [email protected] > https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral > >
_______________________________________________ pgbr-geral mailing list [email protected] https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
