Solución:
Me gusta esto para encontrar índices faltantes:
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;
Esto comprueba si hay más exploraciones de secuencia que exploraciones de índice. Si la tabla es pequeña, se ignora, ya que Postgres parece preferir los escaneos de secuencia para ellos.
La consulta anterior revela índices que faltan.
El siguiente paso sería detectar índices combinados faltantes. Supongo que esto no es fácil, pero factible. Tal vez analizando las consultas lentas … Escuché que pg_stat_statements podría ayudar …
Consulta las estadísticas. pg_stat_user_tables y pg_stat_user_indexes son los primeros con los que empezar.
Consulte “El recopilador de estadísticas”.
En el enfoque de determinar los índices faltantes … Nop. Pero hay algunos planes para facilitar esto en versiones futuras, como pseudoíndices y EXPLAIN legible por máquina.
Actualmente, necesitará EXPLAIN ANALYZE consultas de bajo rendimiento y luego determinar manualmente la mejor ruta. Algunos analizadores de registros como pgFouine pueden ayudar a determinar las consultas.
En cuanto a un índice no utilizado, puede usar algo como lo siguiente para ayudar a identificarlos:
select * from pg_stat_all_indexes where schemaname <> 'pg_catalog';
Esto ayudará a identificar las tuplas leídas, escaneadas y recuperadas.