Läsa en EXPLAIN-plan

När ni kommer till en intervju på seniornivå slutar intervjuare att fråga skriv en fråga och börjar i stället fråga varför är den här frågan långsam. Verktyget som besvarar det är EXPLAIN.

EXPLAIN visar databasens exekveringsplan: den stegvisa strategi som frågeplaneraren valde för att köra er SQL. Den visar vilka tabeller som skannas, i vilken ordning de kopplas ihop och ungefär hur kostsamt varje steg är.

Att kunna läsa en plan visar att ni förstår databasmotorn, inte bara syntaxen. Det är precis den skiljelinje intervjuare använder för att skilja medelnivå från seniornivå.

Det finns två varianter, och intervjuare uppskattar skillnaden.

• EXPLAIN visar frågeplanerarens uppskattade plan utan att köra frågan. Det är snabbt och säkert.
• EXPLAIN ANALYZE kör faktiskt frågan och rapporterar de verkliga radantalen och tidsåtgången tillsammans med uppskattningarna.

Det mest värdefulla är att jämföra uppskattat antal rader med faktiskt antal rader. En stor avvikelse betyder att frågeplaneraren har dålig statistik och sannolikt fattar ett dåligt beslut.

Var försiktiga: EXPLAIN ANALYZE kör frågan på riktigt, så alla INSERT- eller UPDATE-operationer utförs om frågan inte omsluts av en transaktion som rullas tillbaka.

En plan är ett träd, inte en lista. De mest indragna noderna är löven som körs först; resultaten flödar uppåt till roten, som producerar det slutliga resultatet.

Läs den inifrån och ut: hitta den djupast nästlade noden; där börjar exekveringen. Varje överordnad nod bearbetar de rader som dess underordnade noder producerar.

Berätta det på samma sätt i en intervju: först skannar vi den här tabellen, de raderna matas in i den här JOIN-operationen, JOIN-resultatet matas in i sorteringen och sorteringen matar LIMIT. Den förklaringen nerifrån och upp är vad intervjuaren vill höra.

Varje nod i en Postgres-plan innehåller samma nyckeltal:

• cost=0.00..35.50 startkostnad..totalkostnad i godtyckliga planeringsenheter
• rows=1000 uppskattat antal rader som produceras
• width=64 uppskattad genomsnittlig radstorlek i byte

Den första kostnaden är startkostnaden (arbetet innan den första raden visas, till exempel att bygga en hashtabell). Den andra är totalkostnaden för att returnera alla rader. En högre totalkostnad är planerarens uppskattning av den relativa kostnaden.

Föreställ er en enkel filtrerad fråga. Planen nedan berättar hela historien på en rad.

Det är en Seq Scan (fullständig tabelläsning) på orders, där filtret status = 'shipped' tillämpas. Planeraren uppskattar 1000 matchande rader.

Om orders innehåller 10 miljoner rader och endast 1000 matchar, förväntar sig intervjuaren att ni säger: en sekventiell genomsökning är slösaktig här; ett index på status (eller på en mer selektiv kolumn) skulle göra att vi slipper läsa hela tabellen.

Med EXPLAIN ANALYZE får ni även faktiska värden inom parentes.

Titta på exemplet: planeraren uppskattade 1000 rader men fick faktiskt 480000. Det är en underskattning med 480 gånger. Planeraren valde strategi utifrån antagandet att få rader skulle returneras, så valet är troligen fel för de verkliga data.

I intervjuer är detta avstånd er viktigaste diagnos: statistiken är inaktuell; kör ANALYZE på tabellen, så kommer planeraren sannolikt att välja en bättre plan.

Värdet på loops är viktigare än många kandidater tror. Det anger hur många gånger en nod kördes.

Detta visas på den inre sidan av en nested loop-join: den inre noden körs en gång för varje yttre rad. Om loops=480000 kördes det inre steget 480 000 gånger.

Viktigt: tiden per rad och radantalet som visas gäller per loop. För att få den verkliga totalen multiplicerar ni med loops. En nod som verkar billig med 0,004 ms per loop blir nästan 2 sekunder över 480 000 loopar.

En vanlig fälla: kandidater ser cost=18334 och säger det tar 18 sekunder. Fel.

Kostnaden anges i godtyckliga planeringsenheter, kalibrerade så att en sekventiell sidläsning motsvarar 1,0. Den är endast meningsfull för att jämföra planer med varandra, inte som ett mått på faktisk körtid.

För verklig tidsmätning behöver ni EXPLAIN ANALYZE och dess värden för actual time, uppmätta i millisekunder. Säg detta tydligt i en intervju; det visar att ni faktiskt förstår mätvärdet.

Här är en plan för två tabeller. Läs den nerifrån och upp.

De två första genomsökningarna hämtar rader från orders och customers. De matar in raderna i en Hash Join: den ena sidan hashas och den andra slår upp värden i hashen. Joinens resultat matas sedan vidare till det slutliga resultatet.

Observera att indragningen visar strukturen: båda genomsökningarna ligger under Hash Join. Intervjuaren vill att ni identifierar join-metoden (hash i detta fall) och vilken tabell som hashas (vanligen den mindre).

Träna upp blicken för dessa varningstecken i alla planer:

• Seq Scan på en enorm tabell med ett selektivt filter; ett index kan hjälpa.
• Uppskattat antal rader långt från det faktiska, inaktuell statistik.
• Nested Loop med många loops över en stor tabell; ofta saknas ett index på den inre join-nyckeln.
• Sort eller Hash som spiller till disk (visas som användning av Disk); work_mem är för litet.
• Rows Removed by Filter är mycket högt; större delen av tabellen läses och förkastas.

Planer finns i flera format. Standardformatet TEXT är det ni läser upp i intervjuer. Ni kan också begära strukturerad utdata.

EXPLAIN (FORMAT JSON) eller FORMAT YAML producerar maskinläsbara planer som verktyg och kontrollpaneler kan tolka. Ni behöver sällan hantera dem manuellt, men att känna till att de finns är ett trevligt tecken på senior nivå.

Ange alternativ inom parentes: EXPLAIN (ANALYZE, BUFFERS). Alternativet BUFFERS rapporterar cacheträffar jämfört med diskläsningar, vilket är ovärderligt vid diagnostisering av I/O-bundna frågor.

Nu kan ni läsa en plan som en senior utvecklare:

• EXPLAIN gör uppskattningar, medan EXPLAIN ANALYZE kör och mäter.
• Läs trädet nerifrån och upp; löven körs först och roten producerar utdata.
• Varje nod visar kostnad (relativa enheter), rader och bredd; actual time är den verkliga tiden i millisekunder.
• loops multiplicerar värden per loop; var uppmärksam på nästlade loopar.
• Skillnaden mellan uppskattat och faktiskt radantal är er viktigaste diagnostiska signal.

Berätta om planen högt och påpeka varningssignalerna; det är beteendet som ger utdelning på intervjun.