Pour savoir si il faut ré-indexé , cet article peut aider : PostgreSQL : bloat, sinon ces deux requêtes va vous aider.
|
SELECT pg_stat_user_indexes.schemaname, -- Origine de 'schemaname' pg_stat_user_tables.relname AS table_name, -- Origine de 'relname' pg_stat_user_indexes.indexrelname AS index_name, -- Origine de 'indexrelname' pg_stat_user_indexes.idx_scan AS number_of_scans, -- Origine de 'idx_scan' pg_stat_user_indexes.idx_tup_read AS tuples_read, -- Origine de 'idx_tup_read' pg_stat_user_indexes.idx_tup_fetch AS tuples_fetched, -- Origine de 'idx_tup_fetch' pg_stat_user_tables.n_tup_ins AS tuples_inserted, -- Origine de 'n_tup_ins' pg_stat_user_tables.n_tup_upd AS tuples_updated, -- Origine de 'n_tup_upd' pg_stat_user_tables.n_tup_del AS tuples_deleted -- Origine de 'n_tup_del' FROM pg_stat_user_indexes JOIN pg_stat_user_tables ON pg_stat_user_indexes.relid = pg_stat_user_tables.relid WHERE pg_stat_user_tables.n_tup_ins + pg_stat_user_tables.n_tup_upd + pg_stat_user_tables.n_tup_del > 1000 ORDER BY (pg_stat_user_tables.n_tup_ins + pg_stat_user_tables.n_tup_upd + pg_stat_user_tables.n_tup_del) DESC; |
| SELECT relname AS TableName, to_char(seq_scan, '999,999,999,999') AS TotalSeqScan, to_char(idx_scan, '999,999,999,999') AS TotalIndexScan, to_char(n_live_tup, '999,999,999,999') AS TableRows, pg_size_pretty(pg_relation_size(relname :: regclass)) AS TableSize FROM pg_stat_all_tables WHERE schemaname = 'public' AND 50 * seq_scan > idx_scan -- more than 2% AND n_live_tup > 10000 AND pg_relation_size(relname :: regclass) > 5000000 ORDER BY relname ASC; |