PostgreSQL Query Lenta: Diagnóstico y Optimización

Para encontrar y optimizar las queries lentas en PostgreSQL, habilita la extensión pg_stat_statements para identificar los tiempos de ejecución y usa EXPLAIN ANALYZE para leer el plan de ejecución. Agrega los índices faltantes con CREATE INDEX CONCURRENTLY y mantén las estadísticas actualizadas con VACUUM ANALYZE.

|—|—|—|—|

Cuando analicé la base de datos de una organización sanitaria pública con 2.000 endpoints, las API internas tardaban 4 segundos en responder. Los operadores esperaban minutos para cargar un historial clínico. El culpable eran cinco queries sin índices que ejecutaban sequential scan sobre tablas de 5 millones de filas. Tras aplicar estas técnicas, los tiempos de respuesta bajaron a 200 ms. El 40% de los problemas de rendimiento en bases de datos relacionales proviene de queries no optimizadas e índices faltantes (Gartner 2025). Aquí tienes cómo solucionarlos de raíz.

Requisitos Previos y Entorno de Prueba

Entorno de prueba: PostgreSQL 16 sobre un servidor dedicado con 16 GB de RAM y Rocky Linux 9. Necesitas acceso de superusuario para modificar postgresql.conf y crear extensiones. Simulamos una tabla de producción con 5 millones de registros.

El problema: la PostgreSQL query lenta que funciona en dev y falla en producción

El entorno de desarrollo tiene 100 filas de prueba. La producción tiene 5 millones de filas y 50 usuarios concurrentes. Una query que tarda 2 milisegundos en desarrollo puede requerir 30 segundos en producción. La base de datos crece. Las queries degradan de forma silenciosa. Nadie se da cuenta hasta que los usuarios reportan lentitud. El problema se agrava. El optimizador de PostgreSQL basa sus decisiones en las estadísticas. Si las estadísticas son viejas, PostgreSQL elige el plan equivocado.

Identificar queries lentas con pg_stat_statements

Para encontrar las queries lentas, primero debes registrarlas. pg_stat_statements es la extensión oficial de PostgreSQL. Rastrea cada query ejecutada y guarda sus tiempos de ejecución.

Habilita la extensión en el archivo postgresql.conf:

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all

Establece un umbral para registrar las queries que superan un segundo:

log_min_duration_statement = 1000

Reinicia PostgreSQL. Crea la extensión en la base de datos:

CREATE EXTENSION pg_stat_statements;

Ahora encuentra las 10 queries más lentas por tiempo medio de ejecución:

SELECT query, calls, total_exec_time/calls AS avg_ms, rows/calls AS avg_rows
FROM pg_stat_statements ORDER BY avg_ms DESC LIMIT 10;

Este comando devuelve el tiempo medio en milisegundos. Busca queries con un alto número de llamadas y un avg_ms elevado. Son tus culpables.

Analizar el plan de ejecución con EXPLAIN ANALYZE

Encontraste la query lenta. Ahora debes entender por qué es lenta. EXPLAIN muestra el plan estimado por PostgreSQL. EXPLAIN ANALYZE ejecuta la query y muestra el plan real con los tiempos efectivos.

Ejecuta el comando sobre tu query problemática:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM ordenes WHERE cliente_id = 123;

Lee la salida de abajo hacia arriba. Busca las filas con un actual time elevado. Si ves Seq Scan, PostgreSQL está leyendo toda la tabla fila por fila. Es la operación más costosa. Busca también Filter. Significa que PostgreSQL lee más filas de las necesarias y las descarta después. La fila Rows Removed by Filter te indica cuántas filas leyó inútilmente.

Presta atención a EXPLAIN ANALYZE. Ejecuta la query de verdad. Si analizas un UPDATE o un DELETE, envuélvelo en una transacción y haz un rollback para no modificar los datos.

Crear índices sin bloquear la producción

Si EXPLAIN ANALYZE muestra un Seq Scan sobre una columna usada en la cláusula WHERE, falta un índice. Antes de crearlo, verifica si ya existe:

SELECT indexname FROM pg_indexes WHERE tablename = 'ordenes';

Si el índice no existe, créalo. En producción, usa siempre CONCURRENTLY. Creas el índice sin bloquear las escrituras sobre la tabla:

CREATE INDEX CONCURRENTLY idx_ordenes_cliente ON ordenes(cliente_id);

La creación concurrente requiere más tiempo, pero no causa downtime. Después de crear el índice, ejecuta de nuevo EXPLAIN ANALYZE. Deberías ver Index Scan en lugar de Seq Scan. El tiempo de ejecución caerá drásticamente.

No todos los sequential scan son un problema. Si PostgreSQL debe leer el 90% de las filas de una tabla, un sequential scan es más rápido que un index scan. Leer el índice y luego acceder a la tabla requiere I/O extra. El optimizador toma esta decisión de forma autónoma. El sequential scan es un problema cuando la query selecciona pocas filas. Si buscas un cliente_id específico en una tabla de 5 millones de filas, un sequential scan es un desastre. La regla práctica: si la query devuelve menos del 5-10% de las filas totales de la tabla, necesitas un index scan.

Mantener las estadísticas actualizadas con VACUUM ANALYZE

PostgreSQL usa las estadísticas para decidir el plan de ejecución. Si las estadísticas son viejas, las decisiones son equivocadas. El 68% de los desarrolladores usa EXPLAIN ANALYZE regularmente para el tuning (State of PostgreSQL Survey 2025), pero olvida actualizar las estadísticas antes.

ANALYZE recopila las estadísticas. VACUUM recupera el espacio de las filas muertas. Ejecuta ambos:

VACUUM ANALYZE nombre_tabla;

Ejecútalo después de grandes cargas de datos, después de crear o eliminar índices, o si ves una gran diferencia entre las filas estimadas y las reales en EXPLAIN ANALYZE. No esperes al autovacuum. En tablas grandes con muchas escrituras, el autovacuum podría no ejecutarse con la frecuencia necesaria.

Ajustar parámetros de memoria en postgresql.conf

Dos parámetros en postgresql.conf cambian el rendimiento de las queries. shared_buffers define cuánto espacio en RAM usa PostgreSQL para los datos. El valor por defecto es bajo. En un servidor dedicado, establécelo en el 25% de la RAM total.

work_mem define la memoria para las operaciones de ordenación y hash. Si las queries hacen ORDER BY o JOIN sobre grandes conjuntos de datos, un valor bajo obliga a PostgreSQL a escribir en disco. Aumenta work_mem con cautela. Demasiada memoria asignada para 50 conexiones simultáneas agota la RAM.

Establece también effective_cache_size al 75% de la RAM. No asigna memoria, pero indica a PostgreSQL cuánta memoria tiene disponible el sistema operativo para la caché. Ayuda al optimizador a elegir los planes de ejecución correctos.

Errores Comunes y Resolución de Problemas

El error más frecuente es crear índices sin CONCURRENTLY en producción. Bloquea las escrituras sobre la tabla durante toda la creación. Si la tabla tiene millones de filas, el bloqueo dura minutos.

Otro error es ejecutar EXPLAIN sin ANALYZE. EXPLAIN solo muestra una estimación. Si las estadísticas son erróneas, la estimación es errónea. EXPLAIN ANALYZE muestra la realidad.

Si después de crear un índice PostgreSQL sigue usando el sequential scan, comprueba las estadísticas. Ejecuta VACUUM ANALYZE sobre la tabla. El optimizador no usa el índice si las estadísticas le indican que el sequential scan es más rápido.

Las queries con JOIN lentos suelen derivar de columnas sin índice en la condición de unión. Un error común es hacer el JOIN sobre columnas con tipos de datos distintos. Si una es integer y la otra es varchar, PostgreSQL no puede usar el índice y fuerza un sequential scan mediante un cast explícito.

Lee también: Monitorizar Rendimiento de Servidor Linux: 8 Herramientas

Consulta la documentación oficial de pg_stat_statements para profundizar en las métricas disponibles: https://www.postgresql.org/docs/current/pgstatstatements.html

Conclusiones con Puntos Clave Operativos

Para resolver los problemas de rendimiento de PostgreSQL, sigue un método. Primero identifica las queries lentas con pg_stat_statements. Luego analiza el plan de ejecución con EXPLAIN ANALYZE. Crea los índices faltantes de forma concurrente. Mantén las estadísticas actualizadas con VACUUM ANALYZE. Ajusta los parámetros de memoria en postgresql.conf.

No confíes en el ensayo y error. Mide los tiempos antes y después de cada intervención. Una base de datos optimizada reduce los costes de infraestructura y mejora la experiencia de los usuarios.