Para diagnosticar consultas repetidas de PostgreSQL, separa la creación de candidatos de su consumo. Revisa el número de ejecuciones del escaneo interno, comprueba si el almacenamiento en caché evita trabajo repetido y, después, compara la reutilización agrupada, la inserción en línea de CTE o una tabla temporal con índices.

Esta guía paso a paso está dirigida a un desarrollador que trabaja en un Mac y necesita explicar una consulta lenta de artículos: seleccionar los artículos candidatos una vez y, después, encontrar el ID de artículo candidato más alto dentro del tema de cada artículo. En todo el texto se usan filas sintéticas. El objetivo es identificar dónde se repite el trabajo y elegir una forma de consulta que conserve el resultado.

Configurar una consulta de candidatos reproducible

Usa una instalación local existente de PostgreSQL con psql disponible. Abre Terminal, ejecuta psql con la configuración de conexión habitual y mantén abierta la misma sesión durante todos los ejemplos de SQL. La documentación de PostgreSQL describe psql como su terminal interactiva para enviar consultas y ver resultados. psql

Primero, comprueba la versión del servidor con SHOW server_version;. La documentación actual consultada para este artículo identifica PostgreSQL 18 como la versión más reciente. Los ejemplos usan sintaxis de PostgreSQL 12 o posterior; ese es un límite de compatibilidad, no una recomendación de usar una versión anterior. PostgreSQL 12 introdujo la inserción en línea de CTE y los controles MATERIALIZED y NOT MATERIALIZED. Consultas WITH, Versión 12

Ejecuta esta configuración una vez en una sesión nueva. Crea 20.000 artículos sintéticos y un índice para las búsquedas por tema e ID. El predicado de candidatos que se usa más adelante selecciona los primeros 2.000 artículos.

CREATE TEMP TABLE article_demo (
    id integer PRIMARY KEY,
    topic_id integer NOT NULL,
    title text NOT NULL
);

INSERT INTO article_demo (id, topic_id, title)
SELECT g, 1 + (g % 50), 'Article ' || g
FROM generate_series(1, 20000) AS s(g);

CREATE INDEX article_demo_topic_id_idx
    ON article_demo (topic_id, id);

ANALYZE article_demo;

De forma predeterminada, estas tablas temporales duran lo que dure la sesión. Ejecuta explícitamente ANALYZE después de cargarlas: el proceso autovacuum de PostgreSQL no analiza las tablas temporales. CREATE TABLE

Ahora captura la línea de base. La subconsulta lateral calcula el ID candidato más alto para el tema del artículo actual. Aquí, «ID más alto» es simplemente la tarea definida; no implica una fecha de publicación.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
WITH candidate AS MATERIALIZED (
    SELECT id, topic_id, title
    FROM article_demo
    WHERE id <= 2000
)
SELECT c.id, c.title, m.highest_id
FROM candidate AS c
CROSS JOIN LATERAL (
    SELECT max(x.id) AS highest_id
    FROM candidate AS x
    WHERE x.topic_id = c.topic_id
) AS m;

El MATERIALIZED explícito deja claro el límite de reutilización del experimento. Un CTE materializado puede crearse una vez mientras sus consumidores leen repetidamente las filas almacenadas. La documentación de PostgreSQL ilustra cómo un CTE materializado al que se hace referencia varias veces pierde la ventaja de los índices de la tabla subyacente al buscar en ese resultado intermedio. Materialización de expresiones de tabla comunes

Los ejemplos de SQL se revisaron, pero no se ejecutaron en un servidor PostgreSQL para este artículo. El cálculo aritmético del número de filas sintéticas se verificó por separado; no se presentan tiempos de ejecución ni planes capturados como mediciones.

Leer el plan para localizar el trabajo repetido

Empieza por la rama que produce candidatos y luego sigue cada consumidor. En un bucle anidado, identifica la entrada externa y la búsqueda interna. PostgreSQL muestra loops como el número de ejecuciones de un nodo; las filas reales y los tiempos del nodo son promedios por ejecución. Una versión con una subconsulta escalar correlacionada podría mostrar, en cambio, trabajo repetido bajo SubPlan, así que el diagnóstico debe seguir los nodos que se ejecutan y no exigir una etiqueta concreta. Uso de EXPLAIN

Registra cuatro observaciones del plan real:

  1. ¿Cuántas filas entran en la búsqueda externa?
  2. ¿Cuántas veces se ejecuta el escaneo interno subyacente?
  3. ¿Ese escaneo lee candidatos y luego descarta la mayoría mediante un filtro?
  4. ¿Hay un nodo Memoize entre las filas externas y el trabajo interno costoso?

Mantén separados los distintos niveles. Una agregación podría devolver un máximo por ejecución mientras su nodo secundario recorre muchas filas para calcularlo. Por lo tanto, que un productor de candidatos se ejecute una vez no demuestra que consumir sus resultados sea barato.

En este ejemplo exacto, 2.000 candidatos abarcan 50 temas, con 40 artículos por tema. El siguiente cálculo describe posibles patrones de trabajo, no un resultado de plan ni un tiempo de ejecución garantizados:

Patrón de consumo Examen ilustrativo de filas candidatas
Escaneo completo de candidatos para cada artículo externo 2.000 × 2.000 = 4.000.000
Escaneo completo una vez por tema, con las búsquedas posteriores en caché 50 × 2.000 = 100.000
Agrupar candidatos una vez por tema 2.000 filas que entran en el paso de agrupación, más el trabajo posterior de unión

Esta distinción es importante en PostgreSQL 14 y versiones posteriores, que introdujeron la memorización de resultados de bucles anidados internos. La documentación actual describe Memoize como un mecanismo que almacena en caché los resultados de escaneos parametrizados y permite omitir el escaneo subyacente cuando hay un acierto de caché; también se pueden desalojar entradas. Revisa la clave de caché y los aciertos, fallos, desalojos y desbordamientos que se informen antes de atribuir miles de filas externas a miles de escaneos completos. Versión 14, Planificación de consultas

Compara también las filas estimadas con las filas reales en la primera discrepancia sustancial. PostgreSQL recomienda revisar estas estimaciones y advierte que los planes de tablas de prueba pequeñas no necesariamente se extrapolan a entradas más grandes. Evita sumar los tiempos del nodo padre y sus nodos secundarios: representan trabajo de ejecución superpuesto. Uso de EXPLAIN

Elegir una reescritura que corresponda a la tarea repetida

La pregunta clave es cuánto trabajo necesita cada consumidor del conjunto de candidatos. Usa esta guía para elegir el primer experimento:

Requisito observado Primer experimento Condición que hay que preservar
Cada candidato necesita el mismo máximo por tema Agrupar una vez y luego unir Una fila de resumen por tema
Cada consumidor necesita una pequeña parte indexada de la fuente Probar NOT MATERIALIZED Mantener completo el predicado de candidatos
Varias instrucciones necesitan una relación de candidatos con índices explícitos Crear una tabla temporal con índices Mantener la misma sesión e incluir el costo de configuración
Los parámetros repetidos ya producen muchos aciertos de caché Comparar el tiempo de ejecución total antes de cambiar la forma Confirmar que los escaneos subyacentes realmente sean costosos

Calcular una vez el resultado por tema

Para esta tarea, un resumen agrupado expresa directamente la reutilización. Ejecuta lo siguiente después de la configuración y en la misma sesión:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
WITH candidate AS MATERIALIZED (
    SELECT id, topic_id, title
    FROM article_demo
    WHERE id <= 2000
), topic_max AS (
    SELECT topic_id, max(id) AS highest_id
    FROM candidate
    GROUP BY topic_id
)
SELECT c.id, c.title, m.highest_id
FROM candidate AS c
JOIN topic_max AS m USING (topic_id);

Esta reescritura produce un máximo por cada tema y lo asigna a cada artículo candidato. PostgreSQL define max como el valor máximo de entrada no nulo. Las columnas de tema e ID no nulas del ejemplo hacen que la equivalencia sea sencilla: cada candidato pertenece a un grupo que lo contiene. Funciones de agregación

Espera 2.000 filas de resultado si ejecutas la consulta sin EXPLAIN. Por ejemplo, el artículo 1 pertenece al tema 2 y recibe highest_id = 1951; el tema 1 recibe highest_id = 2000. Son consecuencias deterministas del generador de datos de ejemplo.

Comprueba el significado del resultado antes de generalizar. Agrupar por columnas adicionales podría crear varias filas de resumen por tema y multiplicar el resultado de la unión. Si se permiten ID de tema nulos, también hay que revisar la semántica de igualdad. Si la tarea requiere el título del artículo con el ID más alto, unir el máximo con las filas candidatas es un paso adicional; obtener por separado el título máximo respondería otra pregunta.

La reescritura permite un plan distinto, pero no obliga a usar una unión hash ni garantiza una mejora de velocidad. Compara el trabajo de agrupación y unión medido con las ejecuciones internas reales de la línea de base.

Comprobar si los índices de origen pueden reemplazar los escaneos intermedios

Para un CTE apto, reemplaza AS MATERIALIZED en la consulta de línea de base completa por AS NOT MATERIALIZED y vuelve a ejecutarla. Esto permite optimizar conjuntamente con la relación de origen y, potencialmente, intercambiar los escaneos de candidatos almacenados por búsquedas en el origen. También puede duplicar el cálculo de candidatos. Materialización de expresiones de tabla comunes

El índice (topic_id, id) del ejemplo coincide con una igualdad en la columna inicial del tema y una restricción en la columna de ID. Por eso es un índice razonable para probar; verifica si el plan real lo usa y cuánto recorre. PostgreSQL documenta que la igualdad en la columna inicial y la desigualdad en la siguiente columna son condiciones que limitan un escaneo de árbol B multicolumna. Índices multicolumna

Conserva id <= 2000 dentro de la definición de candidatos. Buscar el máximo entre las 20.000 filas de origen cambiaría la respuesta. Conserva también el límite documentado: NOT MATERIALIZED se ignora en los CTE recursivos y en los CTE que tienen efectos secundarios o no son libres de ellos. Para un CTE no apto, usa una reescritura relacional compatible o almacena explícitamente el resultado en una tabla intermedia. SELECT

Almacenar candidatos en una tabla con índices cuando la reutilización abarca varias instrucciones

Un CTE pertenece a una sola instrucción. Si varias instrucciones necesitan las mismas filas candidatas y los mismos índices explícitos, una tabla temporal ofrece una duración y una opción de indexación distintas. Ejecuta esta alternativa después de la configuración original:

CREATE TEMP TABLE candidate_demo AS
SELECT id, topic_id, title
FROM article_demo
WHERE id <= 2000;

CREATE INDEX candidate_demo_topic_id_idx
    ON candidate_demo (topic_id, id);

ANALYZE candidate_demo;

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT c.id, c.title, m.highest_id
FROM candidate_demo AS c
CROSS JOIN LATERAL (
    SELECT max(x.id) AS highest_id
    FROM candidate_demo AS x
    WHERE x.topic_id = c.topic_id
) AS m;

Los índices de las tablas temporales también son temporales, y la relación tiene alcance de sesión. Al evaluar si el almacenamiento intermedio ayuda, incluye la creación, la construcción de índices y el análisis. No compares solo la búsqueda final con toda la operación sin almacenamiento intermedio. CREATE TABLE

Capturar el mismo experimento con node-postgres en tu Mac

Para inspeccionar la ruta de consulta de la aplicación, usa una versión de Node.js con soporte vigente e instala el paquete documentado con npm install pg. Guarda el programa completo que aparece abajo como diagnose.mjs en ese proyecto y ejecútalo con node diagnose.mjs. Crea su propia entrada sintética, por lo que no depende de la sesión anterior de psql. node-postgres

El programa usa el entorno de conexión de PostgreSQL existente. node-postgres documenta las mismas variables de entorno de conexión que usa psql, incluidas PGHOST, PGPORT y PGDATABASE. Conexión

import pg from 'pg';
const { Client } = pg;
const client = new Client();

try {
  await client.connect();
  const version = await client.query('SHOW server_version');
  console.log(version.rows[0]);

  await client.query(`
    CREATE TEMP TABLE article_demo (
      id integer PRIMARY KEY,
      topic_id integer NOT NULL,
      title text NOT NULL
    );
    INSERT INTO article_demo (id, topic_id, title)
    SELECT g, 1 + (g % 50), 'Article ' || g
    FROM generate_series(1, 20000) AS s(g);
    CREATE INDEX article_demo_topic_id_idx
      ON article_demo (topic_id, id);
    ANALYZE article_demo;
  `);

  const sql = `
    WITH candidate AS MATERIALIZED (
      SELECT id, topic_id, title
      FROM article_demo
      WHERE id <= $1::integer
    )
    SELECT c.id, c.title, m.highest_id
    FROM candidate AS c
    CROSS JOIN LATERAL (
      SELECT max(x.id) AS highest_id
      FROM candidate AS x
      WHERE x.topic_id = c.topic_id
    ) AS m
  `;

  const result = await client.query({
    text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) ${sql}`,
    values: [2000]
  });

  for (const row of result.rows) {
    console.log(row['QUERY PLAN']);
  }
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  await client.end();
}

La configuración de consulta usa los campos documentados text y values. Agregar un name crea una instrucción preparada en cada conexión; no almacena en caché las filas candidatas ni elimina las ejecuciones internas repetidas. Consultas

Si una aplicación existente usa un grupo de conexiones, mantén la creación y el consumo de la tabla temporal en un mismo cliente obtenido del grupo y luego libéralo en finally. Llamadas separadas a pool.query pueden usar clientes distintos. Del mismo modo, node-postgres requiere que todas las instrucciones de una transacción usen la misma instancia de cliente. Agrupación de conexiones, Transacciones

En el caso de las instrucciones con nombre cuyo comportamiento cambia según los valores de los parámetros, investiga la planificación por separado. PostgreSQL puede elegir planes genéricos o planes personalizados para parámetros específicos; la forma documentada de inspeccionarlos es EXPLAIN EXECUTE para la instrucción preparada. Por ello, una consulta sin nombre recién explicada podría diferir de una ejecución preparada establecida. PREPARE

Determinar si el trabajo repetido realmente ha mejorado

Usa el mismo predicado de candidatos, las mismas columnas seleccionadas y los mismos valores de entrada en cada comparación. Haz una primera pasada y repite pasadas comparables para observar las variaciones de caché. Registra la versión del servidor, el número de candidatos, las ejecuciones del escaneo interno, el comportamiento de la caché y el tiempo total de ejecución. PostgreSQL 17 mejoró la planificación de CTE mediante estadísticas de columnas y orden de clasificación, así que registra la versión cuando compares resultados de máquinas diferentes. Versión 17

En una segunda pasada de diagnóstico, EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) conserva los recuentos de filas y el tiempo de ejecución de la instrucción, pero evita las mediciones de reloj por nodo. Los totales de búferes de los nodos padre incluyen la actividad de sus nodos secundarios, así que no sumes todo el árbol. EXPLAIN ANALYZE ejecuta la consulta, pero no mide la transmisión de sus filas de resultado a Node.js. EXPLAIN

Usa los interruptores del planificador como experimentos. Desactivar enable_nestloop desincentiva los bucles anidados, pero no puede eliminarlos por completo; enable_material tampoco puede suprimir toda materialización. Ninguno de los dos interruptores demuestra que el consumo de candidatos se haya vuelto eficiente. Planificación de consultas

Termina con la forma de consulta que conserve las filas requeridas y reduzca el trabajo medido para la tarea completa. Para este máximo por tema, la reutilización agrupada es el primer experimento más claro. Mantén las búsquedas indexadas si se demuestra que son baratas y usa almacenamiento intermedio explícito cuando la configuración adicional compense al reutilizarlo varias veces.

Fuentes