För att diagnostisera upprepade PostgreSQL-sökningar skiljer du mellan att skapa kandidater och att använda dem. Läs av exekveringsantalet för den inre skanningen, kontrollera om cachning undviker upprepat arbete och jämför sedan gruppering och återanvändning, inlining av CTE:er eller en indexerad temporär tabell.

Den här genomgången riktar sig till en utvecklare som arbetar på en Mac och behöver förklara en långsam artikelquery: välj ut kandidaterna en gång och hitta sedan det högsta kandidatartikel-ID:t inom varje artikels ämne. Genomgående används syntetiska rader. Målet är att identifiera var arbetet upprepas och välja en queryform som bevarar resultatet.

Konfigurera en reproducerbar kandidatsökning

Använd en befintlig lokal PostgreSQL-installation med psql tillgängligt. Öppna Terminal, kör psql med dina vanliga anslutningsinställningar och låt samma session vara öppen under alla SQL-exempel. PostgreSQL beskriver psql som sin interaktiva terminal för att skicka frågor och visa resultat. psql

Kontrollera först serverversionen med SHOW server_version;. Den aktuella dokumentation som hämtades för den här artikeln anger att PostgreSQL 18 är den senaste versionen. Exemplen använder syntax som stöds från och med PostgreSQL 12; det är en funktionsgräns, inte en rekommendation att använda en äldre version. PostgreSQL 12 introducerade inlining av CTE:er samt kontrollerna MATERIALIZED och NOT MATERIALIZED. WITH Queries, Release 12

Kör den här konfigurationen en gång i en ny session. Den skapar 20 000 syntetiska artiklar och ett index för sökningar på ämne och ID. Kandidatvillkoret nedan väljer de första 2 000 artiklarna.

CREATE TEMP TABLE article_demo (
    id integer PRIMARY KEY,
    topic_id integer NOT NULL,
    title text NOT NULL
);

INSERT INTO article_demo (id, topic_id, title)
SELECT g, 1 + (g % 50), 'Article ' || g
FROM generate_series(1, 20000) AS s(g);

CREATE INDEX article_demo_topic_id_idx
    ON article_demo (topic_id, id);

ANALYZE article_demo;

De här temporära tabellerna finns kvar under sessionen som standard. Kör ANALYZE uttryckligen efter att du har läst in dem: PostgreSQLs autovacuum-process analyserar inte temporära tabeller. CREATE TABLE

Samla nu in ett utgångsvärde. Den laterala underfrågan beräknar det högsta kandidat-ID:t för den aktuella artikelns ämne. Här är ”högsta ID” helt enkelt den definierade uppgiften; det innebär inte ett publiceringsdatum.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
WITH candidate AS MATERIALIZED (
    SELECT id, topic_id, title
    FROM article_demo
    WHERE id <= 2000
)
SELECT c.id, c.title, m.highest_id
FROM candidate AS c
CROSS JOIN LATERAL (
    SELECT max(x.id) AS highest_id
    FROM candidate AS x
    WHERE x.topic_id = c.topic_id
) AS m;

Det uttryckliga MATERIALIZED gör återanvändningsgränsen i experimentet tydlig. En materialiserad CTE kan byggas en gång medan dess konsumenter läser de lagrade raderna flera gånger. PostgreSQLs dokumentation visar hur en materialiserad CTE som refereras flera gånger går miste om fördelen med den underliggande tabellens index när mellanresultatet genomsöks. Materialisering av Common Table Expressions

SQL-exemplen har granskats men kördes inte mot en PostgreSQL-server för den här artikeln. Beräkningarna av antalet syntetiska rader kontrollerades separat; inga exekveringstider eller insamlade planer presenteras som mätvärden.

Läs planen för att hitta upprepat arbete

Börja vid grenen som producerar kandidater och följ sedan varje konsument. Identifiera den yttre inmatningen och den inre sökningen under en nästlad loop. PostgreSQL rapporterar loops som en nods antal exekveringar; faktiska rader och nodtider är medelvärden per exekvering. En version med korrelerad skalär underfråga kan i stället visa upprepat arbete under SubPlan, så diagnosen bör följa de noder som exekveras och inte kräva en viss etikett. Använda EXPLAIN

Anteckna fyra observationer från din faktiska plan:

  1. Hur många rader går in i den yttre sökningen?
  2. Hur många gånger exekveras den underliggande inre skanningen?
  3. Läser den skanningen kandidater och kastar sedan bort de flesta med ett filter?
  4. Finns det en Memoize-nod mellan de yttre raderna och det kostsamma inre arbetet?

Håll isär nivåerna. En aggregatnod kan returnera ett maximum per exekvering samtidigt som dess underordnade nod skannar många rader för att beräkna det. Att kandidatproducenten körs en gång visar alltså inte att det är billigt att använda kandidaterna.

I det här exakta exemplet omfattar 2 000 kandidater 50 ämnen, med 40 artiklar per ämne. Följande beräkning beskriver möjliga arbetsmönster, inte garanterade planresultat eller körtider:

Användningsmönster Illustrativa genomgångar av kandidatrader
Fullständig skanning av kandidater för varje yttre artikel 2 000 × 2 000 = 4 000 000
Fullständig skanning en gång per ämne, med alla senare sökningar cachade 50 × 2 000 = 100 000
Gruppera kandidaterna en gång efter ämne 2 000 rader till grupperingssteget, plus efterföljande join-arbete

Skillnaden spelar roll i PostgreSQL 14 och senare, där memoiser­ing av resultat från inre nästlade loopar infördes. Den aktuella dokumentationen beskriver Memoize som cachning av parametriserade skanningsresultat, vilket gör att den underliggande skanningen kan hoppas över vid en cacheträff; poster kan också rensas ur cachen. Granska cache-nyckeln och eventuella rapporterade träffar, missar, rensningar och överflöden innan du antar att tusentals yttre rader innebär tusentals fullständiga skanningar. Release 14, Frågeplanering

Jämför även uppskattade rader med faktiska rader vid den tidigaste betydande avvikelsen. PostgreSQL rekommenderar att dessa uppskattningar kontrolleras och varnar för att planer för små testtabeller inte nödvändigtvis kan extrapoleras till större indata. Undvik att lägga ihop tider för föräldra- och barnnoder: de beskriver överlappande exekveringsarbete. Använda EXPLAIN

Välj en omskrivning som passar det upprepade arbetet

Den relevanta frågan är hur mycket arbete varje konsument behöver utföra på kandidatmängden. Använd det här beslutsstödet för att välja det första experimentet:

Observerat behov Första experiment Villkor att bevara
Varje kandidat behöver samma maximum per ämne Gruppera en gång och gör sedan en join En sammanfattningsrad per ämne
Varje konsument behöver en liten indexerad del av källdatan Prova NOT MATERIALIZED Behåll hela kandidatvillkoret
Flera satser behöver en uttryckligen indexerad kandidatrelation Skapa en indexerad temporär tabell Behåll samma session och räkna med konfigurationskostnaden
Upprepade parametrar ger redan många cacheträffar Jämför total exekveringstid innan du ändrar formen Bekräfta att de underliggande skanningarna faktiskt är kostsamma

Beräkna resultatet per ämne en gång

För den här uppgiften uttrycker en grupperad sammanfattning återanvändningen direkt. Kör följande efter konfigurationen, i samma session:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
WITH candidate AS MATERIALIZED (
    SELECT id, topic_id, title
    FROM article_demo
    WHERE id <= 2000
), topic_max AS (
    SELECT topic_id, max(id) AS highest_id
    FROM candidate
    GROUP BY topic_id
)
SELECT c.id, c.title, m.highest_id
FROM candidate AS c
JOIN topic_max AS m USING (topic_id);

Den här omskrivningen ger ett maximum för varje ämne och kopplar det till varje kandidatartikel. PostgreSQL definierar max som det största icke-nullvärdet bland indata. Exempelkolumnerna för ämne och ID innehåller inga nullvärden, vilket gör likvärdigheten enkel: varje kandidat tillhör en grupp som innehåller kandidaten själv. Aggregatfunktioner

Förvänta dig 2 000 resultatrader om du kör frågan utan EXPLAIN. Artikel 1 tillhör till exempel ämne 2 och får highest_id = 1951; ämne 1 får highest_id = 2000. Det här följer deterministiskt av exempelgeneratorn.

Kontrollera resultatets innebörd innan du generaliserar. Om du grupperar efter extra kolumner kan det skapas flera sammanfattningsrader per ämne, vilket multiplicerar join-resultatet. Om ämnes-ID:n kan vara null måste även likhetssemantiken ses över. Om uppgiften kräver titeln på artikeln med högst ID är det ytterligare ett steg att joina tillbaka maximumet till kandidatraderna; att ta ett separat maximum av titeln skulle besvara en annan fråga.

Omskrivningen tillåter en annan plan, men kräver inte en hash-join och garanterar ingen hastighetsökning. Jämför det uppmätta grupperings- och join-arbetet med de faktiska inre exekveringarna i utgångsplanen.

Testa om källindex kan ersätta skanningar av mellanresultat

För en CTE som stöder detta byter du ut AS MATERIALIZED i hela utgångsfrågan mot AS NOT MATERIALIZED och kör den igen. Då kan källrelationen optimeras tillsammans med resten, vilket eventuellt byter lagrade kandidatskanningar mot sökningar i källdata. Det kan också duplicera kandidatarbetet. Materialisering av Common Table Expressions

Exemplets (topic_id, id)-index motsvarar likhet på den inledande ämneskolumnen och en begränsning på ID-kolumnen. Det gör det till ett rimligt index att testa; kontrollera om den faktiska planen använder det och hur mycket den skannar. PostgreSQL dokumenterar likhet på den inledande kolumnen och en olikhet på nästa kolumn som villkor som begränsar en skanning med ett sammansatt B-trädindex. Flerspaltiga index

Bevara id <= 2000 i kandidatdefinitionen. Om du söker efter maximum över alla 20 000 källrader ändras svaret. Bevara också den dokumenterade begränsningen: NOT MATERIALIZED ignoreras för rekursiva CTE:er och CTE:er som inte är fria från sidoeffekter. För en CTE som inte uppfyller kraven använder du i stället en stödd relationell omskrivning eller mellanlagrar resultatet uttryckligen. SELECT

Mellanlagra en indexerad kandidatrelation när återanvändningen sträcker sig över flera satser

En CTE gäller bara en sats. Om flera satser behöver samma kandidatrader och uttryckliga index ger en temporär tabell en annan livslängd och indexeringsmöjlighet. Kör det här alternativet efter den ursprungliga konfigurationen:

CREATE TEMP TABLE candidate_demo AS
SELECT id, topic_id, title
FROM article_demo
WHERE id <= 2000;

CREATE INDEX candidate_demo_topic_id_idx
    ON candidate_demo (topic_id, id);

ANALYZE candidate_demo;

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT c.id, c.title, m.highest_id
FROM candidate_demo AS c
CROSS JOIN LATERAL (
    SELECT max(x.id) AS highest_id
    FROM candidate_demo AS x
    WHERE x.topic_id = c.topic_id
) AS m;

Index för temporära tabeller är också temporära, och relationen är begränsad till sessionen. Räkna med skapandet, indexbygget och analysen när du bedömer om mellanlagring hjälper hela uppgiften. Jämför inte bara den sista sökningen med hela operationen utan mellanlagring. CREATE TABLE

Samla in samma experiment med node-postgres på din Mac

Använd en Node.js-version som stöds för närvarande och installera det dokumenterade paketet med npm install pg för att granska programmets frågeväg. Spara hela programmet nedan som diagnose.mjs i projektet och kör node diagnose.mjs. Det skapar egna syntetiska indata och är därför inte beroende av den tidigare sessionen med psql. node-postgres

Programmet använder din befintliga PostgreSQL-anslutningsmiljö. node-postgres dokumenterar samma anslutningsmiljövariabler som psql, däribland PGHOST, PGPORT och PGDATABASE. Ansluta

import pg from 'pg';
const { Client } = pg;
const client = new Client();

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

  await client.query(`
    CREATE TEMP TABLE article_demo (
      id integer PRIMARY KEY,
      topic_id integer NOT NULL,
      title text NOT NULL
    );
    INSERT INTO article_demo (id, topic_id, title)
    SELECT g, 1 + (g % 50), 'Article ' || g
    FROM generate_series(1, 20000) AS s(g);
    CREATE INDEX article_demo_topic_id_idx
      ON article_demo (topic_id, id);
    ANALYZE article_demo;
  `);

  const sql = `
    WITH candidate AS MATERIALIZED (
      SELECT id, topic_id, title
      FROM article_demo
      WHERE id <= $1::integer
    )
    SELECT c.id, c.title, m.highest_id
    FROM candidate AS c
    CROSS JOIN LATERAL (
      SELECT max(x.id) AS highest_id
      FROM candidate AS x
      WHERE x.topic_id = c.topic_id
    ) AS m
  `;

  const result = await client.query({
    text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) ${sql}`,
    values: [2000]
  });

  for (const row of result.rows) {
    console.log(row['QUERY PLAN']);
  }
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  await client.end();
}

Frågekonfigurationen använder de dokumenterade fälten text och values. Om du lägger till ett name skapas en förberedd sats på varje anslutning; det cachar inte kandidatrader och tar inte bort upprepade inre exekveringar. Frågor

Om ett befintligt program använder en pool ska skapandet och användningen av den temporära tabellen ske på samma utlånade klient, som sedan återlämnas med finally. Separata anrop till pool.query kan använda olika klienter. node-postgres kräver också att alla satser i en transaktion använder samma klientinstans. Pooling, Transaktioner

Undersök planeringen separat för namngivna satser vars beteende ändras beroende på parametervärden. PostgreSQL kan välja generiska planer eller anpassade planer för specifika parametrar; den dokumenterade metoden för inspektion är EXPLAIN EXECUTE för den förberedda satsen. En nyskapad förklaring av en namnlös fråga kan därför skilja sig från en etablerad förberedd exekvering. PREPARE

Avgör om det upprepade arbetet faktiskt har minskat

Använd samma kandidatvillkor, valda kolumner och indatavärden vid varje jämförelse. Kör ett första varv och upprepa jämförbara varv för att observera variationer i cachningen. Anteckna serverversion, antal kandidater, antal exekveringar av den inre skanningen, cachebeteende och total exekveringstid. PostgreSQL 17 förbättrade planeringen av CTE:er med hjälp av kolumnstatistik och sorteringsordning, så anteckna versionen när du jämför resultat från olika datorer. Release 17

Under en andra diagnostisk genomgång behåller EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) radantal och satsens exekveringstid, samtidigt som tidsmätningar per nod undviks. Bufferttotaler för föräldranoder omfattar aktivitet i underordnade noder, så summera inte hela trädet. EXPLAIN ANALYZE exekverar frågan men mäter inte överföringen av resultatraderna till Node.js. EXPLAIN

Behandla planeringsinställningar som experiment. Om du stänger av enable_nestloop motverkas nästlade loopar, men de kan inte elimineras helt; enable_material kan inte heller undertrycka all materialisering. Ingen av inställningarna visar att användningen av kandidater har blivit effektiv. Frågeplanering

Avsluta med den queryform som bevarar de rader som krävs och minskar det uppmätta arbetet för hela uppgiften. För det här maximumet per ämne är gruppering och återanvändning det tydligaste första experimentet. Behåll indexerade sökningar när de visar sig vara billiga och använd uttrycklig mellanlagring när den extra konfigurationen lönar sig för upprepade konsumenter.

Källor