LIMIT 10 limita o resultado, mas encontrar dez valores distintos pode exigir milhares de linhas de entrada. Este guia mostra como diagnosticar esse trabalho e comparar duas abordagens usando tópicos sintéticos de artigos no Terminal do seu Mac.

Por que dez resultados podem exigir milhares de linhas

Suponha que uma tabela de artigos contenha 100.000 linhas distribuídas por 100 tópicos, com 1.000 artigos por tópico. Sua tarefa é recuperar os dez primeiros nomes de tópicos diferentes em ordem alfabética. A consulta precisa eliminar nomes repetidos, preservar a ordem dos tópicos e retornar dez resultados.

O PostgreSQL define DISTINCT como a remoção de linhas duplicadas do resultado e LIMIT como a restrição do resultado retornado. Essa semântica não impõe um limite de dez linhas à varredura subjacente. O planejador considera o limite ao escolher um plano, portanto o trabalho físico depende da estratégia de execução escolhida. SELECT

Considere um fluxo ordenado contendo 1.000 ocorrências de topic-001, seguidas por 1.000 ocorrências de topic-002 e assim por diante. Uma operação de remoção de duplicatas em fluxo só encontra o décimo tópico distinto depois de consumir os nove grupos anteriores por inteiro e a primeira linha do décimo grupo:

Linhas de entrada necessárias = (9 × 1.000) + 1 = 9.001
Resultados distintos retornados = 10

Este é um cálculo ilustrativo para essa estratégia específica de fluxo, não um plano medido do PostgreSQL. Grupos desiguais alteram o cálculo: um grupo muito grande para o primeiro tópico pode dominar o trabalho, mesmo quando os grupos seguintes são pequenos.

Um índice B-tree correspondente pode fornecer dados ordenados sem uma ordenação explícita. O PostgreSQL documenta a recuperação antecipada para ORDER BY indexado com LIMIT, mas a eliminação de duplicatas acrescenta outro requisito: valores diferentes em quantidade suficiente precisam chegar ao nó de limite. Um índice pode eliminar o trabalho de ordenação e ainda deixar um volume considerável de trabalho de processamento de duplicatas. Indexes and ORDER BY

Leia o plano para identificar o que consome as linhas

Use EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) na consulta real e com os valores reais dos parâmetros. ANALYZE executa a instrução e informa as contagens reais de linhas; BUFFERS informa a atividade de blocos. O exemplo completo abaixo executa esse diagnóstico em dados sintéticos. EXPLAIN

Abaixo são mostrados dois padrões de plano úteis. São estruturas esquemáticas, não resultados capturados nem garantias sobre o plano que sua instalação escolherá.

Caminho de fluxo ordenado:
Limit
  Unique
    Index Only Scan ou Index Scan

Caminho de agregação:
Limit
  Sort
    HashAggregate
      Seq Scan

No caminho ordenado, compare a saída da varredura com a saída de Unique e Limit. Uma grande diferença indica uma entrada com muitas duplicatas. No caminho de agregação, a agregação consome sua entrada para estabelecer os grupos antes que a ordenação e a limitação finais possam terminar. A documentação de planos do PostgreSQL explica como as varreduras alimentam os nós de ordenação e agregação. Using EXPLAIN

Leia as linhas reais junto com loops: quando um nó é executado repetidamente, as contagens de linhas são médias por execução. Multiplique-as pelo número de loops ao estimar a saída total desse nó. Inspecione também a entrada abaixo de uma ordenação; um número pequeno de linhas emitidas pela ordenação não significa que sua entrada era pequena. Using EXPLAIN

Mantenha separadas três medições: linhas emitidas por um nó, atividade de blocos e tempo decorrido. As contagens de buffers descrevem blocos, não linhas lógicas, e os totais de buffers dos nós pais incluem os filhos. Tabelas temporárias usam buffers locais; arquivos temporários de trabalho usados por operações como ordenações aparecem como blocos temporários. EXPLAIN

Reproduza a comparação no seu Mac

Use uma versão LTS atual do Node.js, npm e um banco de dados PostgreSQL local existente. Este passo a passo usa o PostgreSQL 18 como referência de documentação. O programa imprime a versão do seu servidor para que você possa interpretar os resultados com base na documentação dessa versão. O node-postgres documenta a instalação com npm install pg e o suporte às versões atuais e LTS do Node.js. node-postgres

No Terminal, crie um diretório de trabalho e instale o driver:

mkdir distinct-row-demo
cd distinct-row-demo
npm init -y
npm install pg

Salve o programa completo abaixo como distinct-demo.mjs. Ele cria linhas sintéticas de artigos em uma tabela temporária, mede a consulta comum antes e depois de adicionar um índice e compara uma consulta explícita de busca do sucessor. Usa um único Client durante todo o processo, de modo que cada consulta é executada na mesma sessão do banco de dados.

As configurações de conexão vêm das variáveis de ambiente usuais do PostgreSQL. Configure-as para seu banco de dados local existente antes de executar o programa. O node-postgres documenta tanto conexões baseadas em variáveis de ambiente quanto a passagem de valores da consulta separadamente do texto SQL. Connecting, Queries

import pg from 'pg';
import assert from 'node:assert/strict';

const { Client } = pg;
const client = new Client({ connectionTimeoutMillis: 5000 });
const limit = 10;

const distinctSql = `
  SELECT DISTINCT topic FROM demo_articles
  ORDER BY topic LIMIT $1::integer
`;

const seekSql = `
  WITH RECURSIVE topics(topic, step) AS (
    (SELECT topic, 1 FROM demo_articles ORDER BY topic LIMIT 1)
    UNION ALL
    SELECT n.topic, t.step + 1
    FROM topics AS t
    CROSS JOIN LATERAL (
      SELECT a.topic FROM demo_articles AS a
      WHERE a.topic > t.topic ORDER BY a.topic LIMIT 1
    ) AS n
    WHERE t.step < $1::integer
  )
  SELECT topic FROM topics ORDER BY topic
`;

async function showPlan(label, sql) {
  const plan = await client.query(
    `EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) ${sql}`, [limit]
  );
  console.log(`\n${label}`);
  console.log(plan.rows.map(row => row['QUERY PLAN']).join('\n'));
}

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

  await client.query(`
    CREATE TEMP TABLE demo_articles (
      article_id integer NOT NULL,
      topic text COLLATE "C" NOT NULL,
      title text NOT NULL
    )
  `);

  await client.query(`
    INSERT INTO demo_articles (article_id, topic, title)
    SELECT n,
           'topic-' || lpad((((n - 1) / 1000) + 1)::text, 3, '0'),
           'Sample article ' || n::text
    FROM generate_series(1, 100000) AS s(n)
  `);

  await client.query('ANALYZE demo_articles');
  await showPlan('Before index', distinctSql);

  await client.query(
    'CREATE INDEX demo_articles_topic_idx ON demo_articles (topic)'
  );
  await client.query('ANALYZE demo_articles');
  await showPlan('After index', distinctSql);
  await showPlan('Successor seeks', seekSql);

  const ordinary = await client.query(distinctSql, [limit]);
  const seeking = await client.query(seekSql, [limit]);
  assert.deepEqual(seeking.rows, ordinary.rows);
  assert.equal(ordinary.rows.length, limit);
  console.log(ordinary.rows.map(row => row.topic));
} catch (error) {
  console.error(error.message);
  process.exitCode = 1;
} finally {
  await client.end();
}

O gerador de dados produz inteiros de 1 a 100.000 usando a função generate_series documentada pelo PostgreSQL. A operação aritmética atribui o mesmo tópico a cada grupo consecutivo de 1.000 artigos. ANALYZE fornece estatísticas da tabela que o planejador usa ao selecionar um plano. Set Returning Functions, ANALYZE

Execute o programa usando o nome do seu banco de dados local existente. Por exemplo, se o banco se chamar postgres:

PGHOST=localhost PGPORT=5432 PGDATABASE=postgres node distinct-demo.mjs

Os nomes de tópicos esperados vão de topic-001 a topic-010; as verificações confirmam a contagem de resultados e a concordância entre as duas consultas. A sintaxe JavaScript e o cálculo de 9.001 entradas para o fluxo foram verificados para este artigo. Não foi possível executar o banco de dados no ambiente de pesquisa, portanto os tempos e os planos capturados do PostgreSQL foram intencionalmente omitidos.

Por que uma varredura somente de índice ainda pode exigir muito trabalho

Index Only Scan significa que a consulta pode obter do índice os valores das colunas solicitadas. Isso não significa que haja uma entrada de índice por resultado distinto. Inspecione a contagem real de linhas, mesmo que a tabela em si raramente seja acessada.

O acesso ao heap é uma consideração separada. O PostgreSQL verifica o mapa de visibilidade e ainda pode visitar tuplas do heap quando as páginas correspondentes não estão marcadas como totalmente visíveis. Consequentemente, as linhas de demonstração recém-inseridas podem gerar buscas no heap mesmo em um plano somente de índice. Verifique Heap Fetches junto com as linhas da varredura. Index-Only Scans and Covering Indexes

Adicionar colunas extras de conteúdo com INCLUDE pode disponibilizar mais valores para uma varredura somente de índice, mas não altera a quantidade de tópicos distintos solicitada. Escolha a cobertura do índice para tratar o trabalho de acesso ao heap demonstrado; meça separadamente o consumo de duplicatas. Index-Only Scans and Covering Indexes

O PostgreSQL 18 resolve isso automaticamente com skip scan?

O PostgreSQL 18 introduziu o suporte a skip scan em índices B-tree. Seu objetivo documentado é melhorar algumas buscas em índices de várias colunas quando restrições sobre colunas posteriores podem orientar buscas repetidas nos valores de colunas anteriores. Esse recurso foi introduzido na versão 18; não presuma que ele exista no PostgreSQL 17 ou anterior. Release 18, Multicolumn Indexes

Pular automaticamente de uma chave inicial distinta para a seguinte é uma operação comumente chamada de varredura loose de índice. O wiki do projeto PostgreSQL distingue essa operação do skip scan de índice orientado a predicados e documenta sua emulação com SQL recursivo. Portanto, o anúncio do skip scan no PostgreSQL 18 não comprova a varredura loose automática para esta consulta DISTINCT de coluna única. Loose indexscan

A consulta de busca do sucessor no programa adapta essa emulação: encontra o menor tópico e, em seguida, encontra repetidamente o menor tópico maior que o anterior. A etapa recursiva para após dez resultados ou quando não há sucessor. Com um índice adequado, cada busca pode avançar para além do grupo de duplicatas anterior. Compare o plano real e o tempo de execução com os da consulta comum, em vez de presumir que haverá ganho de velocidade. Loose indexscan

Este exemplo exige deliberadamente tópicos não nulos, ordem crescente, a collation C declarada e um limite positivo. Um limite zero ainda retornaria a âncora recursiva, portanto uma implementação generalizada precisa tratar o zero separadamente. Tópicos que aceitam valores nulos precisam de tratamento explícito, pois uma comparação maior que não os enumerará. Se você adicionar filtros de artigos, aplique as mesmas condições à busca inicial e a cada busca de sucessor; caso contrário, a consulta reescrita poderá retornar um conjunto diferente.

Escolha a próxima alteração com base nas evidências

Use este guia de decisão depois de coletar os planos. Ele separa o trabalho que um índice de ordenação pode eliminar daquele causado por valores de tópico repetidos.

Resultado observado O que investigar em seguida
Uma varredura alimenta a agregação e uma ordenação final Compare o plano existente com um índice B-tree que corresponda à ordenação dos tópicos.
Uma varredura ordenada emite milhares de linhas para dez tópicos Meça o tamanho dos grupos de duplicatas e compare com buscas explícitas de sucessores.
Uma varredura somente de índice informa muitas buscas no heap Investigue separadamente o trabalho de visibilidade e acesso ao heap em relação à contagem de duplicatas.
As estimativas diferem muito das linhas reais Atualize as estatísticas com ANALYZE e meça novamente.
A consulta comum com índice já é barata Mantenha a consulta mais simples, a menos que outro requisito medido justifique uma reescrita.

Evite mover LIMIT 10 para uma subconsulta que selecione dez artigos brutos antes de aplicar DISTINCT. Na ordenação de exemplo, esses dez artigos pertencem todos a topic-001, então a consulta reescrita retorna um tópico em vez de dez. Isso altera a tarefa.

Mantenha também a chave distinta precisa. Adicionar article_id às colunas selecionadas torna cada artigo uma linha de saída diferente, anulando a deduplicação dos tópicos. Se a tarefa mudar para escolher um artigo representativo por tópico, o DISTINCT ON do PostgreSQL dá suporte a essa operação, com suas expressões correspondendo às expressões de ordenação mais à esquerda. Ele não promete, por si só, menos linhas examinadas. SELECT

Para obter os dez primeiros tópicos distintos de artigos, comece comparando a saída real da varredura com os dez resultados retornados. Em seguida, teste o índice correspondente e, quando os grupos de duplicatas dominarem, a consulta limitada de busca do sucessor. Preserve o conjunto e a ordenação dos tópicos solicitados ao escolher a abordagem cujo trabalho medido seja adequado aos seus dados.

Fontes