LIMIT 10 begränsar resultatet, men att hitta tio distinkta värden kan kräva tusentals indatarader. Den här guiden visar hur du diagnostiserar det arbetet och jämför två metoder med syntetiska artikelämnen från din Macs Terminal.

Varför tio resultat kan kräva tusentals rader

Anta att en artikeltabell innehåller 100 000 rader över 100 ämnen, med 1 000 artiklar per ämne. Din uppgift är att hämta de första tio olika ämnesnamnen i alfabetisk ordning. Frågan måste eliminera dubblettnamn, bevara ämnesordningen och returnera tio resultat.

PostgreSQL definierar DISTINCT som att ta bort dubblettutdateringsrader och LIMIT som att begränsa det returnerade resultatet. Dessa semantikker medför inte en budget på tio rader för den underliggande skanningen. Planeraren beaktar gränsen när ett plan valjs, så det fysiska arbetet beror på den valda körstrategin. SELECT

Överväg en ordnad ström som innehåller 1 000 förekomster av topic-001, följt av 1 000 av topic-002, och så vidare. En strömmande dubblettborttagningsoperation ser det tionde distinkta ämnet först efter att ha konsumerat alla nio föregående grupper och den första raden i den tionde gruppen:

Input rows needed = (9 × 1,000) + 1 = 9,001
Distinct results returned = 10

Detta är en illustrativ beräkning för den specifika strömmandestrategin, inte en mörd PostgreSQL-plan. Ojämlika grupper ändrar beräkningen: en mycket stor första ämnesgrupp kan dominera arbetet även när senare grupper är små.

Ett matchande B-trädindex kan leverera sorterad inmatning utan explicit sortering. PostgreSQL dokumenterar tidig hämtning för indexerat ORDER BY med LIMIT, men dubblettborttagning lägger till ett annat krav: tillräckligt många olika värden måste nå begränsningsnoden. Ett index kan ta bort sorteringsarbetet men lämna betydande dubblettbearbetningsarbete. Indexes and ORDER BY

Läs planen för att identifiera vad som konsumerar raderna

Använd EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) på den faktiska frågan och parametervärdena. ANALYZE kör uttrycket och rapporterar faktiska radantal; BUFFERS rapporterar blockaktivitet. Det kompletta exemplet nedan kör denna diagnostik på syntetiska data. EXPLAIN

Två användbara planmönster visas nedan. De är schematiska former, inte fångad utdata eller löften om vilken plan din installation kommer att välja.

Ordnad strömmande väg:
Limit
  Unique
    Index Only Scan or Index Scan

Aggregeringsväg:
Limit
  Sort
    HashAggregate
      Seq Scan

I den ordnade vägen, jämför skanningens utdata med utdata från Unique och Limit. En stor skillnad indikerar dubbletttung inmatning. I aggregeringsvägen konsumerar aggregeraren sin inmatning för att etablera grupperna innan den slutgiltiga ordningen och begränsningen kan slutföras. PostgreSQLsplandokumentation förklarar hur skanningar matar sorterings- och aggregeringsnoder. Using EXPLAIN

Läs faktiska rader tillsammans med loops: radantal är genomsnitt per körning när en nod körs upprepade gånger. Multiplicera dem med loopen antal när du uppskattar total utdata från den noden. Undersök också inmatningen under en sortering; ett litet antal rader som emitteras av sorteringen etablerar inte att dess inmatning var liten. Using EXPLAIN

Håll tre mätvärden separata: rader som emitteras av en nod, blockaktivitet och förfluten tid. Buffertantal beskriver block snarare än logiska rader, och förälderbuffertotaler inkluderar deras barn. Temporära tabeller använder lokala buffertar; temporära arbetsfiler som används av operationer som sorteringar visas som temp-block. EXPLAIN

Reproducera jämförelsen från din Mac

Använd en nuvarande Node.js LTS-utgåva, npm, och en befintlig lokal PostgreSQL-databas. Den här genomgången använder PostgreSQL 18 som dokumentationsbaslinje. Programmet skriver ut din serverversion så att du kan tolka resultaten mot den versionens dokumentation. node-postgres dokumenterar installation med npm install pg och stöd för nuvarande och LTS Node.js-utgåvor. node-postgres

I Terminal, skapa en arbetsmapp och installera drivrutinen:

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

Spara det följande kompletta programmet som distinct-demo.mjs. Det skapar syntetiska artikelrader i en temporär tabell, mäter den vanliga frågan före och efter att ett index lagts till, och jämför en explicit efterföljare-sökfråga. Det använder en Client genomgående så att varje fråga körs i samma databassession.

Anslutningsinställningar kommer från de vanliga PostgreSQL-miljövariablerna. Konfigurera dessa för din befintliga lokala databas innan du kör programmet. node-postgres dokumenterar både miljöbaserade anslutningar och att skicka frågevärden separat från SQL-text. 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();
}

Datageneratorn producerar heltal från 1 till 100 000 med hjälp av PostgreSQLs dokumenterade generate_series-funktion. Aritmetiken tilldelar varje på varandra följande grupp av 1 000 artiklar samma ämne. ANALYZE levererar tabellstatistik som planeraren använder när ett plan väljs. Set Returning Functions, ANALYZE

Kör programmet, med namnet på din befintliga lokala databas. Till exempel, om den databasen heter postgres:

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

De förväntade ämnesnamnen är topic-001 till topic-010; kontrollerna kontrollerar resultatantalet och överensstämmelse mellan de två frågorna. JavaScript-syntaxen och 9 001-inmatningens strömmande beräkning kontrollerades för den här artikeln. Databaskörning var otillgänglig i forskningsmiljön, så tidsmätningar och fångade PostgreSQL-planer utelämnas avsiktligt.

Varför en index-endast-skanning fortfarande kan göra betydligt arbete

En Index Only Scan betyder att frågan kan hämta sina efterfrågade kolumnvärden från indexet. Det betyder inte en indexpost per distinklt resultat. Undersök dess faktiska radantal även om tabellen sällan besöks.

Heap-åtkomst är en separat övervägande. PostgreSQL kontrollerar synlighetskartan och kan fortfarande besöka heap-tupler när motsvarande sidor inte är markerade som alla-synliga. Därför kan de nyligen infogda demonstrationsraderna producera heap-hämtningar även under en index-endast-plan. Kontrollera Heap Fetches tillsammans med skanningsrader. Index-Only Scans and Covering Indexes

Att lägga till extra nyttolastkolumner med INCLUDE kan göra ytterligare värden tillgängliga för en index-endast-skanning, men det ändrar inte antalet efterfrågade distinkta ämnen. Välj indextäckning för att hantera visade heap-åtkomstarbete; mät dubblettkonsumtion separat. Index-Only Scans and Covering Indexes

Löser PostgreSQL 18 skip scan detta automatiskt?

PostgreSQL 18 introducerade stöd för B-trädskippsskanning. Dess dokumenterade syfte är att förbättra vissa flerkolumnsindexsökningar när begränsningar på senare kolonner kan leda upprepade sökningar över tidigare kolumnvärden. Den funktionen introducerades i version 18; anta inte att den finns i PostgreSQL 17 eller tidigare. Release 18, Multicolumn Indexes

Att automatiskt hoppa från en distinkt ledande nyckel till nästa kallas ofta en lös indexskanning. PostgreSQL-projektets wiki skiljer denna operation från predikat-orienterad index-skipping-skanning och dokumenterar rekursiv SQL-emulering. PostgreSQL 18s skip-scan-utlysning etablerar alltså inte automatisk lös skanning för denna enkeltkolumns-DISTINCT-fråga. Loose indexscan

Programmets efterföljare-sökfråga anpassar den emuleringen: hitta det minsta ämnet, hitta sedan upprepade gånger det minsta ämnet större än det föregående. Det rekursiva steget stoppas efter tio resultat eller när ingen efterföljare existerar. Med ett lämpligt index kan varje sökning söka bortom den föregående dubblettgruppen. Jämför dess faktiska plan och körningstid med den vanliga frågan snarare än att anta en hastighetsökning. Loose indexscan

Det här exemplet kräver avsiktligt icke-null-ämnen, stigande ordning, den deklarerade C kollationen och en positiv gräns. En nollgräns skulle fortfarande returnera den rekursiva ankare, så en generaliserad implementation måste hantera noll separat. Nullbara ämnen behöver explicit behandling eftersom en större-jämförelse inte kommer att räkna upp null-värden. Om du lägger till artikelfilter, applicera samma villkor på både den initiala sökningen och varje efterföljaresökning; annars kan den omskrivna frågan returnera en annan uppsättning.

Välj nästa förändring utifrån bevisen

Använd detta beslutsstöd efter att du samlat in planerna. Det separerar arbete som ett ordningsindex kan ta bort från arbete orsakat av upprepade ämnesvärden.

Observerat resultat Vad att undersöka nästa
En skanning matar aggregering och en slutlig sortering Jämför den befintliga planen med ett B-trädindex som matchar ämnesordningen.
En ordnad skanning emitterar tusentals rader för tio ämnen Mät dubblettgruppstorlekar och jämför explicita efterföljaresökningar.
En index-endast-skanning rapporterar många heap-hämtningar Undersök synlighet och heap-åtkomstarbete separat från dubblettantal.
Uppskattningar skiljer sig avsevärt från faktiska rader Uppdatera statistik med ANALYZE, mät sedan igen.
Den indexerade vanliga frågan är redan billig Behåll den enklare frågan om inte ett annat mätt krav motiverar en omskrivning.

Undvik att flytta LIMIT 10 in i en underfråga som väljer tio råartiklar innan DISTINCT tillämpas. I exempelordningen tillhör dessa tio artiklar alla topic-001, så den omskrivna frågan returnerar ett ämne istället för tio. Det ändrar uppgiften.

Håll också det distinkta nyckelvärdet precist. Att lägga till article_id till de valda kolumnerna gör att varje artikel blir en annan utdatarad, vilket motverkar ämnesdeduplicering. Om uppgiften ändras till att välja en representativ artikel per ämne, stödjer PostgreSQLs DISTINCT ON den operationen, med dess uttryck som matchar de vänstersta ordningsuttrycken. Det lovar inte själv färre skannade rader. SELECT

För de första tio distinkta artikelämnena, börja med att jämföra skanningens faktiska utdata med de tio returnerade resultaten. Testa sedan det matchande indexet och, där dubblettgrupper dominerar, den begränsade efterföljare-sökfrågan. Bevara det efterfrågade ämnesutbudet och ordningen medan du väljer den metod vars mätta arbete passar dina data.

Källor