LIMIT 10 limita il risultato, ma trovare dieci valori distinti può richiedere migliaia di righe in input. Questa guida mostra come diagnosticare questo lavoro e confrontare due approcci usando argomenti sintetici per articoli dal Terminal del tuo Mac.

Perché dieci risultati possono richiedere migliaia di righe

Supponiamo che una tabella di articoli contenga 100.000 righe distribuite su 100 argomenti, con 1.000 articoli per argomento. Il tuo compito è recuperare alfabeticamente i primi dieci nomi di argomento distinti. La query deve eliminare i nomi ripetuti, mantenere l’ordine degli argomenti e restituire dieci risultati.

PostgreSQL definisce DISTINCT come la rimozione delle righe di output duplicate e LIMIT come la limitazione del risultato restituito. Queste semantiche non impongono un limite di dieci righe alla scansione sottostante. Il planner considera il limite quando sceglie un piano, quindi il lavoro fisico dipende dalla strategia di esecuzione scelta. SELECT

Considera un flusso ordinato contenente 1.000 occorrenze di topic-001, seguite da 1.000 di topic-002 e così via. Un’operazione di rimozione dei duplicati in streaming vede il decimo argomento distinto solo dopo aver consumato tutti i nove gruppi precedenti e la prima riga del decimo gruppo:

Righe di input necessarie = (9 × 1.000) + 1 = 9.001
Risultati distinti restituiti = 10

Questo è un calcolo illustrativo per quella specifica strategia di streaming, non un piano PostgreSQL misurato. Gruppi di dimensioni diverse cambiano il calcolo: un gruppo iniziale molto grande può dominare il lavoro anche quando quelli successivi sono piccoli.

Un indice B-tree corrispondente può fornire dati ordinati senza un ordinamento esplicito. PostgreSQL documenta il recupero anticipato per ORDER BY indicizzato con LIMIT, ma l’eliminazione dei duplicati aggiunge un altro requisito: devono raggiungere il nodo limit i valori distinti sufficienti. Un indice può eliminare il lavoro di ordinamento lasciando comunque un considerevole lavoro di elaborazione dei duplicati. Indexes and ORDER BY

Leggi il piano per individuare cosa consuma le righe

Usa EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) sulla query effettiva e sui valori dei parametri. ANALYZE esegue l’istruzione e riporta i conteggi effettivi delle righe; BUFFERS riporta l’attività dei blocchi. L’esempio completo qui sotto esegue questa diagnosi su dati sintetici. EXPLAIN

Di seguito sono mostrati due schemi di piano utili. Sono forme schematiche, non output acquisiti né promesse sul piano che sceglierà la tua installazione.

Percorso ordinato in streaming:
Limit
  Unique
    Index Only Scan or Index Scan

Percorso di aggregazione:
Limit
  Sort
    HashAggregate
      Seq Scan

Nel percorso ordinato, confronta l’output della scansione con quello di Unique e Limit. Una grande differenza indica un input con molti duplicati. Nel percorso di aggregazione, l’aggregato consuma il proprio input per determinare i gruppi prima che possano terminare l’ordinamento e il limit finale. La documentazione dei piani di PostgreSQL spiega come le scansioni alimentano i nodi di ordinamento e aggregazione. Using EXPLAIN

Leggi le righe effettive insieme a loops: i conteggi delle righe sono medie per esecuzione quando un nodo viene eseguito più volte. Moltiplicali per il numero di cicli per stimare l’output totale di quel nodo. Esamina anche l’input sotto un ordinamento: il fatto che l’ordinamento emetta poche righe non dimostra che il suo input fosse ridotto. Using EXPLAIN

Tieni separate tre misurazioni: righe emesse da un nodo, attività dei blocchi e tempo trascorso. I conteggi dei buffer descrivono i blocchi, non le righe logiche, e i totali dei buffer dei nodi padre includono quelli dei figli. Le tabelle temporanee usano buffer locali; i file di lavoro temporanei usati da operazioni come gli ordinamenti appaiono come blocchi temporanei. EXPLAIN

Riproduci il confronto dal tuo Mac

Usa una versione LTS attuale di Node.js, npm e un database PostgreSQL locale esistente. Questa procedura usa PostgreSQL 18 come riferimento per la documentazione. Il programma stampa la versione del server, così puoi interpretare i risultati in base alla documentazione di quella versione. node-postgres documenta l’installazione con npm install pg e il supporto per le versioni attuali e LTS di Node.js. node-postgres

In Terminal, crea una directory di lavoro e installa il driver:

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

Salva il programma completo qui sotto come distinct-demo.mjs. Crea righe sintetiche di articoli in una tabella temporanea, misura la query ordinaria prima e dopo l’aggiunta di un indice e confronta una query esplicita di ricerca del successivo. Usa un unico Client per tutte le query, così vengono eseguite nella stessa sessione del database.

Le impostazioni di connessione provengono dalle consuete variabili d’ambiente di PostgreSQL. Configurale per il tuo database locale esistente prima di eseguire il programma. node-postgres documenta sia le connessioni basate sull’ambiente sia il passaggio dei valori di query separatamente dal testo 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();
}

Il generatore di dati produce numeri interi da 1 a 100.000 usando la funzione generate_series documentata da PostgreSQL. L’aritmetica assegna lo stesso argomento a ogni gruppo consecutivo di 1.000 articoli. ANALYZE fornisce le statistiche della tabella usate dal planner nella scelta di un piano. Set Returning Functions, ANALYZE

Esegui il programma usando il nome del tuo database locale esistente. Per esempio, se il database si chiama postgres:

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

I nomi degli argomenti attesi vanno da topic-001 a topic-010; le asserzioni controllano il numero di risultati e la corrispondenza tra le due query. Per questo articolo sono stati verificati la sintassi JavaScript e il calcolo di 9.001 righe di input per lo streaming. Nell’ambiente di ricerca non era possibile eseguire il database, quindi i tempi e i piani PostgreSQL acquisiti sono stati omessi intenzionalmente.

Perché una scansione solo dell’indice può comunque richiedere molto lavoro

Index Only Scan significa che la query può ottenere dall’indice i valori delle colonne richieste. Non significa che ci sia una voce dell’indice per ogni risultato distinto. Esamina il conteggio effettivo delle righe anche se la tabella viene visitata raramente.

L’accesso all’heap è una questione distinta. PostgreSQL controlla la mappa di visibilità e può comunque visitare le tuple nell’heap quando le pagine corrispondenti non sono contrassegnate come tutte visibili. Di conseguenza, le righe dimostrative appena inserite possono comportare recuperi dall’heap anche con un piano solo dell’indice. Controlla Heap Fetches insieme alle righe della scansione. Index-Only Scans and Covering Indexes

Aggiungere ulteriori colonne di payload con INCLUDE può rendere disponibili altri valori a una scansione solo dell’indice, ma non cambia il numero di argomenti distinti richiesti. Scegli la copertura dell’indice per affrontare il lavoro dimostrato di accesso all’heap; misura separatamente il consumo di duplicati. Index-Only Scans and Covering Indexes

PostgreSQL 18 risolve automaticamente il problema con la scansione skip?

PostgreSQL 18 ha introdotto il supporto per la scansione skip degli indici B-tree. Lo scopo documentato è migliorare alcune ricerche su indici multicolonna quando i vincoli sulle colonne successive possono guidare ricerche ripetute attraverso i valori delle colonne precedenti. Questa funzionalità è stata introdotta nella versione 18; non presumere che sia disponibile in PostgreSQL 17 o precedenti. Release 18, Multicolumn Indexes

Il salto automatico da una chiave iniziale distinta alla successiva è comunemente chiamato scansione loose dell’indice. Il wiki del progetto PostgreSQL distingue questa operazione dalla scansione skip dell’indice orientata ai predicati e documenta l’emulazione con SQL ricorsivo. L’annuncio della scansione skip in PostgreSQL 18, quindi, non dimostra che la scansione loose sia automatica per questa query a colonna singola DISTINCT. Loose indexscan

La query di ricerca del successivo nel programma adatta tale emulazione: trova l’argomento più piccolo, poi cerca ripetutamente l’argomento più piccolo maggiore del precedente. Il passaggio ricorsivo si ferma dopo dieci risultati o quando non esiste alcun successivo. Con un indice adatto, ogni ricerca può saltare oltre il precedente gruppo di duplicati. Confronta il piano effettivo e il tempo di esecuzione con quelli della query ordinaria invece di presumere un aumento di velocità. Loose indexscan

Questo esempio richiede deliberatamente argomenti non nulli, ordinamento crescente, la collation C dichiarata e un limite positivo. Un limite pari a zero restituirebbe comunque l’ancora ricorsiva, quindi un’implementazione generalizzata deve gestire separatamente lo zero. Gli argomenti nullable richiedono una gestione esplicita, perché un confronto maggiore di non enumererà i valori nulli. Se aggiungi filtri sugli articoli, applica le stesse condizioni sia alla ricerca iniziale sia a ogni ricerca del successivo; altrimenti la query riscritta può restituire un insieme diverso.

Scegli la modifica successiva in base ai dati

Usa questo supporto decisionale dopo aver raccolto i piani. Distingue il lavoro che un indice di ordinamento può eliminare da quello causato dai valori di argomento ripetuti.

Risultato osservato Cosa esaminare successivamente
Una scansione alimenta l’aggregazione e un ordinamento finale Confronta il piano esistente con un indice B-tree che corrisponda all’ordinamento degli argomenti.
Una scansione ordinata emette migliaia di righe per dieci argomenti Misura le dimensioni dei gruppi di duplicati e confronta le ricerche esplicite del successivo.
Una scansione solo dell’indice riporta molti recuperi dall’heap Esamina visibilità e accesso all’heap separatamente dal conteggio dei duplicati.
Le stime differiscono notevolmente dalle righe effettive Aggiorna le statistiche con ANALYZE, quindi misura di nuovo.
La query ordinaria indicizzata ha già un costo contenuto Mantieni la query più semplice, a meno che un altro requisito misurato non giustifichi una riscrittura.

Evita di spostare LIMIT 10 in una sottoquery che seleziona dieci articoli grezzi prima di applicare DISTINCT. Nell’ordinamento di esempio, quei dieci articoli appartengono tutti a topic-001, quindi la query riscritta restituisce un solo argomento invece di dieci. Questo cambia il compito.

Mantieni precisa anche la chiave distinta. Aggiungere article_id alle colonne selezionate rende ogni articolo una riga di output diversa, impedendo la deduplicazione degli argomenti. Se il compito cambia e diventa scegliere un articolo rappresentativo per ogni argomento, PostgreSQL supporta questa operazione con DISTINCT ON, le cui espressioni devono corrispondere alle espressioni iniziali dell’ordinamento. Questo non garantisce di per sé un numero inferiore di righe scansionate. SELECT

Per recuperare i primi dieci argomenti distinti degli articoli, inizia confrontando l’output effettivo della scansione con i dieci risultati restituiti. Poi prova l’indice corrispondente e, quando dominano i gruppi di duplicati, la query limitata di ricerca del successivo. Mantieni l’insieme e l’ordinamento degli argomenti richiesti mentre scegli l’approccio il cui lavoro misurato si adatta ai tuoi dati.

Fonti