Din SQL er ikke langsom den er bare ærlig

40 % af den tid mange webapps bruger på et request, forsvinder nede i databasen. Ikke i backend-koden. Ikke i frameworket. I én eller to SQL-queries.

Jeg opdagede det første gang, da en uskyldig liste-side tog 4-5 sekunder at loade. Jeg kiggede på API-koden i en time. Til sidst kørte jeg EXPLAIN. Så stod problemet og råbte mig ind i hovedet.

I den her artikel vil jeg vise dig, hvordan jeg i dag går til en langsom query med EXPLAIN, trin for trin. Ikke alle mulige DB-specifikke nørdetermer, bare det du skal kunne læse for at finde et indeks, der faktisk hjælper.

Før og efter: den samme query med og uden fornuft

Jeg starter med slutningen, for det er ofte sådan jeg selv lærer noget: se forskellen, så forklare bagefter.

Situationen: en langsom liste-side

Forestil dig en tabel orders med 500.000 rækker. Du har en admin-side, der viser de nyeste ordre for én kunde:

SELECT id, customer_id, created_at, total_amount
FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 20;

Brugeren oplever det som “siden hænger”. Du oplever det som “det er sikkert bare React”. Det er det sjældent.

EXPLAIN før: fuld tabelscan

Vi kører:

EXPLAIN
SELECT id, customer_id, created_at, total_amount
FROM orders
WHERE customer_id = 123
ORDER BY created_at DESC
LIMIT 20;

En typisk output (PostgreSQL-agtig, forsimplet) kunne ligne:

Seq Scan on orders  (cost=0.00..18250.00 rows=25000 width=48)
  Filter: (customer_id = 123)

Nogle databaser viser det i en tabel, andre som tekst. Pointen er:
Seq Scan (sequential scan) og ingen nævnelse af index.

EXPLAIN efter: indeks der rent faktisk bliver brugt

Vi opretter først et indeks:

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);

Så kører vi den samme EXPLAIN igen:

Index Scan using idx_orders_customer_created on orders
  (cost=0.43..150.00 rows=20 width=48)
  Index Cond: (customer_id = 123)

Nu står der Index Scan, og cost/rows ser helt anderledes ud. Samme query, ny plan. Typisk går svaret fra sekunder til millisekunder.

Resten af artiklen handler om at kunne se den forskel og bevidst styre derhen.

Hvad EXPLAIN faktisk hjælper dig med (og hvad det ikke gør)

EXPLAIN er ikke magi. Det er bare databasen, der fortæller dig, hvordan den har tænkt sig at udføre din query.

EXPLAIN svarer på

• Bruger den et indeks eller ej?
• Hvor mange rækker forventer den at læse?
• I hvilken rækkefølge bliver tabeller joinet?
• Hvor bliver der sorteret, filtreret og limt ting sammen?

EXPLAIN svarer ikke på

• Den faktiske tid (det er et estimat, ikke et stopur)
• Om din SQL er “pæn” eller “grim”
• Om du har designet datamodellen klogt

Derfor bruger jeg altid to ting sammen:

• Rigtig måling: logget query-tid eller EXPLAIN ANALYZE
• Planen fra EXPLAIN til at forstå “hvorfor”

Hvis du slet ikke er tryg ved almindelige SELECT-sætninger endnu, så er det faktisk værd at tage en omvej forbi små SQL SELECT øvelser først. Performance er sjovere, når syntaksen sidder nogenlunde.

5 signaler i EXPLAIN du skal kende først

De fleste EXPLAIN-planer ligner en blanding af juletræ og skattejagt første gang man ser dem. Jeg kigger næsten altid efter de samme 5 ting.

1. Seq Scan vs Index Scan

Det første jeg leder efter:

• Seq Scan: databasen læser hele tabellen igennem
• Index Scan (eller Index Only Scan): databasen bruger et indeks

Seq Scan er ikke altid ondt. På små tabeller kan det være hurtigere end at rode med indeks. Men hvis din tabel har hundredtusindvis af rækker, og EXPLAIN viser Seq Scan, så er der typisk noget at hente.

2. estimated rows

I mange databaser kan du se noget ala:

rows=25000

Det er hvor mange rækker planner tror, den skal igennem. Hvis du filtrerer på én kunde, og den stadig tror, den skal læse halvdelen af tabellen, er din betingelse ikke særlig selektiv. Så er et indeks måske ikke det rette alene, eller også er statistikken forældet.

3. Filter vs Index Cond

To nøgleord i EXPLAIN:

• Index Cond: filteret sker i indekset
• Filter: filteret sker efter, der er læst rækker

Hvis du ser Index Cond: (customer_id = 123), bruger databasen dit indeks, som du håber.

Hvis du ser Index Cond på én kolonne og Filter på resten, er du halvvejs. Om lidt kigger vi på composite indexes, hvor rækkefølgen bestemmer, hvad der kan være Index Cond.

4. Sort-noder

Hvis du har ORDER BY i din query, kigger jeg altid efter en Sort-node:

Sort  (cost=...)
  Sort Key: created_at DESC
  -> Seq Scan on orders ...

Det betyder, at databasen først henter rækkerne, så sorterer. Det kan være fint for få rækker, men rigtig dyrt på store resultater.

Hvis dit indeks er lavet i samme rækkefølge som ORDER BY, kan databasen ofte springe Sort-noden over og bare læse rækkefølgen direkte fra indekset.

5. Join-rækkefølge

Ved joins kigger jeg på, hvilken tabel der står nederst (starter) og hvordan de andre joiner på.

En klassisk langsom case:

Nested Loop
  -> Seq Scan on big_table
  -> Index Scan on small_table

Hvis planner starter med en stor tabel uden et godt filter, kan hele joinet blive langsomt. Nogle gange kan man få en bedre plan ved at:

• ændre WHERE-betingelser
• tilføje et indeks på join-kolonnerne
• eller splitte queryen op

Indeks 101 genopfriskning: hvorfor “flere indeks” ofte gør det værre

Jeg har selv været “bare giv den et indeks mere”-typen. Det virker i starten. Indtil det ikke gør.

Hvad et indeks hjælper på

Et indeks hjælper typisk kun på tre ting:

• WHERE-betingelser
• JOIN-kolonner
• ORDER BY-kolonner

Alt andet er mere eller mindre bonus. Et indeks er grundlæggende et sorteret opslag over en eller flere kolonner, som databasen kan slå hurtigt op i i stedet for at scanne hele tabellen.

Selectivity: hvor meget får vi egentlig sorteret fra?

Et indeks giver først mening, når det skærer meget væk.

• God selectivity: du filtrerer ned til få procent af tabellen
• Dårlig selectivity: du filtrerer ned til 50-80 % af tabellen

Eksempel:

-- Dårligt indeks-kandidat
WHERE is_active = true;

-- Bedre kandidat (hvis du ofte søger på det)
WHERE email = 'bruger@example.com';

Et indeks på is_active hjælper sjældent, hvis 90 % af dine rækker er aktive. Databasen ender alligevel med at hente næsten alt.

Hvorfor for mange indeks gør det langsomt

Hvert ekstra indeks betyder arbejde ved:

• INSERT: der skal indsættes i tabellen og alle relevante indeks
• UPDATE: indekset skal opdateres, hvis kolonnen ændrer sig
• DELETE: rækker skal fjernes fra tabellen og indekser

Alt det kostbar arbejde sker i den del af din app, hvor der typisk er færrest caches og mindst tålmodighed: når nogen skriver data.

Derfor er en sund vane at bruge indsigt i data og databaser og regelmæssigt slette eller samle overflødige indeks, i stedet for bare at smide nye på.

Composite indexes: rækkefølgen afgør alt

Composite indexes (flere kolonner i samme indeks) er der, hvor meget SQL-performance enten bliver fantastisk eller meget forvirrende.

Et simpelt composite indeks

Vi vender tilbage til vores orders-tabel og opretter:

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);

Det hjælper på queries af typen:

WHERE customer_id = 123
ORDER BY created_at DESC

Men ikke nødvendigvis på:

WHERE created_at > now() - interval '7 days'
ORDER BY created_at DESC

Her mangler vi jo filter på customer_id. Det er fordi indekset læses i rækkefølgen:

1. Først efter customer_id
2. Inden for hver kunde efter created_at

Tommelregel for rækkefølge

Jeg bruger ofte den her lille huskeregel:

• Kolonner du altid filtrerer på først
• Kolonner du ofte sorterer på bagefter

I praksis:

• Bruger- eller kunde-id først
• Dato/tid eller status-nøgle bagefter

Eksempel:

-- Du henter oftest "sager" for én bruger, nyeste først
CREATE INDEX idx_cases_user_created
ON cases (user_id, created_at DESC);

Hvis du senere finder ud af, at du oftere henter “alle åbne sager på tværs”, kan et andet indeks give mere mening, f.eks. (status, created_at). Det er her, man må kigge på rigtige queries, ikke bare gætte.

Query-mønstre der ofte dræber performance (og hvad EXPLAIN siger)

Nogle SQL-mønstre opfører sig ret forudsigeligt dårligt. Det er dem, jeg tjekker først i en performance-fejlsøgning.

LIKE “%…” uden begyndelse

En klassiker:

WHERE name LIKE '%abc%'

Med et almindeligt indeks på name kan databasen ikke bruge det effektivt, fordi den ikke kender starten på strengens mønster.

I EXPLAIN ser du ofte:

Seq Scan on users
  Filter: (name ~~ '%abc%'::text)

Hvis du kan ændre mønstret til:

WHERE name LIKE 'abc%'

så kan et indeks ofte bruges bedre, og EXPLAIN vil vise Index Scan i stedet.

ORDER BY uden matchende indeks

Hvis du har en query med:

ORDER BY created_at DESC
LIMIT 20

men ingen indeks der starter med created_at, bliver EXPLAIN typisk:

Sort
  Sort Key: created_at DESC
  -> Seq Scan on orders

Et indeks som:

CREATE INDEX idx_orders_created
ON orders (created_at DESC);

kan gøre, at sorteringen sker via indekset i stedet for i en separat Sort-node.

OFFSET pagination

Den klassiske “side 37”-pagination:

SELECT ...
FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 720;  -- side 37

Problemet er ikke kun sorteringen. For at give dig side 37, skal databasen typisk:

• læse de første 740 rækker i rækkefølge
• smide de 720 første væk
• returnere 20

I EXPLAIN med OFFSET vil du tit se høje rows-tal og måske Sort på toppen.

Et alternativ er keyset pagination, hvor du husker sidste id eller timestamp:

SELECT ...
FROM orders
WHERE created_at < $last_created_at
ORDER BY created_at DESC
LIMIT 20;

Det spiller meget bedre sammen med et indeks på (created_at) og ser pænere ud i EXPLAIN.

Den lille 15-minutters metode til en slow query

Nu binder vi det sammen til den proces, jeg faktisk følger, når noget er langsomt.

1. Mål først, ikke føl

Før jeg rører noget som helst:

• Notér queryen, som den faktisk kører i appen
• Notér gennemsnitlig varighed (fra logs eller databaseværktøj)
• Kør den et par gange manuelt for at bekræfte, at den er langsom

Hvis du har mulighed for det, så brug EXPLAIN ANALYZE i stedet for bare EXPLAIN. Det giver dig både planen og de faktiske tider. Bare pas på i produktion, da den rent faktisk udfører queryen.

2. Kør EXPLAIN og find én åbenlys ting

Jeg prøver altid at finde ét oplagt problem:

• Seq Scan på en stor tabel
• Sort på en stor mængde rækker
• Join uden indeks på join-kolonnerne

Jeg prøver ikke at fikse alt på én gang. Et lille, kontrolleret eksperiment er bedre end en stor omlægning.

3. Formuler en hypotese

Eksempel:

• “Hvis jeg laver et composite indeks på (customer_id, created_at), burde den skifte til Index Scan og droppe Sort”

Det er vigtigt at være specifik, så du kan se, om EXPLAIN-planen ændrer sig sådan, som du forventer.

4. Lav én ændring og kør EXPLAIN igen

Det kan være:

• Et nyt indeks
• En omskrivning af WHERE
• En ændret ORDER BY

Så kører jeg EXPLAIN igen og tjekker:

• Er Seq Scan blevet til Index Scan?
• Er Sort-noden væk?
• Er estimeret rows gået markant ned?

5. Mål igen og beslut om det var det værd

Til sidst måler jeg igen på den rigtige querytid:

• Hvis den er 2-10 gange hurtigere: fint
• Hvis forskellen er inden for støj: ryk tilbage, slet dit indeks, prøv noget andet

Det lyder kedeligt, men det er sådan du undgår placebo-optimering og ender med fem næsten identiske indeks, fordi “det føltes hurtigere”.

Mini-case: fra 3,2 sekunder til 40 ms på en liste-side

Jeg slutter med et mere sammenhængende eksempel. Ikke fordi det er snydt, men fordi det faktisk er tæt på noget jeg har haft i et hobbyprojekt.

Udgangspunktet

En frontend-liste over blogindlæg for en bestemt kategori og sprog:

SELECT id, title, slug, published_at
FROM posts
WHERE category_id = 4
  AND language = 'da'
  AND published_at <= now()
ORDER BY published_at DESC
LIMIT 10;

Tabellen posts havde ca. 300.000 rækker. Query-tid: ca. 3,2 sekunder i gennemsnit.

EXPLAIN før

Planen lignede:

Sort  (cost=50000..50020 rows=10000)
  Sort Key: published_at DESC
  -> Seq Scan on posts  (cost=0.00..40000 rows=10000)
        Filter: (category_id = 4 AND language = 'da' AND published_at <= now())

Oversættelse:

• Seq Scan: læser hele tabellen
• Filter: alt sker efter læsning, ingen Index Cond
• Sort: sorterer 10.000 rækker

Hypotese

De fleste forespørgsler på siden filtrerede på category_id og language, og altid sorteret efter published_at DESC.

Min hypotese blev:

• Et composite indeks på (category_id, language, published_at DESC) vil gøre det muligt at bruge Index Scan og undgå Sort.

Ændringen

CREATE INDEX idx_posts_cat_lang_published
ON posts (category_id, language, published_at DESC);

EXPLAIN efter

Ny plan:

Index Scan using idx_posts_cat_lang_published on posts
  (cost=0.43..120.00 rows=10)
  Index Cond: (category_id = 4 AND language = 'da' 
               AND published_at <= now())

Så nu:

• Index Scan i stedet for Seq Scan
• Index Cond på alle tre kolonner
• Ingen separat Sort-node

Resultat

Query-tiden faldt til ~40 ms. Samme database, samme data, samme query-tekst. Kun forskellen på “ingen indeks” og “indeks der matcher det mønster vi faktisk bruger”.

Det er i den slags situationer, man bliver lidt for glad for databaser. Og så går man ud og laver kaffe.

Lille tjekliste: sådan angriber du din næste slow query

Jeg vil lige samle en minimal huskeseddel, som jeg selv kunne have brugt tidligere. Brug den som en slags mini-workflow, på linje med når du debugger JavaScript eller kigger i logs og stacktraces.

1. Få styr på symptomet

• Hvilken side eller API-endpoint er langsomt?
• Hvilken konkret query køres (kopiér den fra logs eller ORM)?
• Hvor langsom er den egentlig (gennemsnit, worst case)?

2. Kør EXPLAIN (eller EXPLAIN ANALYZE)

• Leder efter Seq Scan på store tabeller
• Kigger efter Sort på store mængder rows
• Tjekker Index Scan / Index Cond vs Filter

3. Tænk i mønstre, ikke i tilfældige indeks

• Hvad filtrerer du næsten altid på?
• Hvordan sorterer du oftest?
• Hvilke joins går igen?

Lav composite indexes ud fra det, ikke ud fra én tilfældig query.

4. Gør én ting ad gangen

• Tilføj ét indeks eller omskriv én WHERE/ORDER BY
• Kør EXPLAIN igen og tjek om planen faktisk ændrer sig
• Mål tid igen under så ens forhold som muligt

5. Ryd op bagefter

• Slet eksperimentelle indeks du ikke ender med at bruge
• Notér hvad du lærte (f.eks. i README eller som kommentar i koden)
• Overvej tests, der sikrer, at du ikke uforvarende ændrer query-mønstre helt

Det kan virke overkill på små hobbyprojekter, men min erfaring er, at det er netop der, man tør øve sig, så man ikke står og panikker første gang en kunde ringer og spørger, hvorfor alt er blevet tungt.

Din database lyver faktisk aldrig om, hvad den laver. Du har bare ikke kigget den i kortene endnu.