Auditoría de índices en Postgres con pg_stat: encontrar índices lentos y sin uso

Qué es una auditoría de índices de Postgres (y qué no es)

Una auditoría de índices de Postgres es una puesta en contexto basada en lo que tu base de datos realmente está haciendo en producción. Responde preguntas prácticas: qué consultas son lentas, qué índices se usan, qué índices nunca se tocan y a dónde se va el tiempo.

El objetivo no es coleccionar índices “bonitos de tener”. Es acelerar los caminos más concurridos y reducir costes evitables.

Los índices a menudo parecen correctos en dev porque el tráfico de dev es ordenado: tablas pequeñas, consultas previsibles y unos pocos caminos felices. Producción es más desordenada. Las tablas crecen, los filtros y ordenaciones varían, aparecen casos límite y muchos usuarios golpean los mismos endpoints a la vez. Una consulta que parece “suficientemente rápida” con 10.000 filas puede venirse abajo a 10 millones si empieza a hacer escaneos secuenciales o grandes ordenaciones.

Una buena auditoría te ayuda a:

• Identificar patrones específicos de consultas lentas bajo carga real
• Confirmar qué índices están ayudando hoy
• Encontrar índices que cuestan almacenamiento y ralentizan escrituras sin devolver beneficio
• Decidir qué añadir o ajustar y cómo probar que mejoró

“Estadísticas reales de consulta” significa comportamiento medido por tu workload en vivo: con qué frecuencia se ejecuta una consulta, cuánto tarda, cuántas filas devuelve y si tiene picos. Adivinar solo desde el esquema es arriesgado. Un esquema puede sugerir qué podría indexarse, pero no puede decirte qué filtros usan realmente los usuarios o qué consultas dominan el tiempo de la base.

También ayuda a mantener claros los trade-offs. Los índices no son gratis:

• Aceleran lecturas (SELECT) cuando casan con tus filtros, joins y orden
• Pueden ralentizar escrituras (INSERT/UPDATE/DELETE) porque cada cambio debe actualizar índices
• Incrementan almacenamiento y trabajo de mantenimiento (vacuum, bloat, backups)

Por eso una auditoría no es “añadir índices por todas partes”. Es “añadir los correctos, eliminar el desperdicio y verificar los resultados con estadísticas”. Esto aparece mucho en bases de código generadas por IA (por ejemplo de Cursor, Replit, o v0), donde las consultas suelen entrar sin un indexado cuidadoso. La app funciona en una demo y luego el tráfico de producción encuentra rápidamente los caminos lentos.

Antes de empezar: asegúrate de que tus estadísticas son fiables

Una auditoría de índices solo es tan buena como las estadísticas que la respaldan. Si tu base de datos se reinició, tuvo un failover o las estadísticas se reiniciaron, puedes acabar añadiendo el índice equivocado o eliminando uno útil.

Primero, confirma que puedes leer las vistas que necesitas. La mayoría de señales vienen de vistas del sistema como pg_stat_*. En Postgres gestionado puede que no seas superuser, pero normalmente puedes leer las vistas comunes de pg_stat con un rol orientado a monitorización.

Antes de sacar números, haz algunas comprobaciones rápidas:

• Asegúrate de poder consultar pg_stat_database, pg_stat_user_tables y pg_stat_user_indexes sin errores.
• Verifica si las estadísticas se han reiniciado recientemente (tras un reinicio, failover o reset manual).
• Confirma que las consultas de tu app no están en su mayoría evitando Postgres (una capa de caché puede esconder la carga real de la BD).
• Ejecuta la auditoría en la misma base de datos que atiende tráfico real.

Si puedes usarlo, pg_stat_statements hace la auditoría mucho más fiable porque agrega comportamiento real de consulta a lo largo del tiempo (calls, tiempo total, tiempo medio). Debe instalarse y activarse con antelación, y solo trackea consultas después de que empieza a recolectar.

Elige una ventana de observación que coincida con cómo se usa la app. Un valor por defecto razonable son unos pocos ciclos reales de negocio, incluyendo horas pico.

Evita usar una ventana distorsionada a menos que la distorsión sea el problema que quieres arreglar. No hagas una auditoría justo después de:

• Un despliegue mayor que cambió patrones de consulta
• Un backfill o migration que inundó la base
• Una importación masiva que no representa tráfico normal
• Un incidente donde el sistema estuvo degradado

Una trampa común: una nueva funcionalidad provoca un bucle N+1. Tus estadísticas entonces pedirán índices a gritos, cuando la solución real es parar la explosión de consultas.

Las vistas pg_stat en las que te apoyarás (mapa rápido)

Una auditoría práctica parte de un principio: confía en lo que tu base de datos ha observado bajo tráfico real.

Estas vistas cubren la mayoría de lo que necesitas:

• pg_stat_statements: un marcador de queries normalizadas con totales y promedios de tiempo, llamadas y filas. Aquí encuentras qué patrones de consulta consumen más latencia.
• pg_stat_user_tables: cómo se accede a cada tabla. La señal clave es si se accede mayormente por índices o por escaneos secuenciales.
• pg_stat_user_indexes: con qué frecuencia se usa cada índice. Útil para detectar índices que parecen importantes pero tienen uso cercano a cero.
• pg_statio_user_tables: si las lecturas de tabla vienen de caché o disco. Lecturas de disco altas suelen correlacionar con lentitud visible por el usuario.
• pg_statio_user_indexes: la misma vista caché vs disco, pero para páginas de índice.

Un malentendido común: conteos de uso e impacto no son lo mismo.

• Los conteos de uso (como “cuántas veces se escaneó un índice”) muestran popularidad, no tiempo ahorrado.
• Señales de tiempo y I/O (de pg_stat_statements y pg_statio_*) muestran el dolor.

Un buen flujo es: encontrar formas de consulta caras en pg_stat_statements, luego usar estadísticas de tabla e índice para entender por qué esas consultas son lentas (escaneos de tabla, lecturas de disco, baja selectividad, etc.).

Recuerda también que las estadísticas no son permanentes. Se reinician en un reinicio y se pueden resetear manualmente. “No usado” a veces solo significa “no usado desde el último reset”, así que asegúrate de que tu ventana cubra tráfico normal.

Paso a paso: ejecutar una auditoría simple de índices en 30–60 minutos

Esta es una auditoría rápida basada en señales de tráfico real. Si heredaste una app donde las páginas van lentas al azar o timeouts, estas instantáneas te ayudan a centrarte en lo que los usuarios realmente golpean.

Paso 1: Obtén tus consultas más lentas (por tiempo total y tiempo medio).

-- Requires pg_stat_statements
-- Postgres 13+ uses *_exec_time columns
SELECT
  queryid,
  calls,
  total_exec_time AS total_ms,
  mean_exec_time  AS mean_ms,
  rows,
  shared_blks_read,
  shared_blks_hit,
  left(query, 120) AS query_sample
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

SELECT
  queryid,
  calls,
  total_exec_time AS total_ms,
  mean_exec_time  AS mean_ms,
  rows,
  shared_blks_read,
  shared_blks_hit,
  left(query, 120) AS query_sample
FROM pg_stat_statements
WHERE calls >= 20
ORDER BY mean_exec_time DESC
LIMIT 20;

Usa la primera consulta para encontrar qué consume más tiempo total durante el día. Usa la segunda para hallar sentencias lentas que puedan disparar la latencia del usuario.

Paso 2: Busca tablas que hagan muchas lecturas secuenciales. Estas son candidatas comunes a “índice faltante o forma de consulta errónea”.

SELECT
  schemaname,
  relname,
  seq_scan,
  seq_tup_read,
  idx_scan,
  n_live_tup,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC
LIMIT 20;

Paso 3: Lista índices que no se usan o se usan muy poco. Índices grandes con idx_scan bajo son sospechosos.

SELECT
  schemaname,
  relname      AS table_name,
  indexrelname AS index_name,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC
LIMIT 30;

Paso 4: Detecta tablas con muchas escrituras donde índices extra pueden ralentizarte. Cada índice adicional obliga a más trabajo en inserts y updates.

SELECT
  schemaname,
  relname,
  n_tup_ins,
  n_tup_upd,
  n_tup_del,
  n_tup_hot_upd,
  (n_tup_ins + n_tup_upd + n_tup_del) AS total_writes
FROM pg_stat_user_tables
ORDER BY total_writes DESC
LIMIT 20;

Paso 5: Guarda snapshots con timestamps para comparar antes y después. Almacena resultados en un lugar consistente (CSV o una tabla pequeña) y toma notas de lo que cambió.

CREATE SCHEMA IF NOT EXISTS audit;
CREATE TABLE IF NOT EXISTS audit.slow_queries_snap (
  captured_at timestamptz NOT NULL,
  queryid     bigint,
  calls       bigint,
  total_ms    double precision,
  mean_ms     double precision,
  query_sample text
);

INSERT INTO audit.slow_queries_snap
SELECT
  now(), queryid, calls,
  total_exec_time, mean_exec_time,
  left(query, 200)
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 50;

Cómo detectar índices faltantes usando señales de tráfico real

Un índice faltante suele aparecer como dos cosas a la vez:

• Una tabla grande siendo escaneada repetidamente
• Un pequeño conjunto de patrones de consulta repetidos y costosos

Empieza por señales a nivel de tabla. En pg_stat_user_tables, un seq_scan alto (especialmente si va acompañado de seq_tup_read alto) en una tabla grande es una fuerte pista de que las consultas filtran o hacen joins sin índice. No es prueba, pero indica dónde mirar.

Luego conéctalo a patrones de consulta en pg_stat_statements. Buscas accesos repetidos:

• Filtros frecuentes en WHERE (por ejemplo user_id, created_at, tenant_id)
• Claves de JOIN (foreign keys son causas frecuentes)
• Columnas en ORDER BY que obligan a ordenar grandes conjuntos
• Consultas con LIMIT que aún leen mucho porque no pueden usar un índice

Una regla práctica: indexa primero columnas selectivas. Una columna selectiva tiene muchos valores distintos, así que reduce la búsqueda rápido. WHERE user_id = 42 suele ser selectiva. WHERE status = 'active' a menudo no lo es.

Índices multicolumna pueden ser la solución, pero el orden importa porque Postgres usa la parte más a la izquierda de un índice btree con mayor eficacia. Si tu consulta habitual es:

SELECT * FROM orders
WHERE user_id = $1 AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;

Un índice como (user_id, created_at) coincide con ese patrón. El inverso (created_at, user_id) suele ayudar menos porque no filtra por usuario desde el principio.

Cómo encontrar índices no usados (y cuándo no eliminarlos)

Los índices no usados son traicioneros: añaden coste en escrituras, ocupan disco y hacen más pesados vacuum y backups. La meta no es borrar índices agresivamente. Es eliminar los que nunca ayudan a tu workload real.

Empieza con pg_stat_user_indexes. Una primera pasada básica es:

• idx_scan = 0 desde el último reset de estadísticas (candidato)
• Escaneos muy bajos comparados con cuánto se escribe la tabla (candidato)

Luego haz una comprobación de sentido. Algunos índices existen por corrección, no por rendimiento:

• Índices que respaldan PRIMARY KEY o UNIQUE
• Índices críticos para checks de foreign key
• Índices utilizados para reglas de aplicación (por ejemplo, “una suscripción activa por usuario”)
• Índices necesarios para jobs raros pero importantes (reportes, exportaciones, forense)

Un error clásico: ves idx_scan = 0 en orders_created_at_idx, lo borras y después descubres que la conciliación de fin de mes dependía de él. Si miras solo una semana de estadísticas, puedes tomar la decisión equivocada.

Un proceso cauteloso que funciona bien:

1. Haz una lista corta de candidatos (tabla, índice, por qué crees que está sin usar).
2. Revisa después de un ciclo de negocio completo (a menudo 2–4 semanas).
3. Si sigue sin uso, elimínalo en una ventana planificada y observa latencia y tiempos de escritura.
4. Guarda la definición del índice para recrearlo rápidamente si hace falta.

Encontrar los mayores responsables de latencia por estadísticas de consultas

Si quieres la mayor ganancia rápido, ordena las consultas reales por cuánto tiempo consumen en total. pg_stat_statements te ayuda a ver lo que es costoso durante el día, no solo lo que es lento una vez.

Dos patrones para comparar:

• Una consulta muy lenta (2 segundos) que se ejecuta dos veces al día
• Una consulta “media” (40 ms) que se ejecuta 10.000 veces al día

El segundo normalmente duele más.

Aquí tienes un punto de partida simple. Los nombres de columna varían por versión de Postgres (puedes ver total_time/mean_time en lugar de total_exec_time/mean_exec_time):

SELECT
  queryid,
  calls,
  total_exec_time,
  mean_exec_time,
  rows,
  shared_blks_hit,
  shared_blks_read,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Luego vuelve a ordenar por tiempo medio para detectar sentencias lentas “uno a uno”:

SELECT
  queryid,
  calls,
  total_exec_time,
  mean_exec_time,
  rows,
  shared_blks_hit,
  shared_blks_read,
  query
FROM pg_stat_statements
WHERE calls > 50
ORDER BY mean_exec_time DESC
LIMIT 20;

Para cada consulta candidata, captura algunos datos antes de cambiar índices:

• Texto de la consulta y queryid
• Calls, tiempo total, tiempo medio
• Filas devueltas (¿está devolviendo muchas más de las que la app necesita?)
• shared_blks_hit vs shared_blks_read (¿mayormente cacheado o con muchas lecturas de disco?)
• Un rápido EXPLAIN (ANALYZE, BUFFERS) en un entorno seguro

Los índices no siempre son la solución. La alta latencia también viene de joins malos que multiplican filas, paginación ausente, traer blobs JSON enormes o hacer demasiado trabajo dentro de SQL.

Validar y diseñar el índice correcto (sin adivinar)

Después de señalar una consulta lenta, no crees un índice inmediatamente. Primero confirma qué está haciendo la base de datos para la forma de consulta de producción. La forma más rápida es probar la consulta exacta (con parámetros realistas) usando EXPLAIN (ANALYZE, BUFFERS).

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM orders
WHERE user_id = 42
  AND status = 'open'
ORDER BY created_at DESC
LIMIT 20;

Léelo como una historia:

• Seq Scan significa que Postgres está leyendo gran parte de la tabla y filtrando después. Eso está bien en tablas pequeñas, pero suele ser lento en tablas grandes.
• Index Scan o Index Only Scan significa que Postgres puede saltar a las filas que necesita.
• BUFFERS muestra cuánto trabajo ocurrió en memoria vs disco, que a menudo explica por qué una consulta es rápida a veces y lenta en picos.

Cuando diseñes un índice, haz que coincida con cómo la consulta filtra y ordena:

• Índice de una columna cuando una columna filtra la mayor parte (por ejemplo user_id).
• Índice compuesto cuando filtras por varias columnas juntas (por ejemplo (user_id, status)). El orden importa.
• Índice parcial cuando siempre consultas un subconjunto (por ejemplo solo status = 'open'). Índice más pequeño y menos coste en escritura.
• Incluir la columna de orden cuando evita una gran ordenación (por ejemplo (user_id, status, created_at)).

Mantén los cambios pequeños y reversibles:

1. Añade un índice que apunte a una consulta de alta latencia.
2. Vuelve a ejecutar EXPLAIN (ANALYZE, BUFFERS) para confirmar que el plan mejoró.
3. Mide otra vez durante una ventana comparable.
4. Si el tiempo de escritura o lock empeora, revierte.

Si heredaste código generado por IA, la consulta suele ser el problema real (filtros faltantes, traer demasiadas filas, ordenar conjuntos enormes). Corrige la consulta primero cuando ese sea el caso.

Errores comunes que hacen que las auditorías de índices salgan mal

Las auditorías fallan cuando tratas una estadística como “la verdad” y actúas demasiado rápido.

Errores comunes:

• Eliminar un índice que protege la corrección. Índices que respaldan PRIMARY KEY o UNIQUE importan aunque parezcan “no usados”. Algunos índices también son críticos para checks de foreign key.
• Perseguir idx_scan sin mirar tiempo y filas. Un conteo de escaneos bajo no significa automáticamente “seguro para borrar”, y un conteo alto no significa automáticamente “hay que conservarlo”.
• Añadir índices solapados para cada consulta ligeramente diferente. Esto lleva a duplicados cercanos, mayor coste de escritura y más mantenimiento.
• Culpar a índices cuando el verdadero problema es bloat o estadísticas obsoletas. Si autovacuum o analyze están atrasados, los planes pueden degradar y los escaneos volverse más lentos.
• Probar durante horas tranquilas y asumir que mejora en pico. La calidez de la caché, la concurrencia y la presión de escritura cambian el comportamiento de los planes.

Haz un cambio a la vez, mide antes y después bajo carga similar y ten un plan de rollback.

Checklist rápido y siguientes pasos

Una auditoría solo paga si puedes repetirla. Mantén un informe simple que puedas ejecutar hoy y luego vuelve a ejecutarlo tras cada cambio para demostrar la mejora (o detectar una regresión).

Tu informe repetible de 30 minutos

• Captura las principales consultas por tiempo total y por tiempo medio (mantenlo pequeño, como 20 cada una).
• Anota tablas grandes con muchas lecturas secuenciales.
• Mantén una lista corta de índices que parecen no usados, con una razón en una línea.
• Propón hasta tres cambios (añadir, eliminar o ajustar) y vincula cada uno a una consulta o tabla específica.
• Vuelve a ejecutar el mismo informe tras el despliegue y compara tiempo, llamadas, filas y escaneos.

Antes de eliminar nada, haz una comprobación rápida: ¿respalda un primary key o unique, soporta una foreign key o existe para un job raro pero importante?

Cuando necesitas una segunda opinión

Si tu app fue generada por herramientas como Replit, Cursor o v0, los problemas de rendimiento suelen venir de una mezcla de índices faltantes, consultas ineficientes y patrones inseguros. FixMyMess (fixmymess.ai) se centra en diagnosticar y reparar bases de código generadas por IA, incluyendo problemas de consulta y esquema que solo aparecen bajo carga de producción. Si estás atascado, una auditoría dirigida puede ayudarte a elegir el conjunto mínimo de cambios que realmente reduzcan la latencia.