Pour savoir si il faut ré-indexé , cet article peut aider : PostgreSQL : bloat, sinon cette requête 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;