Ajuste de consultas PostgreSQL

Verificación de afirmaciones — CHECKS.md. Todo lo siguiente se aplica a PostgreSQL estándar. Primero averigua la versión (SHOW server_version): de ella dependen BUFFERS por defecto (PG18), skip scan (PG18) y el valor predeterminado de hash_mem_multiplier (2.0 desde PG15).

Regla principal

No trates al azar. El orden es el siguiente:

dónde se va realmente el tiempo;
qué consulta es la culpable y cuántas veces se llama;
en qué nodo del plan la estimación de filas es incorrecta;
corrección;
verificación por número de páginas con datos de volumen de producción.

Índice, work_mem y random_page_cost tócalos solo si el diagnóstico los señala. Por qué: sin perfil se corrige una consulta «terrible» con una fracción de 1/60 y no se gana nada, mientras que un índice añadido al azar perjudica a una consulta larga.

Dónde se va el tiempo

Queja de «lento»: primero compara el tiempo en la BD con lo que ve el usuario. Si las consultas suman ~100 ms y se quejan de un minuto, la base de datos no tiene la culpa. No toques las consultas, busca en la aplicación, red, frontend.
Operación con muchas consultas: toma un perfil mediante pg_stat_statements.
1. Establece pg_stat_statements.track = all. Por defecto está top, y las consultas dentro de funciones no son visibles.
2. Ejecuta pg_stat_statements_reset(). Se necesita un superusuario o un permiso otorgado sobre la función. El reinicio borra las estadísticas de todos, por lo que debes hacerlo solo en un entorno de pruebas o en un momento tranquilo.
3. Ejecuta la operación una vez y ordena por total_exec_time.
4. Observa calls, shared_blks_hit + shared_blks_read y toplevel. El tiempo de las filas con toplevel = true ya incluye las consultas anidadas. Si sumas todo seguido, el tiempo se cuenta dos veces.
Corrige solo lo que ocupa la mayor parte. La ganancia no es mayor que esa fracción: una subtarea de 1 s de 60 dará como máximo 1/60.
Las consultas en pg_stat_statements están normalizadas: las constantes se reemplazan por $1, las filas no se separan por sesión, se almacenan agregados (media, min/max, stddev). Si parece que el plan depende del valor (cliente grande frente a pequeño), toma valores concretos y analiza EXPLAIN por separado. Con pg_stat_activity y EXPLAIN VERBOSE las filas se vinculan por queryid; para esto se necesita compute_query_id (desde PG14; auto lo habilita cuando está cargado pg_stat_statements).
A través del log: en log_line_prefix se necesitan %p o %c y la hora %m. Las consultas anidadas solo son visibles mediante auto_explain. Establece log_min_duration (por defecto -1, es decir, desactivado), activa log_analyze y log_nested_statements (por defecto off; cambiarlo requiere superusuario o GRANT SET, desde PG15). Analiza el log con pgBadger. Para una sola sesión basta con SET log_min_duration_statement (superusuario o GRANT SET ON PARAMETER) y LOAD 'auto_explain' (solo superusuario).

Con qué datos medir

No saques conclusiones en una base de desarrollo con datos de juguete. El plan depende del tamaño de las tablas y de las estadísticas, y una base pequeña cabe completamente en caché. Si la consulta es rápida en una base pequeña, escribe que eso no prueba nada.
Volumen y distribución como en producción. Si en producción hay datos personales, no copies a un entorno de pruebas. Genera datos sintéticos con la misma forma: cuántas filas en las tablas principales y cómo están distribuidas por clientes. En producción solo accede a estadísticas (pg_stat_statements, pg_class).
No insertes planes con constantes de producción en visualizadores externos: las constantes pueden contener datos personales. Utiliza herramientas locales.

Muchas llamadas rápidas (N+1)

Si la consulta ya es rápida, pero en una sola operación se llama decenas o cientos de veces, no la aceleres. Reescribe la tarea en una sola consulta para todo el lote:
- en lugar de un bucle de consultas por lista de ids en el código — WHERE id = ANY($1) o batch;
- desgloses por categorías — count(*) FILTER (WHERE …);
- para no perder filas sin datos — LEFT JOIN.
Por qué: una consulta de 1 ms ya no se puede acelerar, pero mil de esas llamadas ralentizan. Por ejemplo, 27 llamadas unificadas en una sola consulta leen ≈60 mil páginas en lugar de ≈900 mil.
Si no se puede con una sola consulta, revisa el acceso a los datos en el servicio, no la configuración de la base de datos.

Cómo comparar variantes

La métrica principal son las páginas. Ejecuta EXPLAIN (ANALYZE, BUFFERS) y observa shared hit + read en el nodo superior: allí ya está la suma de los hijos. Suma hit y read, porque su proporción depende del caché. En PG18 BUFFERS se incluye automáticamente con ANALYZE.
El tiempo es solo una referencia. Lo desplazan el caché, la carga, el hardware y la propia medición de ANALYZE. La segunda ejecución siempre es más rápida y no prueba nada. «Se volvió más rápido» escríbelo solo tras varias ejecuciones.
Las páginas no ven la carga de CPU: ordenación en memoria, funciones, JIT, planes paralelos. Si la consulta calcula más de lo que lee, verifica el tiempo por separado.
Si solo necesitas filas y buffers, añade TIMING FALSE: así no habrá sobrecarga de medición.

Análisis del plan

Clase de consulta.
- Corta: devuelve pocas filas, una persona espera la respuesta. Por ejemplo: ficha de objeto, página de lista, búsqueda por clave. Espera acceso por índice y nested loop. Durante el análisis establece SET max_parallel_workers_per_gather = 0.
- Larga: lee tablas casi por completo, con agregación al final. Por ejemplo: informes, analítica, exportaciones. Espera seq scan y hash join; el paralelismo es normal aquí.
Esto es una expectativa, no una ley. Si el plan no coincide con la expectativa, busca la causa en las estimaciones, no añadas un índice.
Estimación frente a realidad. Compara rows con actual rows por nodos. Cuando loops > 1, el valor actual rows se da por una ejecución; multiplícalo por loops. Corrige el nodo más interno donde apareció la discrepancia por primera vez. El error crece hacia arriba en el plan y conduce a un método de acceso y join incorrectos.
Páginas frente a tamaño de tablas (para consultas largas). Ejecuta ANALYZE. Luego compara hit + read del nodo superior con la suma de relpages de las tablas de la consulta en pg_class. Añade índices y TOAST por separado si la consulta los lee.
- Comparable o menor: los datos se leen una vez, el plan es razonable.
- Varias veces mayor: los datos se leen repetidamente, por ejemplo Index Scan dentro de nested loop.
Para consultas cortas por índice este criterio no sirve.
Memoria. Si en BUFFERS hay temp read/written, Sort se realiza en disco o Hash tiene Batches > 1, falta memoria. Qué hacer — en la sección «Qué corregir».

Qué corregir

Primero las estimaciones y la propia consulta, luego la memoria. Los costos cámbialos al final.

Función sobre columna en la condición (starts_at::date = …, date_trunc('day', starts_at) = …, round(col) …). Reescribe en un rango semiabierto starts_at >= $1 AND starts_at < $2. Los límites para timestamptz calcúlalos en la zona horaria del usuario, no del servidor. Por qué: para una expresión sin estadísticas, el planificador toma una selectividad fija (para = es 0.5 % de las filas) y se equivoca mucho. El rango sobre la propia columna se estima mediante estadísticas y usa el índice. Crea un índice o estadísticas sobre la expresión solo si no se puede reescribir.
Verifica qué índice se ha seleccionado. Si el índice es compuesto y la columna filtrada no es la primera, hasta PG18 se lee por completo. En PG18 ayuda skip scan (en el plan — Index Searches: N), pero es beneficioso solo cuando la primera columna tiene pocos valores distintos. De lo contrario, se necesita un índice donde esta columna sea la primera.
Declara FK compuestos (account_id, user_id), incluso si la aplicación lo verifica todo, y escribe el join con todas las columnas del FK mediante igualdades simples. El planificador mediante el FK estima el join sobre varias columnas. Sin FK, considera las columnas independientes y se equivoca mucho (1000 filas en lugar de 100 mil), y luego elige nested loop en lugar de hash. Si el join cubre el FK no por completo (una columna mediante expresión o en WHERE), la estimación mediante FK no se aplica. En la tabla de destino se necesita una clave única sobre las mismas columnas. El costo es gastos adicionales de escritura.
Join superfluo en una consulta larga. Si la tabla no filtra nada y la relación está garantizada por FK y NOT NULL, elimínala: a menudo es la mayor ganancia. No esperes que el planificador elimine un inner join a otra tabla por sí mismo, incluso con FK: elimina LEFT JOIN a una clave única cuyas columnas la consulta no lee, y desde PG18 — la unión de una tabla consigo misma por clave única. Sin la garantía de FK y NOT NULL, no toques ese join: cambiará el resultado.
Las estimaciones son incorrectas y no se puede reescribir la consulta: crea estadísticas extendidas o establece ALTER TABLE … ALTER COLUMN … SET STATISTICS n (por defecto 100).
Falta memoria (archivos temporales, Batches > 1). Aumenta work_mem o hash_mem_multiplier para la sesión o el rol (SET, ALTER ROLE … SET), pero no globalmente: work_mem se asigna por cada nodo en cada sesión. El límite para hash es work_mem × hash_mem_multiplier. Por defecto es 4 MB × 2.0 = 8 MB; hasta PG15 el multiplicador era 1.0. Verifica con SHOW y calcula el límite final.

Hipótesis «el planificador se equivocó»

Verifica en una transacción: BEGIN; SET LOCAL enable_nestloop = off; (o enable_seqscan), luego EXPLAIN (ANALYZE, BUFFERS), luego ROLLBACK. Compara por páginas — así se ve si existe un plan mejor.
No dejes enable_* ni en la configuración ni en el código del servicio. Solo disuaden al planificador de un nodo y afectan a todas las consultas. La documentación los llama un medio temporal tosco.
Luego dos caminos:
- Las estimaciones de filas son incorrectas — corrige las estimaciones (ver «Qué corregir»).
- Las estimaciones son correctas, pero el plan necesario no se selecciona sistemáticamente en varias consultas — cambia los costos. Si es SSD o la base está casi por completo en caché, reduce random_page_cost de 4.0 a 1.1–2, pero no por debajo de seq_page_cost (1.0). En effective_cache_size pon una estimación real: shared_buffers más el caché del SO (por defecto 4 GB). En toda la base cámbialo solo después de verificar en varias consultas.

Fuera de esta habilidad

pg_query_state — requiere un núcleo parcheado.
plprofiler y plpgsql_check — para lógica en PL/pgSQL; aquí la lógica está en la aplicación.
Técnicas que requieren compilaciones comerciales de PostgreSQL.

scary-production/claude-plugins

¿Qué es claude-plugins?

Preguntar en tu IA favorita

Documentación