Inleiding tot EXPLAIN en ANALYZE

Wat is een queryplan precies? Het is een gedetailleerde, stapsgewijze uitsplitsing van hoe PostgreSQL je SQL-instructie wil uitvoeren. Zie het als een receptkaart met alle ingrediënten en handelingen.

• Welke tabellen worden gelezen?
• In welke volgorde?
• Worden er indexen gebruikt?
• Hoe worden gegevens gesorteerd of geaggregeerd?

Het queryplan beantwoordt deze vragen en toont de bewerkingen en hun geschatte kosten.

Laten we de opdracht EXPLAIN gebruiken om het geschatte plan voor een eenvoudige SELECT-query te bekijken. We gaan ervan uit dat er een basistabel users bestaat.

Belangrijk: EXPLAIN toont alleen het plan; de query wordt NIET daadwerkelijk uitgevoerd en er worden geen gegevens opgehaald. Je kunt deze opdracht veilig op productiesystemen gebruiken.

De uitvoer van EXPLAIN wordt vaak weergegeven als een boomstructuur van bewerkingen. Dit zijn enkele veelvoorkomende termen die je kunt tegenkomen:

• Seq Scan: PostgreSQL leest elke rij in de tabel van begin tot eind.
• Index Scan: PostgreSQL gebruikt een index om snel specifieke rijen te vinden.
• rows: de schatting van de optimizer van het aantal rijen dat een bewerking verwerkt.
• cost: een geschatte maat voor de hoeveelheid werk, die de totale kosten van de bewerking weergeeft. Lagere kosten zijn doorgaans beter.

Dit zijn allemaal schattingen op basis van databasestatistieken.

Hoewel EXPLAIN je een goed overzicht en schattingen geeft, kunnen deze schattingen soms onnauwkeurig zijn als databasestatistieken verouderd zijn of als er complexe voorwaarden worden gebruikt.

Daar komt EXPLAIN ANALYZE van pas! Door ANALYZE aan je opdracht met EXPLAIN toe te voegen, wordt de query daadwerkelijk uitgevoerd en worden statistieken uit de praktijk verzameld. Zo krijg je een veel nauwkeuriger beeld van de prestaties.

Laten we dezelfde query uitvoeren, maar deze keer met EXPLAIN ANALYZE. Let op: omdat deze opdracht de query uitvoert, duurt dit net zo lang als de query normaal zou duren en treden eventuele neveneffecten, zoals wijzigingen in gegevens, daadwerkelijk op.

De uitvoer van EXPLAIN ANALYZE bevat alle informatie van een gewone EXPLAIN, plus werkelijke uitvoeringsstatistieken:

• actual time: De werkelijke tijd die elke stap kost (opstarttijd en totale tijd).
• rows: Het werkelijke aantal rijen dat door elke stap is verwerkt.
• loops: Hoe vaak een bewerking is uitgevoerd.

Door deze werkelijke waarden te vergelijken met de geschatte waarden van een eenvoudige EXPLAIN, kunt u ontdekken waar de optimizer mogelijk slechte keuzes heeft gemaakt.

Hier volgt een korte samenvatting van de belangrijkste verschillen:

• EXPLAIN:
  - Toont het *geschatte* plan en de kosten.
  - Voert de query *niet* uit.
  - Veilig voor productieomgevingen.
• EXPLAIN ANALYZE:
  - Toont *werkelijke* uitvoeringsstatistieken (tijd, rijen).
  - *Voert* de query uit, waarbij gegevens mogelijk worden gewijzigd.
  - Kan veel resources gebruiken; wees voorzichtig in productie.

Weten wanneer u elke opdracht gebruikt, is essentieel voor effectieve prestatieafstemming:

• Gebruik EXPLAIN voor:
  - Snelle controles van het plan zonder de query uit te voeren.
  - Het testen van hypothetische herschrijvingen van queries zonder neveneffecten.
  - Het veilig onderzoeken van plannen voor complexe of langlopende queries.
• Gebruik EXPLAIN ANALYZE voor:
  - Gedetailleerde prestatieanalyse en het identificeren van knelpunten.
  - Het controleren van schattingen van de optimizer aan de hand van echte uitvoering.
  - Gebruik dit doorgaans eerst in ontwikkel- of acceptatieomgevingen.

Gefeliciteerd! U hebt uw eerste stap gezet in het begrijpen van queryplannen van PostgreSQL. U weet nu:

• EXPLAIN toont het *geschatte* plan van de database.
• EXPLAIN ANALYZE *voert* de query uit en geeft *werkelijke* prestatiemetrieken.

Deze opdrachten zijn onmisbare hulpmiddelen voor iedereen die PostgreSQL-queries wil optimaliseren. In de volgende lessen gaan we dieper in op het interpreteren van de verschillende knooppunten die u in deze plannen ziet.