PostgreSQL kan använda ett index för id < 11 och en sekventiell skanning för id > 11 eftersom det första villkoret matchar mycket få rader, medan det andra matchar nästan hela tabellen. Båda operatorerna stöder B-trädindex; frågeplaneraren jämför deras uppskattade kostnader.

Den här guiden hjälper en utvecklare som arbetar på en Mac att återskapa det valet med syntetiska artikelrader, läsa planerna och kontrollera om skillnaden kräver åtgärd. Den följer den hämtade, aktuella dokumentationen för PostgreSQL 18 och node-postgres. Kontrollera serverversionen innan du jämför plandetaljer; exemplen garanterar inte identiska planer mellan versioner eller datorer. PostgreSQL: Using EXPLAIN

Varför kan samma index leda till olika val av skanning?

Ett B-trädindex stöder både mindre än- och större än-jämförelser. Att ändra jämförelseriktningen gör inte indexet oanvändbart. Det som ändras är värdeintervallet och därmed antalet rader som frågeplaneraren förväntar sig att hämta. PostgreSQL: Index Types

Tänk dig en tabell med exakt 100 000 artikelrader och löpande heltals-ID:n från 1 till 100 000:

Predikat Matchande rader Andel av tabellen
id < 11 10 0,01 %
id > 11 99 989 99,989 %
id = 11 1 0,001 %

Dessa antal följer av den syntetiska indatauppsättningen, inte av mätningar från en befintlig databas. Inget av intervallen omfattar ID 11. Ett index kan effektivt hitta de tio matchande ID:na i det första intervallet. I det andra intervallet skulle det ändå identifiera nästan varje rad.

En vanlig indexskanning i PostgreSQL hämtar poster från indexet och motsvarande rader från tabellens huvudsakliga lagring, som kallas heapen. Dessa heaprader kan vara utspridda över många sidor. När nästan varje sida behövs kan det vara billigare att läsa tabellen direkt än att gå igenom indexet och hämta raderna som det hänvisar till. PostgreSQL: Index-Only Scans and Covering Indexes

Talet 11 har alltså ingen särskild betydelse för frågeplaneraren. Med andra data kan villkoret större än motsvara det snäva intervallet. PostgreSQL uppskattar andelen matchande rader, som kallas selektivitet, med hjälp av tabellstatistik. Gränsvärdets storlek i sig fastställer inte den andelen. PostgreSQL: Statistics Used by the Planner

Hur återskapar du jämförelsen på en Mac?

Du behöver en befintlig PostgreSQL-anslutning och kommandot psql tillgängligt i Mac-datorns Terminal. Installation ingår inte i den här övningen. Öppna din vanliga databasanslutning med psql; det tar emot anslutningsinställningar och ger en interaktiv SQL-prompt. Kör följande kodblock i en ny session. PostgreSQL: psql

Konfigurationen skapar en temporär tabell som bara innehåller syntetisk artikeltext. Temporära tabeller försvinner när sessionen avslutas. Den vanliga heltalsbaserade primärnyckeln skapar automatiskt ett unikt B-trädindex, så ett andra index på id behövs inte. PostgreSQL: CREATE TABLE

SHOW server_version;

CREATE TEMP TABLE article_scan_demo (
    id integer PRIMARY KEY,
    title text NOT NULL,
    body text NOT NULL
);

INSERT INTO article_scan_demo (id, title, body)
SELECT
    g,
    'Article ' || g,
    repeat('Sample article text. ', 20)
FROM generate_series(1, 100000) AS s(g);

ANALYZE article_scan_demo;

SELECT
    count(*) AS total_rows,
    count(*) FILTER (WHERE id < 11) AS below_11,
    count(*) FILTER (WHERE id > 11) AS above_11
FROM article_scan_demo;

PostgreSQL-funktionen för heltal generate_series inkluderar båda ändpunkterna med standardsteget ett. Frågan som räknar rader bör därför ge totalt 100 000 rader, 10 under 11 och 99 989 över 11. PostgreSQL: Set Returning Functions

Kör ANALYZE när raderna har lästs in, så att frågeplaneraren får statistik om den ifyllda tabellen. Kommandot samlar in statistik; det har ett annat syfte än alternativet för exekveringsmätning i EXPLAIN ANALYZE. PostgreSQL: ANALYZE

Jämför sedan de två frågorna som hämtar hela rader:

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM article_scan_demo WHERE id < 11;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM article_scan_demo WHERE id > 11;

Dessa kommandon kör varje fråga och visar dess plan och faktiska radantal, utan att returnera alla valda artikelrader. BUFFERS lägger till blockaktivitet; TIMING OFF stänger av tidsmätning för enskilda noder. Det är användbart när du studerar exekveringen, men mäter inte tiden det tar att skicka alla matchande artikeldata till ett skrivbordsprogram. PostgreSQL: EXPLAIN

Räkna med en indexbaserad plan för det snäva intervallet och en sekventiell skanning för det breda intervallet med vanliga inställningar. Se detta som förväntningar: PostgreSQL kan välja en bitmapplan eller en annan tillämplig plan utifrån sina uppskattningar. SQL-exemplet granskades, men ingen PostgreSQL-server fanns tillgänglig för att köra det under förberedelsen; beräkningarna av radantal kontrollerades separat.

Vilka delar av planen förklarar beslutet?

Börja med skanningsnoden, dess uppskattade rows och dess faktiska rows. För den första frågan ska du leta efter Index Scan med ett Index Cond, eller en Bitmap Index Scan under en Bitmap Heap Scan. För den andra ska du leta efter Seq Scan med ett Filter. En bitmapplan använder fortfarande ett index; den samlar matchande radpositioner innan heapens sidor hämtas. PostgreSQL: Using EXPLAIN

Kontrollera sedan följande:

Observation i det här exemplet Vad du bör undersöka
Uppskattningarna ligger ungefär i nivå med 10 och 99 989 faktiska rader De olika matchningsantalen förklarar de olika valen.
Uppskattat radantal skiljer sig betydligt från det faktiska Uppdatera statistiken innan du ändrar inställningar för frågeplaneraren.
Båda predikaten ger sekventiella skanningar på ett mycket mindre dataurval Det kan vara billigare att skanna tabellen direkt.
En bitmapplan förekommer Jämför dess raduppskattningar och heaparbete som ett annat indexbaserat alternativ.

Frågeplanerarkostnader är uppskattningar i godtyckliga enheter, inte millisekunder. Jämför dem som en del av PostgreSQL:s modell i stället för att tolka en kostnad på 100 som en tidsåtgång. Eftersom exemplet använder temporära tabeller visas buffertaktiviteten under lokala block; buffertar för vanliga tabeller och index visas under delade block. PostgreSQL: EXPLAIN

Om uppskattningarna verkar överraskande kan du fortsätta i samma session med den här diagnostikfrågan:

SELECT
    schemaname,
    n_distinct,
    histogram_bounds,
    correlation
FROM pg_stats
WHERE schemaname = (
    SELECT nspname
    FROM pg_namespace
    WHERE oid = pg_my_temp_schema()
)
  AND tablename = 'article_scan_demo'
  AND attname = 'id';

Histogrammet beskriver värdefördelningen som används för intervalluppskattning. Korrelation beskriver hur nära kolumnens logiska ordning följer den fysiska radordningen; värden nära någon av ytterligheterna kan göra indexåtkomst till heapen billigare i frågeplanerarens beräkning. Inget av fälten fastställer en universell procentandel då PostgreSQL måste byta skanningstyp. PostgreSQL: pg_stats

Hur jämför du frågorna via node-postgres?

node-postgres skickar frågetext och parametervärden till PostgreSQL, där planeringen sker. Fråge-API:t tar emot SQL följt av en värdearray, så båda jämförelserna kan använda samma gränsvärde, 11. node-postgres: Queries

För en fristående Node.js-återskapning behöver du en fungerande installation av Node.js/npm och dina befintliga anslutningsinställningar för PostgreSQL. Kör npm init -y i en ny projektkatalog och därefter npm install pg. Spara hela programmet nedan som compare-scans.mjs och kör node compare-scans.mjs. Klienten använder PostgreSQL-miljöinställningar som PGHOST, PGPORT och PGDATABASE; använd inställningarna för din befintliga lokala databas. node-postgres: Connecting

import pg from 'pg';

const client = new pg.Client();

try {
  await client.connect();

  await client.query(`
    CREATE TEMP TABLE article_scan_demo (
      id integer PRIMARY KEY,
      title text NOT NULL,
      body text NOT NULL
    )
  `);

  await client.query(`
    INSERT INTO article_scan_demo (id, title, body)
    SELECT
      g,
      'Article ' || g,
      repeat('Sample article text. ', 20)
    FROM generate_series(1, 100000) AS s(g)
  `);

  await client.query('ANALYZE article_scan_demo');

  const queries = [
    'EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM article_scan_demo WHERE id < $1',
    'EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM article_scan_demo WHERE id > $1'
  ];

  for (const text of queries) {
    const result = await client.query(text, [11]);
    console.log(text);
    console.log(
      result.rows.map(row => row['QUERY PLAN']).join('\n')
    );
  }
} finally {
  await client.end();
}

Programmet skapar en egen exempeltabell och utför alla operationer på en enda anslutning. Det skriver ut båda planerna och stänger sedan anslutningen. JavaScript-syntaxen kontrollerades med Node.js; databaskörning genomfördes inte under förberedelsen. Om anslutningen misslyckas bör du lösa det innan du tolkar skanningsbeteendet.

Kan namngivna förberedda satser ändra jämförelsen?

Om du lägger till name i en node-postgres-frågekonfiguration skapas en namngiven förberedd sats på varje anslutning. Då tillkommer en skillnad: PostgreSQL kan använda en anpassad plan som bygger på parametervärdena eller en generell plan som delas mellan körningar. node-postgres: Queries, PostgreSQL: PREPARE

Med PostgreSQL 18:s standardpolicy för automatisk hantering använder de fem första körningarna anpassade planer. Därefter jämför PostgreSQL den uppskattade kostnaden för en generell plan med den genomsnittliga kostnaden för anpassade planer; det växlar inte automatiskt till en generell plan vid den sjätte körningen. PostgreSQL: PREPARE

Om en namngiven sats i ett program beter sig annorlunda än ett test med bokstavlig SQL kan du undersöka den satsen på samma anslutning med EXPLAIN EXECUTE. En generell plan visar parametersymboler, medan en anpassad plan ersätter dem med de angivna värdena. Behåll de två jämförelseoperatorerna i separata namngivna satser eftersom deras SQL-text skiljer sig åt. PostgreSQL: PREPARE

När bör du ändra frågan eller inställningarna för frågeplaneraren?

Utgå först från vad frågan faktiskt ska göra. Om du ersätter SELECT * med SELECT id ändras vilka data PostgreSQL måste hämta. När alla nödvändiga kolumner finns i ett lämpligt index blir en index-only-skanning möjlig. Att undvika hämtningar från heapen beror också på synlighetskartan, så en index-only-plan garanterar inte att heapen inte alls används. PostgreSQL: Index-Only Scans and Covering Indexes

Om uppgiften kräver alla matchande artiklar ska du behålla det kravet när du jämför planer. Om den bara behöver ett litet sorterat urval kan du i stället testa den faktiska frågan med ORDER BY och LIMIT: sortering och tidig avslutning kan ändra vilken plan som föredras. PostgreSQL: Using EXPLAIN

När du har kontrollerat statistiken kan du jämföra en alternativ plan i den befintliga SQL-sessionen:

BEGIN;
SET LOCAL enable_seqscan = off;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM article_scan_demo WHERE id > 11;

ROLLBACK;

Den här inställningen gör sekventiella skanningar mindre attraktiva, men kan inte förhindra dem helt. Använd resultatet som en diagnostisk jämförelse. PostgreSQL modellerar också sekventiell och icke-sekventiell sidåtkomst med seq_page_cost och random_page_cost. Om du ändrar deras relativa värden kan planvalen ändras, men dokumentationen rekommenderar att kostnadsinställningar utvärderas för hela frågebelastningen i stället för utifrån några få experiment. PostgreSQL: Query Planning

För den här syntetiska tabellen är den avgörande jämförelsen 10 matchande rader mot 99 989. Kontrollera den exakta frågan, uppdatera statistiken, jämför uppskattade och faktiska radantal och undersök beteendet hos förberedda planer när det är relevant. Då kan du avgöra om den sekventiella skanningen passar arbetet som efterfrågas och få underlag för eventuella ytterligare ändringar.

Källor