CTE

en navngiven delforespørgsel, der læses som en tabel

Hvad det er

En CTE, kort for common table expression, er en forespørgsel, der får et navn i en WITH-klausul øverst i en sætning, så resten af sætningen kan læse dens rækker, som om de kom fra en tabel. En sætning kan definere flere CTE’er, hver af dem kan læse dem, der er navngivet før den, og den sidste forespørgsel nederst sætter dem sammen. PostgreSQL’s manual beskriver dem som midlertidige tabeller, der kun findes for én forespørgsel. Billedet holder med ét forbehold: Der gemmes ikke noget under navnet, og den næste sætning kan ikke se det. En CTE kan være en SELECT, eller i PostgreSQL også en INSERT, UPDATE, DELETE eller MERGE, og sætningen, som WITH-klausulen hænger på, kan være en hvilken som helst af dem.

Et spørgsmål som »hvilke målere brugte på en enkelt dag denne måned mere end tre gange deres eget normale forbrug« deler sig naturligt i trin:

WITH dagligt AS (
  SELECT maaler_id, maalt_kl::date AS dag, sum(kwh) AS kwh
  FROM forbrug
  WHERE maalt_kl >= current_date - 30
  GROUP BY maaler_id, maalt_kl::date
),
normalt AS (
  SELECT maaler_id, avg(kwh) AS snit_kwh
  FROM dagligt
  GROUP BY maaler_id
)
SELECT d.maaler_id, d.dag, d.kwh, n.snit_kwh
FROM dagligt AS d
JOIN normalt AS n USING (maaler_id)
WHERE d.kwh > 3 * n.snit_kwh
ORDER BY d.dag, d.maaler_id;

Første trin lægger hver målers aflæsninger sammen pr. dag, andet trin tager gennemsnittet af de dagstal pr. måler, og det sidste sammenligner hver dag med sin egen målers gennemsnit. Samme svar kan skrives med indlejrede delforespørgsler, men så står dagstallene to gange i teksten, den ene inde i den anden, og læseren må arbejde sig indefra og ud. Med WITH læses logikken oppefra og ned, og hvert trin kan køres for sig, mens man kontrollerer det.

Tilføjer man RECURSIVE, kan en CTE læse sit eget output, og det er den del, almindelig SQL ellers ikke har nogen måde at udtrykke på. En rekursiv CTE har en startforespørgsel, derefter UNION eller UNION ALL og så en forespørgsel, der henviser tilbage til CTE’en. PostgreSQL kører startforespørgslen, lægger dens rækker i en arbejdstabel og bliver ved med at køre den anden forespørgsel mod de nyeste rækker, indtil en runde ikke tilføjer flere. Sådan går en sætning gennem et træ af ukendt dybde, for eksempel kategorierne under én overskrift:

WITH RECURSIVE undertrae AS (
  SELECT id, foraelder_id, navn, 1 AS dybde
  FROM kategorier
  WHERE id = 42
  UNION ALL
  SELECT k.id, k.foraelder_id, k.navn, u.dybde + 1
  FROM kategorier AS k
  JOIN undertrae AS u ON k.foraelder_id = u.id
)
SELECT id, navn, dybde FROM undertrae ORDER BY dybde, navn;

Hvorfor det betyder noget

Den første grund er læsbarhed. En analytisk forespørgsel på hundrede linjer, der er indlejret fire niveauer ned, er svær at gennemgå og sværere at ændre. Den samme logik som en kæde af navngivne trin giver den, der gennemgår koden, én sætning pr. trin, giver forfatteren et sted at skrive en kommentar og lader enhver køre ét trin alene for at se, om rækkerne ser rigtige ud, før man stoler på det næste.

Den anden grund er, hvordan databasen evaluerer trinnene, og her har detaljerne ændret sig over tid. PostgreSQL evaluerer normalt en WITH-forespørgsel én gang pr. kørsel af sætningen, også når sætningen henviser til den flere gange, så en dyr beregning, der skal bruges to steder, kun kører én gang. Prisen for den garanti er, at planlæggeren ikke kan skubbe et filter fra den ydre forespørgsel ned i en CTE, der bruges flere steder, fordi filteret kun skal gælde én af brugene. CTE’en beregnes fuldt ud, og rækkerne smides væk bagefter.

Indtil PostgreSQL 12 gjaldt det for alle CTE’er: Hver blev evalueret før resten af forespørgslen og aldrig flettet ind i den. Version 12 ændrede standarden. En CTE, der ikke er rekursiv, ikke har sideeffekter og kun bruges én gang, foldes nu ind i den ydre forespørgsel, så planlæggeren optimerer de to niveauer samlet og kan bruge et indeks på det filter, den før ikke kunne se. MATERIALIZED tvinger den gamle opførsel frem, og NOT MATERIALIZED tvinger foldningen igennem, også for en CTE, der bruges flere gange, med risiko for at arbejdet kører to gange. SQLite accepterer de samme to nøgleord som uforpligtende hints og skriver selv, at idéen er lånt fra PostgreSQL. Andre motorer beslutter selv, og planen er stedet at kontrollere det.

Den tredje grund er, at en CTE kan ændre data. En DELETE med RETURNING i en WITH-klausul kan fodre en INSERT i hovedsætningen, og så flyttes rækker fra én tabel til en anden i én sætning, der enten sker helt eller slet ikke. Dataændrende CTE’er kører præcis én gang og altid til ende, uanset om noget læser deres output. Det gør dem til et redskab til at udføre flere skrivninger samlet, snarere end en måde at beskrive et resultat på.

Sådan ser det ud i praksis

Arbejdet bag PostgreSQL-ydeevnen, der blev accelereret 10 gange gjaldt en database med brugerkonti, tenants, lokationer og daglige eldata, der blev langsom, efterhånden som datamængderne voksede. Løsningen havde tre dele: målrettede indeks, herunder GiST-indeks til de geospatiale data, tidsbaseret partitionering af eltabellen pr. dag eller måned, og omskrivning af forespørgsler. Komplekse forespørgsler blev skrevet om for at undgå full table scans, med CTE’er og materialized views, mens EXPLAIN ANALYZE pegede på de ineffektive joins. Svartider, der blev 10 gange bedre, og et ressourceforbrug, der faldt med 40 %, kom fra de tre dele tilsammen og ikke fra CTE’er alene. Kombinationen er værd at bemærke, fordi de to gør forskelligt arbejde: En CTE navngiver et mellemresultat, så længe én sætning varer, mens et materialized view gemmer et resultat, som mange sætninger kan læse, indtil det opdateres.

Den anden side af CTE’er, læsbarheden, bar de 500 rapporter om el- og GIS-dataanalyse. Hver rapport blev skrevet i ren SQL direkte i PostgreSQL og BigQuery, over data fra smart-målere med geolokationskoordinater, måler-ID’er og tidsstemplet forbrug, og scriptsene brugte CTE’er sammen med window functions, delforespørgsler og geospatiale joins. Hver rapport kom med annoteret SQL-kode, så kolleger kunne reproducere og revidere analysen, og med fejlfindingsnoter, der dokumenterede almindelige datakvalitetsproblemer og anbefalede håndteringen af dem. Rapporterne blev en standardreference på tværs af afdelinger og bidrog til en forbedring af driftsplanlægningens effektivitet på 10–15 %. Navngivne trin er det, der overhovedet gør den slags SQL mulig at annotere: En note kan stå over hvert trin og sige, hvad det fjerner, eller hvad det forudsætter.

Hvor det går galt

Et filter, der først rammer, når arbejdet er gjort. En CTE, der bruges to gange eller er markeret MATERIALIZED, beregnes fuldt ud, før den ydre forespørgsel filtrerer den, og den kopi, den laver, har ingen indeks. En forespørgsel, der burde røre få rækker, læser en hel tabel. Læs planen: Står CTE’en som sit eget trin med rækkeantal langt over det, forespørgslen returnerer, så prøv NOT MATERIALIZED, eller skriv forespørgslen om, så CTE’en bruges én gang. Behold den materialiserede form, hvor CTE’en kalder en dyr funktion, så funktionen kører én gang pr. række og ikke én gang pr. henvisning.

En rekursiv forespørgsel, der bliver ved. Data med en cyklus, for eksempel to kategorier, der hver står som den andens forælder, sender en rekursiv CTE med UNION ALL rundt i løkken, så længe serveren tillader det. UNION hjælper kun, når hele rækker gentager sig, og det holder sjældent, når der er en dybdekolonne med. Gem stien af besøgte nøgler i et array og stop, når en nøgle dukker op igen, eller brug CYCLE-klausulen, som PostgreSQL 14 tilføjede. En LIMIT på den ydre forespørgsel virker som et trick under test, og manualen fraråder at regne med det i produktion. SQL Server begrænser rekursion til 100 niveauer som standard og afbryder sætningen derefter, så den samme fejl stopper i stedet for at køre videre.

Ændringer, som resten af sætningen ikke ser. En dataændrende CTE og hovedforespørgslen kører samtidig på det samme snapshot, så en hovedforespørgsel, der læser måltabellen direkte, ser rækkerne, som de var før ændringen. Den eneste kanal mellem dem er RETURNING. Værre er det, når to dele af én sætning ændrer den samme række: Kun én af ændringerne slår igennem, og hvilken er svær at forudsige. Læs ændrede rækker fra CTE’ens RETURNING-output, og lad hver række have én skriver pr. sætning.

En plan, der flyttede sig med en opgradering. Kode skrevet til PostgreSQL 11 eller tidligere regnede nogle gange med, at en CTE blev evalueret først, som en bevidst barriere mod en plan, forfatteren ikke ville have. Efter en opgradering til 12 eller nyere foldes den samme CTE ind i den ydre forespørgsel, og planlæggeren vælger noget nyt, som regel hurtigere og en gang imellem meget langsommere. Sammenlign EXPLAIN-output for de langsomme sætninger før og efter opgraderingen, og tilføj MATERIALIZED, hvor barrieren var tilsigtet.

Læs mere

Ordliste