Een EXPLAIN-plan lezen

Zodra je bij een senior sollicitatieronde komt, vragen interviewers niet meer alleen schrijf een query, maar beginnen ze te vragen waarom is deze query traag. Het hulpmiddel dat daarop antwoord geeft, is EXPLAIN.

EXPLAIN toont het uitvoeringsplan van de database: de stapsgewijze strategie die de planner heeft gekozen om je SQL uit te voeren. Je ziet welke tabellen worden gescand, in welke volgorde ze worden gekoppeld en ongeveer hoe duur elke stap is.

Als je een plan kunt lezen, laat je zien dat je de engine begrijpt en niet alleen de syntaxis. Precies dat onderscheid gebruiken interviewers om medior- en seniorontwikkelaars van elkaar te onderscheiden.

Er zijn twee varianten, en interviewers letten graag op het verschil.

• EXPLAIN toont het geschatte plan van de planner zonder de query uit te voeren. Snel en veilig.
• EXPLAIN ANALYZE voert de query daadwerkelijk uit en rapporteert naast de schattingen de werkelijke aantallen rijen en tijden.

Het waardevolste inzicht krijg je door geschatte aantallen rijen met werkelijke aantallen te vergelijken. Een groot verschil betekent dat de planner over slechte statistieken beschikt en waarschijnlijk een verkeerde keuze maakt.

Let op: EXPLAIN ANALYZE voert de query echt uit en voert dus ook elke INSERT of UPDATE uit, tenzij de query in een teruggedraaide transactie is verpakt.

Een plan is een boom, geen lijst. De meest ingesprongen knooppunten zijn de bladeren die het eerst worden uitgevoerd; resultaten stromen omhoog naar de wortel, die de uiteindelijke uitvoer produceert.

Lees het van binnen naar buiten: zoek het diepste knooppunt, want daar begint de uitvoering. Elke ouder verwerkt de rijen die zijn kinderen opleveren.

Beschrijf het in een sollicitatiegesprek ook zo: eerst scannen we deze tabel, die rijen gaan naar deze koppeling, de koppeling gaat naar de sortering en de sortering gaat naar de limiet. Die beschrijving van onder naar boven is wat ze willen horen.

Elk knooppunt in een Postgres-plan bevat dezelfde belangrijke getallen:

• cost=0.00..35.50 opstartkosten..totale kosten in willekeurige eenheden van de planner
• rows=1000 geschat aantal geproduceerde rijen
• width=64 geschatte gemiddelde rijgrootte in bytes

De eerste kostenpost is de opstartkost: het werk voordat de eerste rij verschijnt, zoals het opbouwen van een hashtabel. De tweede is de totale kost om alle rijen terug te geven. Hogere totale kosten zijn volgens de planner een aanwijzing voor relatief hogere belasting.

Bekijk een eenvoudige query met een filter. Het onderstaande plan vertelt in één regel een verhaal.

Het is een Seq Scan (de volledige tabel lezen) op orders, waarbij het filter status = 'shipped' wordt toegepast. De planner schat dat 1000 rijen overeenkomen.

Als orders 10 miljoen rijen bevat en er maar 1000 overeenkomen, verwacht een interviewer dat je zegt: een sequentiële scan is hier verspilling; met een index op status (of op een kolom die selectiever is) zouden we kunnen voorkomen dat we de hele tabel lezen.

Met EXPLAIN ANALYZE krijg je tussen haakjes ook werkelijke getallen.

Bekijk het voorbeeld: de planner schatte 1000 rijen, maar kreeg er in werkelijkheid 480000. Dat is een onderschatting met een factor 480. De planner koos zijn strategie in de veronderstelling dat er weinig rijen waren, dus zijn keuze is waarschijnlijk verkeerd voor de echte gegevens.

In sollicitatiegesprekken is dit verschil je belangrijkste diagnose: de statistieken zijn verouderd, voer ANALYZE uit op de tabel; daarna kiest de planner waarschijnlijk een beter plan.

De waarde van loops is belangrijker dan kandidaten verwachten. Het is het aantal keer dat een knooppunt is uitgevoerd.

Dit zie je aan de binnenzijde van een nested loop-join: het binnenste knooppunt wordt één keer uitgevoerd per buitenste rij. Als loops=480000, is die binnenste stap 480 duizend keer uitgevoerd.

Belangrijk: de weergegeven tijd per rij en het aantal rijen gelden per lus. Om het echte totaal te berekenen, vermenigvuldig je met loops. Een knooppunt dat met 0.004 ms per lus goedkoop lijkt, komt over 480000 lussen uit op bijna 2 seconden.

Een veelgemaakte valkuil: kandidaten lezen cost=18334 en zeggen dat duurt 18 seconden. Fout.

De kosten zijn uitgedrukt in willekeurige eenheden van de planner, zo afgesteld dat het lezen van één sequentiële pagina gelijkstaat aan 1.0. Ze zijn alleen betekenisvol voor het vergelijken van plannen onderling, niet als waarde voor de verstreken tijd.

Voor echte tijdmetingen heb je EXPLAIN ANALYZE en de waarden voor actual time nodig, gemeten in milliseconden. Zeg dit duidelijk in een sollicitatiegesprek; het laat zien dat je de metriek echt begrijpt.

Hier is een plan voor twee tabellen. Lees het van onder naar boven.

De eerste twee scans verzamelen rijen uit orders en customers. Ze leveren invoer aan een Hash Join: aan één kant wordt een hash gemaakt en aan de andere kant wordt daarin gezocht. De uitvoer van de join levert vervolgens de uiteindelijke resultaten.

Let op dat de inspringing de structuur laat zien: beide scans staan onder de Hash Join. De interviewer wil dat je de joinmethode herkent (hier een hash) en aangeeft welke tabel wordt gehasht (meestal de kleinere).

Train je oog voor deze waarschuwingssignalen in elk plan:

• Seq Scan on a huge table met een selectief filter; een index kan helpen.
• Estimated rows far from actual; verouderde statistieken.
• Nested Loop with high loops over een grote tabel; vaak ontbreekt er een index op de binnenste joinsleutel.
• Sort or Hash spilling to disk (weergegeven als gebruik van Disk); work_mem is te klein.
• Rows Removed by Filter is zeer hoog; je hebt het grootste deel van de tabel gelezen en weggegooid.

Plannen zijn beschikbaar in verschillende indelingen. De standaardindeling TEXT is wat je in sollicitatiegesprekken hardop leest. Maar je kunt ook gestructureerde uitvoer opvragen.

EXPLAIN (FORMAT JSON) of FORMAT YAML produceert machineleesbare plannen die hulpmiddelen en dashboards kunnen ontleden. Je hebt ze zelden handmatig nodig, maar als je weet dat ze bestaan, laat dat seniorniveau zien.

Voeg opties toe tussen haakjes: EXPLAIN (ANALYZE, BUFFERS). De optie BUFFERS rapporteert cachetreffers tegenover schijflezingen, wat erg waardevol is voor het opsporen van I/O-beperkte query's.

Je kunt nu een plan lezen als een senior:

• EXPLAIN maakt schattingen; EXPLAIN ANALYZE voert uit en meet.
• Lees de boom van onder naar boven; bladeren worden eerst uitgevoerd en de wortel produceert de uitvoer.
• Elk knooppunt toont cost (relatieve eenheden), rows en width; actual time is de werkelijke waarde in milliseconden.
• loops vermenigvuldigt de waarden per lus; let op geneste lussen.
• Het verschil tussen geschatte en werkelijke rijen is je belangrijkste diagnostische signaal.

Vertel het plan hardop en benoem waarschuwingssignalen; dat is gedrag waarmee je het sollicitatiegesprek wint.