Hur planeraren uppskattar antal rader

Frågeplaneraren tittar inte på dina data när planen skapas. Den läser förberäknade sammanfattningar från systemkatalogen pg_statistic, som fylls i av ANALYZE (körs automatiskt av autovacuum). Den läsbara vyn över katalogen är pg_stats.

För varje kolumn visar pg_stats byggstenarna för uppskattningen:

• null_frac — andelen NULL-värden.
• n_distinct — antalet distinkta värden.
• most_common_vals / most_common_freqs — MCV-listan.
• histogram_bounds — intervall för återstoden som inte finns i MCV-listan.

Selektivitet är den andel rader som ett predikat uppskattas behålla, mellan 0 och 1. Det uppskattade antalet rader är helt enkelt:

• estimated_rows = selectivity × total_rows

där total_rows hämtas från pg_class.reltuples (som också uppdateras av ANALYZE). Uppskattningen kan alltså reduceras till två frågor: hur många rader har tabellen och hur stor andel överlever varje predikat? Allt annat handlar om hur den andelen beräknas.

För column = 'value' kontrollerar frågeplaneraren först listan över most_common_vals (MCV). Om värdet finns där använder den den exakta frekvensen från most_common_freqs — ingen matematik, bara en uppslagning.

Exempel: om most_common_vals = {shipped, pending, cancelled} och most_common_freqs = {0.62, 0.25, 0.08}, har status = 'shipped' selektiviteten 0.62. I en tabell med 1 000 000 rader innebär det en uppskattning på 620 000 rader.

MCV-värden gör att snedfördelade distributioner kan uppskattas korrekt — frågeplaneraren känner exakt till hur vanliga de mest frekventa värdena är.

Om värdet inte finns i MCV-listan antar frågeplaneraren att alla värden som inte finns i MCV-listan är lika sannolika. Den beräknar den återstående sannolikhetsmassan och fördelar den jämnt:

• residual = 1 − sum(most_common_freqs) − null_frac
• n_distinct_residual = n_distinct − count(MCVs)
• selectivity = residual / n_distinct_residual

Därför kan uppskattningar av ovanliga värden bli dåliga när även den långa svansen är snedfördelad: antagandet om en jämn fördelning inom resten håller inte. MCV-listan täcker toppen; resten är en förenklad, jämn approximation av svansen.

För olikheter som amount > 500 eller created_at BETWEEN … använder frågeplaneraren histogram_bounds. Dessa gränser delar upp värdena som inte finns i MCV-listan i intervall som vart och ett innehåller ungefär samma antal rader (lika djup, inte lika bredd).

För att uppskatta amount < X hittar den var X hamnar bland gränserna och interpolerar linjärt inom det intervall som innehåller värdet. Med N intervall representerar varje intervall ungefär 1/N av raderna som inte finns i MCV-listan. Frågeplaneraren räknar därför hela intervall under X samt en del av gränsintervallet.

Med flera AND-villkor på olika kolumner multiplicerar PostgreSQL deras selektiviteter och antar att kolumnerna är statistiskt oberoende:

• sel(A AND B) = sel(A) × sel(B)

Om status = 'shipped' är 0.62 och country = 'DE' är 0.10 uppskattar frågeplaneraren att 0.062 av tabellen återstår. Men om skickade beställningar till största delen är tyska kan den verkliga andelen vara 0.30 — en underskattning med en faktor på 5. Korrelerade kolumner är där statistik för enskilda kolumner inte räcker till och planerna faller samman.

När kolumner är korrelerade skapar du utökad statistik med CREATE STATISTICS. Statistiktypen dependencies lär frågeplaneraren funktionella beroenden, medan mcv lagrar kombinationer av de vanligaste värdena i flera kolumner så att AND-predikat uppskattas gemensamt i stället för att multipliceras.

När objektet har skapats måste du köra ANALYZE på tabellen för att fylla det. Därefter läser frågeplaneraren den gemensamma fördelningen och slutar anta oberoende för dessa kolumner.

Antalet rader i joinar bygger vidare på uppskattningarna för de enskilda tabellerna. För en equi-join uppskattar PostgreSQL ungefär resultatet så här:

• rows ≈ (outer_rows × inner_rows) / max(n_distinct_outer, n_distinct_inner)

med n_distinct för joinnyckeln från respektive sida. Därför får en felaktig uppskattning för en enskild tabell en kaskadeffekt: om frågeplaneraren tror att den filtrerade sidan har 5 rader när den i själva verket har 50 000, ärver varje överordnad join felet och kan välja en nästlad loop som körs 50 000 gånger i stället för en hash-join.

Uppskattningar är bara så bra som statistiken de bygger på. Praktiska åtgärder:

• Kör ANALYZE efter massinläsningar och låt autovacuum hålla statistiken aktuell.
• Öka upplösningen för snedfördelade kolumner med ALTER TABLE … ALTER COLUMN … SET STATISTICS n (större MCV-lista och histogram).
• Lägg till CREATE STATISTICS för grupper av korrelerade kolumner.
• Jämför uppskattade och faktiska radantal i EXPLAIN ANALYZE för att hitta noden där uppskattningen först blir fel.

Felsök från planens nedersta del och uppåt: den första noden med en stor skillnad mellan uppskattat och faktiskt antal är vanligtvis den verkliga orsaken.

Ni kan nu följa en raduppskattning från början till slut:

• ANALYZE fyller i pg_statistic / pg_stats och anger reltuples.
• Likhet använder MCV-listan när värdet är vanligt och annars den jämna resten över n_distinct.
• Intervall interpoleras inom histogram_bounds med lika djupa intervall.
• Flera AND-villkor multiplicerar selektiviteter under antagandet om oberoende — den främsta källan till uppskattningsfel.
• Utökad statistik (dependencies, mcv) korrigerar för korrelerade kolumner.
• Joinar kombinerar uppskattningarna från respektive sida med joinnyckelns n_distinct, så fel för enskilda tabeller fortplantas uppåt.

Selektivitet × radantal ger de kardinaliteter som styr val av skanning, join och sorteringsordning. Bemästra detta, så slutar EXPLAIN-utdata att vara mystisk — den blir en berättelse som Ni kan läsa.