tablas - Análisis de uso del índice PostgreSQL
postgresql conclusiones (8)
¿Existe alguna herramienta o método para analizar Postgres y determinar qué índices faltantes deberían crearse y qué índices no utilizados deberían eliminarse? Tengo un poco de experiencia haciendo esto con la herramienta "profiler" para SQLServer, pero no conozco una herramienta similar incluida con Postgres.
En el enfoque determinan los índices que faltan ... No. Pero hay algunos planes para facilitar esto en versiones futuras, como pseudo-índices y EXPLAIN legible por máquina.
En la actualidad, deberá 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 sin usar, puede usar algo como lo siguiente para ayudar a identificarlos:
select * from pg_stat_all_indexes where schemaname <> ''pg_catalog'';
Esto ayudará a identificar tuplas leídas, escaneadas, recuperadas.
Esto debería ayudar: Análisis de consultas práticas
Hay múltiples enlaces a scripts que lo ayudarán a encontrar índices no utilizados en la wiki de PostgreSQL . La técnica básica es mirar pg_stat_user_indexes y buscar aquellos en los que idx_scan , el recuento de cuántas veces ese índice se ha utilizado para responder consultas, es cero, o al menos muy bajo. Si la aplicación ha cambiado y es probable que un índice usado anteriormente no sea ahora, a veces tiene que ejecutar pg_stat_reset() para volver todas las estadísticas a 0 y luego recopilar datos nuevos; puede guardar los valores actuales para todo y calcular un delta en su lugar para resolverlo.
Todavía no hay buenas herramientas disponibles para sugerir índices faltantes. Un enfoque es registrar las consultas que está ejecutando y analizar cuáles requieren mucho tiempo para ejecutarse utilizando una herramienta de análisis de registro de consultas como pgFouine o pqa. Consulte " Registrar consultas difíciles " para obtener más información.
El otro enfoque es mirar pg_stat_user_tables y buscar tablas que tengan un gran número de escaneos secuenciales contra ellos, donde seq_tup_fetch es grande. Cuando se usa un índice, el conteo de idx_fetch_tup aumenta. Eso puede guiarte cuando una tabla no está indexada lo suficientemente bien como para responder consultas en su contra.
En realidad, ¿en qué columnas debe indexar? Eso generalmente lleva de nuevo al análisis de logs de consulta de nuevo.
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 then 2%
AND n_live_tup > 10000
AND pg_relation_size(relname :: regclass) > 5000000
ORDER BY relname ASC;
Esto verifica si hay más escaneos de secuencia y luego escaneos 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 faltantes.
El próximo paso sería detectar los í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 ...
Otra herramienta nueva e interesante para analizar PostgreSQL es PgHero . Está más centrado en ajustar la base de datos y hace numerosos análisis y sugerencias.
Verifica las estadísticas. pg_stat_user_tables y pg_stat_user_indexes son los pg_stat_user_indexes para comenzar.
Ver " El recopilador de estadísticas ".
PoWA parece una herramienta interesante para PostgreSQL 9.4+. Recopila estadísticas, las visualiza y sugiere índices. Utiliza la extensión pg_stat_statements .
PoWA es el analizador de cargas de trabajo de PostgreSQL que reúne estadísticas de rendimiento y proporciona cuadros y gráficos en tiempo real para ayudar a monitorear y ajustar sus servidores PostgreSQL. Es similar a Oracle AWR o SQL Server MDW.
Puede usar la consulta a continuación para encontrar el uso del índice y el tamaño del índice:
La referencia se toma de este blog.
SELECT
pt.tablename AS TableName
,t.indexname AS IndexName
,pc.reltuples AS TotalRows
,pg_size_pretty(pg_relation_size(quote_ident(pt.tablename)::text)) AS TableSize
,pg_size_pretty(pg_relation_size(quote_ident(t.indexrelname)::text)) AS IndexSize
,t.idx_scan AS TotalNumberOfScan
,t.idx_tup_read AS TotalTupleRead
,t.idx_tup_fetch AS TotalTupleFetched
FROM pg_tables AS pt
LEFT OUTER JOIN pg_class AS pc
ON pt.tablename=pc.relname
LEFT OUTER JOIN
(
SELECT
pc.relname AS TableName
,pc2.relname AS IndexName
,psai.idx_scan
,psai.idx_tup_read
,psai.idx_tup_fetch
,psai.indexrelname
FROM pg_index AS pi
JOIN pg_class AS pc
ON pc.oid = pi.indrelid
JOIN pg_class AS pc2
ON pc2.oid = pi.indexrelid
JOIN pg_stat_all_indexes AS psai
ON pi.indexrelid = psai.indexrelid
)AS T
ON pt.tablename = T.TableName
WHERE pt.schemaname=''public''
ORDER BY 1;