Compare planos do PostgreSQL associando tempos de execução repetidos às contagens de buffers do nó raiz. Este passo a passo no Terminal do Mac usa linhas sintéticas de artigos para testar se um índice reduz o trabalho no banco de dados, levando em conta os efeitos do cache, que podem tornar uma execução enganosa.

O que você deve medir em conjunto?

Sua tarefa é comparar a mesma consulta de lista de artigos antes e depois de adicionar um índice. Mantenha inalterados as colunas selecionadas, a categoria, a ordenação, o limite e os dados. Registre tanto o tempo de execução decorrido quanto a atividade de buffers necessária para produzir o resultado.

Use EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, FORMAT JSON). A opção ANALYZE executa a consulta; TIMING OFF desativa as medições de tempo de cada nó, mantendo o tempo de execução da instrução completa. A saída legível por máquina facilita a coleta de medições repetidas. PostgreSQL: EXPLAIN

Analise estas medições em conjunto:

Medição O que ela indica
Execution Time Duração da execução no servidor, excluindo o planejamento
Planning Time Tempo gasto gerando e otimizando o plano
Shared Hit Blocks Acessos a buffers atendidos pelo cache compartilhado do PostgreSQL
Shared Read Blocks Blocos trazidos para os buffers compartilhados
Acertos compartilhados mais leituras Uma comparação útil do volume de acessos a buffers compartilhados
Temp Read Blocks e Temp Written Blocks Atividade de arquivos de trabalho temporários, como uma ordenação que excede a memória

Os blocos compartilhados pertencem a tabelas e índices regulares. Os blocos locais pertencem a tabelas e índices temporários; os blocos de arquivos de trabalho temporários formam uma categoria separada. Um nó pai inclui a atividade de buffers dos nós filhos; portanto, use o nó raiz para os totais de execução e examine os nós filhos para localizar o trabalho. Nunca some as contagens de todos os nós. PostgreSQL: EXPLAIN

As contagens de buffers representam acessos, não blocos distintos: um mesmo bloco pode ser contado novamente quando acessado mais de uma vez. Além disso, os custos do planejador são unidades arbitrárias, não milissegundos. Compare os tempos de execução reais com as contagens reais de buffers; use os custos estimados para entender a escolha do planejador. PostgreSQL: Usando EXPLAIN

Uma leitura compartilhada não comprova que o dispositivo de armazenamento do Mac realizou uma leitura física. As estatísticas de E/S do PostgreSQL não distinguem dados buscados no armazenamento de dados já disponíveis no cache de páginas do sistema operacional. Descreva a medição como leituras de buffers compartilhados, a menos que você tenha evidências separadas sobre o armazenamento. PostgreSQL: O sistema cumulativo de estatísticas

Prepare o experimento no Mac e verifique os limites entre versões

Comece com um banco de dados PostgreSQL local em execução e o Node.js com npm disponível no Terminal. Este passo a passo tem como alvo o PostgreSQL 18 e usa o Node.js 22 ou 24. A documentação do node-postgres lista essas versões do Node.js como compatíveis e documenta a instalação com npm install pg. node-postgres

O comando de comparação também aparece na documentação do PostgreSQL 16 e 17. O PostgreSQL 18 inclui automaticamente a saída de buffers com EXPLAIN ANALYZE; especificar BUFFERS deixa a intenção explícita nessas versões. Não presuma que configurações opcionais mais recentes estejam disponíveis em servidores mais antigos. PostgreSQL 16: EXPLAIN, PostgreSQL 17: EXPLAIN, Notas da versão do PostgreSQL 18

Em um novo diretório de trabalho, instale o driver:

mkdir pg-plan-comparison
cd pg-plan-comparison
npm init -y
npm install pg

Use um banco de dados local de prática existente que possa ser aberto pela sua conexão habitual do PostgreSQL. O script usa new Client(), que lê as mesmas variáveis de ambiente de conexão que psql. Por exemplo, se esse banco se chamar plan_lab, execute o script com PGDATABASE=plan_lab. Adicione as configurações existentes de PGHOST ou PGPORT, se necessário. node-postgres: Conexão

O programa cria 100.000 artigos sintéticos dentro de uma transação, compara dois estados do esquema e desfaz a transação. Ele usa uma tabela regular para que a varredura apareça nos buffers compartilhados. O PostgreSQL documenta que ROLLBACK descarta as atualizações da transação. Se o nome da tabela de demonstração já existir, a criação falhará em vez de substituí-la. PostgreSQL: ROLLBACK

Execute uma comparação completa de antes e depois

Salve o programa completo a seguir como compare-plans.mjs. Ele gera seus próprios dados de entrada, realiza um aquecimento não registrado por fase, mantém sete execuções medidas e imprime o primeiro plano medido de cada fase. O gerador de linhas usa a função generate_series documentada pelo PostgreSQL. PostgreSQL: Funções que retornam conjuntos

import pg from 'pg';

const { Client } = pg;
const client = new Client();
const query = `
  SELECT article_id, title, published_at
  FROM plan_compare_articles_demo
  WHERE category_id = $1
  ORDER BY published_at DESC, article_id DESC
  LIMIT $2
`;
const values = [7, 50];
const explain = `
  EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, FORMAT JSON)
  ${query}
`;
const median = values =>
  [...values].sort((a, b) => a - b)[Math.floor(values.length / 2)];

async function measure(label) {
  await client.query(explain, values);
  const runs = [];
  let firstPlan;
  for (let run = 1; run <= 7; run++) {
    const result = await client.query(explain, values);
    const document = result.rows[0]['QUERY PLAN'][0];
    const root = document.Plan;
    firstPlan ??= document;
    const hits = root['Shared Hit Blocks'] ?? 0;
    const reads = root['Shared Read Blocks'] ?? 0;
    runs.push({
      phase: label,
      run,
      execution_ms: document['Execution Time'],
      planning_ms: document['Planning Time'],
      shared_hit: hits,
      shared_read: reads,
      shared_accesses: hits + reads,
      temp_read: root['Temp Read Blocks'] ?? 0,
      temp_written: root['Temp Written Blocks'] ?? 0
    });
  }
  console.table(runs);
  console.log(label, 'median execution ms:',
    median(runs.map(run => run.execution_ms)));
  console.dir(firstPlan, { depth: null });
}

try {
  await client.connect();
  console.log((await client.query('SHOW server_version')).rows[0]);
  await client.query('BEGIN');
  await client.query(`
    CREATE TABLE plan_compare_articles_demo (
      article_id integer PRIMARY KEY,
      category_id integer NOT NULL,
      title text NOT NULL,
      published_at timestamp NOT NULL,
      body text NOT NULL
    )
  `);
  await client.query(`
    INSERT INTO plan_compare_articles_demo
      (article_id, category_id, title, published_at, body)
    SELECT g, g % 20, 'Article ' || g,
           timestamp '2025-01-01' + g * interval '1 minute',
           repeat('Synthetic article text. ', 12)
    FROM generate_series(1, 100000) AS series(g)
  `);
  await client.query('ANALYZE plan_compare_articles_demo');
  const before = await client.query(query, values);
  await measure('before index');

  await client.query(`
    CREATE INDEX plan_compare_articles_demo_list_idx
    ON plan_compare_articles_demo
      (category_id, published_at DESC, article_id DESC)
  `);
  const after = await client.query(query, values);
  if (JSON.stringify(before.rows) !== JSON.stringify(after.rows)) {
    throw new Error('Result mismatch between schema states');
  }
  await measure('after index');
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  try {
    await client.query('ROLLBACK');
  } finally {
    await client.end();
  }
}

Execute-o no mesmo diretório, substituindo pelo nome real do seu banco de dados de prática:

PGDATABASE=plan_lab node compare-plans.mjs

O programa envia SQL e valores de parâmetros separadamente por meio de client.query(text, values). Ele lê o plano da coluna retornada chamada QUERY PLAN; o node-postgres mapeia as colunas do resultado para propriedades de objetos e analisa o JSON do PostgreSQL em objetos JavaScript. node-postgres: Consultas, node-postgres: pg.Result, node-postgres: Tipos de dados

O comando independente ANALYZE coleta estatísticas da tabela para o planejador. Ele difere da opção ANALYZE, que mede a execução dentro de EXPLAIN. Aqui, coletar estatísticas após carregar as linhas sintéticas fornece às duas fases as mesmas informações iniciais sobre a tabela. PostgreSQL: ANALYZE

Os cálculos de mediana e de totais de buffers em JavaScript foram verificados com entradas sintéticas determinísticas. O experimento completo com o banco de dados não foi executado durante a preparação porque o PostgreSQL não estava disponível no ambiente de pesquisa; o artigo não apresenta resultados de benchmarks medidos.

O que deve mudar nos dois planos?

O índice candidato coloca primeiro a categoria filtrada por igualdade, seguida pelas colunas de ordenação solicitadas. O PostgreSQL documenta que condições de igualdade em colunas iniciais de índices B-tree restringem o intervalo do índice percorrido. Isso faz desse índice um candidato fundamentado para a consulta. PostgreSQL: Índices de várias colunas

Antes da indexação, procure uma varredura e uma ordenação; depois, uma varredura ordenada do índice abaixo do limite. Essas são expectativas a verificar na sua saída, não formatos de plano garantidos. Um índice B-tree compatível pode fornecer diretamente as linhas ordenadas, e combinar ordenação com limite pode evitar o processamento dos resultados correspondentes restantes. PostgreSQL: Índices e ORDER BY

Examine três aspectos nos planos impressos:

  1. Confirme que a raiz retorna 50 linhas nas duas fases e que o programa não relata divergências nos resultados.
  2. Encontre os nós responsáveis pela varredura e pela ordenação. Compare as estimativas de linhas com as linhas reais e examine onde ocorre a atividade de buffers.
  3. Compare os acessos compartilhados da raiz e os tempos de execução repetidos. Uma mudança apenas nos nomes dos nós não demonstra uma melhoria útil.

Esta é uma comparação por fases com cache aquecido. A inserção das linhas, as consultas de verificação de correção, os aquecimentos e a criação do índice afetam o estado do cache. Mantenha a mesma carga de trabalho do Mac e a mesma configuração do banco de dados nas duas fases. Preserve as sete observações individuais para que uma execução excepcionalmente lenta continue visível; só a mediana pode ocultar variações.

Para um acompanhamento mais robusto, repita o experimento inteiro e teste outras categorias e limites. Se testar um limite maior, identifique-o como uma carga de trabalho separada. A comparação original responde se este índice ajuda na solicitação específica de 50 artigos nas condições locais observadas.

Como distinguir menos trabalho de buffers mais aquecidos?

Os números a seguir são entradas ilustrativas, não resultados coletados. Eles mostram por que leituras, acertos e tempo devem ser analisados em conjunto:

Execução ilustrativa Tempo de execução Acertos compartilhados Leituras compartilhadas Acertos + leituras
A, observação inicial 12 ms 900 100 1.000
A, observação repetida 10 ms 1.000 0 1.000
B, plano candidato 3 ms 80 0 80

Entre as duas observações de A, as leituras desaparecem, enquanto o total de acessos compartilhados permanece em 1.000. Isso é compatível com uma mudança no estado do cache; não demonstra uma redução no volume de acessos a buffers compartilhados. B combina menor duração com menos acessos. Se esse padrão se repetir com resultados equivalentes, ele sustenta a conclusão mais restrita de que B realiza menos trabalho com buffers compartilhados para esta tarefa.

Use este guia de decisão se seus próprios resultados forem diferentes:

Padrão observado Interpretação e próxima ação
Menor tempo e menos acessos compartilhados Evidência de melhoria para a carga de trabalho testada; repita para verificar a consistência
Menor tempo, menos leituras e total de acessos semelhante O estado do cache pode explicar a mudança; compare repetições com o cache aquecido
Menos acessos, mas tempo semelhante ou maior Examine a ordenação, as expressões e a variação dos tempos antes de decidir
Zero leituras, mas muitos acertos A execução com cache ainda realiza acessos a buffers; examine os nós com maior atividade
Surgem leituras ou gravações temporárias Examine a operação de trabalho que as produziu antes de alterar as configurações

Evite usar a porcentagem de acertos de cache como critério decisivo. Na ilustração, tanto A aquecida quanto B têm uma taxa de acertos de buffers compartilhados de 100%, mas suas contagens de acessos diferem bastante. Mantenha as contagens brutas e o tamanho do resultado ao lado dos tempos.

Como essas medições se relacionam com a velocidade da aplicação?

O Execution Time do servidor exclui o planejamento, enquanto o EXPLAIN ANALYZE comum também omite a transmissão do resultado e, por padrão, a conversão da saída. A instrumentação também introduz sobrecarga. Portanto, o script compara a execução instrumentada no banco de dados, em vez de medir o tempo total para exibir uma lista de artigos. PostgreSQL: Usando EXPLAIN

Para investigar a conversão da saída separadamente no PostgreSQL 17 ou 18, adicione SERIALIZE TEXT às mesmas opções de explicação. O PostgreSQL 16 não lista essa opção. A serialização ainda não transmite as linhas do resultado da consulta, portanto não mede a entrega pela rede. Mantenha as configurações de serialização idênticas nas execuções comparadas. PostgreSQL 17: EXPLAIN, PostgreSQL 16: EXPLAIN

Para uma medição na aplicação, cronometre a SELECT habitual de artigos até a conclusão da chamada aguardada do driver, com a conexão já estabelecida. Registre isso como uma medição separada do tempo decorrido no cliente. Preserve a configuração real de consulta da aplicação: adicionar uma consulta name do node-postgres ativa o comportamento de instrução preparada por conexão e altera as condições da comparação. node-postgres: Consultas

Escolha o candidato quando resultados equivalentes, tempos repetidos e contagens de buffers apresentarem um padrão consistente para a solicitação de artigos que você precisa atender. Guarde os planos completos, as execuções individuais, a versão do servidor e os parâmetros da consulta para que a conclusão continue específica e reproduzível.

Fontes