Para diagnosticar consultas PostgreSQL repetidas, separe a construção de candidatos do consumo de candidatos. Leia a contagem de execução do scan interno, verifique se o armazenamento em cache evita trabalho repetido e, em seguida, compare reutilização agrupada, inline de CTE ou uma tabela temporária indexada.

Este passo a passo é para um desenvolvedor trabalhando em um Mac que precisa explicar uma consulta de artigo lenta: selecione artigos candidatos uma vez e, em seguida, encontre o maior ID de artigo candidato dentro do tópico de cada artigo. Ele usa linhas sintéticas em todo o processo. O objetivo é identificar onde o trabalho se repete e escolher um formato de consulta que preserve o resultado.

Configurar uma consulta de candidatos reproduzível

Use uma instalação PostgreSQL local existente com psql disponível. Abra o Terminal, execute psql com suas configurações de conexão habituais e mantenha a mesma sessão aberta durante todos os exemplos SQL. O PostgreSQL documenta o psql como seu terminal interativo para enviar consultas e visualizar resultados. psql

Primeiro, verifique a versão do servidor com SHOW server_version;. A documentação atual recuperada para este artigo identifica o PostgreSQL 18 como a versão atual. Os exemplos usam sintaxe do PostgreSQL 12 ou posterior; isso é um limite de recurso, não uma recomendação para usar uma versão mais antiga. O PostgreSQL 12 introduziu inline de CTE e os controles MATERIALIZED e NOT MATERIALIZED. Consultas WITH, Lançamento 12

Execute esta configuração uma vez em uma sessão nova. Ela cria 20.000 artigos sintéticos e um índice para buscas por tópico e ID. O predicado de candidato usado abaixo seleciona os primeiros 2.000 artigos.

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;

Essas tabelas temporárias duram a sessão por padrão. Execute ANALYZE explicitamente após carregá-las: o processo autovacuum do PostgreSQL não analisa tabelas temporárias. CREATE TABLE

Agora capture a linha de base. A subconsulta lateral calcula o maior ID candidato para o tópico do artigo atual. Aqui, "maior ID" é simplesmente a tarefa definida; não implica uma data de publicação.

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;

O MATERIALIZED explícito torna o limite de reutilização do experimento claro. Uma CTE materializada pode ser construída uma vez enquanto seus consumidores leem as linhas armazenadas repetidamente. A documentação do PostgreSQL ilustra como uma CTE materializada e referenciada múltiplas vezes perde o benefício dos índices da tabela subjacente ao pesquisar esse resultado intermediário. Materialização de Common Table Expression

Os exemplos SQL foram inspecionados, mas não foram executados contra um servidor PostgreSQL para este artigo. A aritmética de contagem de linhas sintéticas foi verificada separadamente; nenhum tempo de execução ou planos capturados são apresentados como medições.

Ler o plano para localizar trabalho repetido

Comece no ramo produtor de candidatos e, em seguida, siga cada consumidor. Sob um loop aninhado, identifique a entrada externa e a busca interna. O PostgreSQL relata o loops como contagem de execução de um nó; linhas reais e tempos de nó são médias por execução. Uma versão com subconsulta escalar correlacionada pode, em vez disso, mostrar trabalho repetido sob SubPlan, então o diagnóstico deve seguir os nós em execução em vez de exigir um rótulo específico. Usando EXPLAIN

Registre quatro observações do seu plano real:

  1. Quantas linhas entram na busca externa?
  2. Quantas vezes o scan interno subjacente é executado?
  3. Esse scan lê candidatos e depois descarta a maioria através de um filtro?
  4. Existe um nó Memoize entre as linhas externas e o trabalho interno caro?

Mantenha os níveis separados. Um agregado pode retornar um máximo por execução enquanto seu filho faz scan de muitas linhas para calculá-lo. Um produtor de candidatos executando uma vez, portanto, não estabelece que o consumo de candidatos é barato.

Para esta amostra exata, 2.000 candidatos abrangem 50 tópicos, com 40 artigos por tópico. O cálculo a seguir descreve padrões de trabalho possíveis, não saída de plano ou tempo de execução prometidos:

Padrão de consumo Exames ilustrativos de linhas candidatas
Scan completo de candidatos para cada artigo externo 2.000 × 2.000 = 4.000.000
Scan completo uma vez por tópico, com todas as buscas posteriores em cache 50 × 2.000 = 100.000
Agrupar candidatos uma vez por tópico 2.000 linhas entrando na etapa de agrupamento, mais trabalho de join subsequente

A distinção importa no PostgreSQL 14 e posterior, que introduziu memoização de resultados de loops aninhados internos. A documentação atual descreve o Memoize como cache de resultados de scans parametrizados e permitindo que o scan subjacente seja ignorado em um acerto de cache; entradas também podem ser despejadas. Inspecione a chave de cache e quaisquer acertos, falhas, despejos e transbordamentos relatados antes de atribuir milhares de linhas externas a milhares de scans completos. Lançamento 14, Planejamento de Consultas

Também compare linhas estimadas com linhas reais no primeiro desvio substancial. O PostgreSQL recomenda verificar essas estimativas e alerta que planos de tabelas de brinquedo não extrapolam necessariamente para entradas maiores. Evite somar tempos de pai e filho: eles descrevem trabalho de execução sobreposto. Usando EXPLAIN

Escolher uma reescrita que corresponda à tarefa repetida

A pergunta útil é quanto trabalho cada consumidor precisa do conjunto de candidatos. Use este auxílio de decisão para selecionar o primeiro experimento:

Requisito observado Primeiro experimento Condição a preservar
Todo candidato precisa do mesmo máximo por tópico Agrupar uma vez e depois fazer join Uma linha de resumo por tópico
Cada consumidor precisa de uma pequena parte indexada da fonte Tentar NOT MATERIALIZED Manter o predicado completo de candidato
Várias declarações precisam de uma relação de candidatos explicitamente indexada Criar uma tabela temporária indexada Manter a mesma sessão e incluir custo de configuração
Parâmetros repetidos já produzem muitos acertos de cache Comparar tempo total de execução antes de mudar o formato Confirmar que os scans subjacentes são realmente caros

Calcular o resultado por tópico uma vez

Para esta tarefa, um resumo agrupado expressa a reutilização diretamente. Execute o seguinte após a configuração, na mesma sessão:

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 reescrita produz um máximo para cada tópico e o anexa a cada artigo candidato. O PostgreSQL define o max como o valor máximo de entrada não nulo. As colunas de tópico e ID não nulas da amostra tornam a equivalência direta: todo candidato pertence a um grupo contendo a si mesmo. Funções de Agregação

Espere 2.000 linhas de resultado ao executar a consulta sem EXPLAIN. Por exemplo, o artigo 1 pertence ao tópico 2 e recebe highest_id = 1951; o tópico 1 recebe highest_id = 2000. Estas são consequências determinísticas do gerador de amostras.

Verifique o significado do resultado antes de generalizar. Agrupar por colunas extras pode criar múltiplas linhas de resumo por tópico e multiplicar a saída do join. Permitir IDs de tópico nulos também exige revisitar a semântica de igualdade. Se a tarefa exigir o título do artigo de maior ID, fazer join do máximo de volta às linhas candidatas é um passo adicional; tomar um máximo separado do título responderia a uma pergunta diferente.

A reescrita permite um plano diferente, mas não exige um hash join nem garante uma aceleração. Compare o trabalho de agrupamento e join medido com as execuções internas reais da linha de base.

Testar se índices da fonte podem substituir scans intermediários

Para uma CTE elegível, substitua AS MATERIALIZED na consulta completa de linha de base por AS NOT MATERIALIZED e execute-a novamente. Isso permite otimização conjunta com a relação da fonte, potencialmente trocando scans de candidatos armazenados por buscas na fonte. Também pode duplicar o cálculo de candidatos. Materialização de Common Table Expression

O índice (topic_id, id) da amostra corresponde à igualdade na coluna tópico principal e uma restrição na coluna ID. Isso o torna um índice razoável para testar; verifique se o plano real o usa e quanto ele faz scan. O PostgreSQL documenta igualdade na coluna principal e desigualdade na próxima coluna como condições que limitam um scan B-tree multicoluna. Índices Multicoluna

Preserve o id <= 2000 dentro da definição do candidato. Buscar o máximo em todas as 20.000 linhas da fonte mudaria a resposta. Também preserve o limite documentado: NOT MATERIALIZED é ignorado para CTEs recursivas e CTEs que não são livres de efeitos colaterais. Para uma CTE inelegível, use uma reescrita relacional suportada ou faça stage explícito do resultado. SELECT

Fazer stage de uma relação de candidatos indexada quando a reutilização abrange declarações

Uma CTE pertence a uma declaração. Se várias declarações precisam das mesmas linhas de candidatos e índices explícitos, uma tabela temporária fornece esse tempo de vida e escolha de indexação diferentes. Execute esta alternativa após a configuração 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;

Índices de tabelas temporárias também são temporários, e a relação tem escopo de sessão. Inclua criação, construção de índice e análise ao avaliar se o staging ajuda a tarefa completa. Não compare apenas sua busca final contra a operação não estagiada inteira. CREATE TABLE

Capturar o mesmo experimento com node-postgres no seu Mac

Para inspecionar o caminho de consulta da aplicação, use um lançamento Node.js atualmente suportado e instale o pacote documentado com npm install pg. Salve o programa completo abaixo como diagnose.mjs nesse projeto e execute node diagnose.mjs. Ele cria sua própria entrada sintética, então não depende da sessão psql anterior. node-postgres

O programa usa seu ambiente de conexão PostgreSQL existente. O node-postgres documenta as mesmas variáveis de ambiente de conexão usadas pelo psql, incluindo PGHOST, PGPORT e PGDATABASE. Conectando

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();
}

A configuração da consulta usa os campos text e values documentados. Adicionar um name cria uma declaração preparada em cada conexão; não armazena linhas de candidatos em cache nem remove execução interna repetida. Consultas

Se uma aplicação existente usa um pool, mantenha a criação e consumo de tabela temporária em um cliente verificado, depois libere-o em finally. Chamadas pool.query separadas podem usar clientes diferentes. Da mesma forma, o node-postgres exige que todas as declarações em uma transação usem a mesma instância de cliente. Pooling, Transações

Para declarações nomeadas cujo comportamento muda com valores de parâmetros, investigue o planejamento separadamente. O PostgreSQL pode escolher planos genéricos ou personalizados específicos de parâmetros; sua rota de inspeção documentada é EXPLAIN EXECUTE para a declaração preparada. Uma consulta sem nome recém-explicada pode, portanto, diferir de uma execução preparada estabelecida. PREPARE

Decidir se o trabalho repetido realmente melhorou

Use o mesmo predicado de candidato, colunas selecionadas e valores de entrada para cada comparação. Execute uma passagem inicial e repita passagens comparáveis para observar variação de cache. Registre a versão do servidor, contagem de candidatos, execuções de scan interno, comportamento de cache e tempo total de execução. O PostgreSQL 17 melhorou o planejamento de CTE usando estatísticas de coluna e ordem de classificação, então retenha a versão ao comparar resultados de máquinas diferentes. Lançamento 17

Para uma segunda passagem de diagnóstico, o EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) retém contagens de linhas e tempo de execução da declaração enquanto evita medições de relógio por nó. Totais de buffer em nós pai incluem atividade de filho, então não some a árvore inteira. EXPLAIN ANALYZE executa a consulta, mas não mede a transmissão de suas linhas de resultado para o Node.js. EXPLAIN

Trate interruptores do planejador como experimentos. Desativar o enable_nestloop desencoraja loops aninhados, mas não pode eliminá-los totalmente; o enable_material também não pode suprimir toda materialização. Nenhum interruptor estabelece que o consumo de candidatos se tornou eficiente. Planejamento de Consultas

Termine com o formato de consulta que preserva as linhas necessárias e reduz o trabalho medido para a tarefa completa. Para este máximo por tópico, a reutilização agrupada é o primeiro experimento mais claro. Mantenha buscas indexadas quando provarem ser baratas e use staging explícito quando sua configuração adicional justificar seu lugar entre consumidores repetidos.

Fontes