Prestandaoptimering och frågeoptimering i PostgreSQL · Lektion

MCV- och N-Distinct-korrigeringar

Använd ndistinct- och statistik över vanligaste värden för att korrigera uppskattningar av joiner och grupperingar.

Lektion 3 av 413 steg

MCV- och N-Distinct-korrigeringar är en gratis lektion i Prestandaoptimering och frågeoptimering i PostgreSQL på CoddyKit. Detta är lektion 3 av 4. Du kan läsa vilka 3 lektioner som helst i den här lärvägen kostnadsfritt i sin helhet – därefter låser CoddyKit PRO upp alla lektioner, plus praktisk övning med en inbyggd kodredigerare och en AI-lärare dygnet runt. Den ingår i lärvägen för Prestandaoptimering och frågeoptimering i PostgreSQL, och Era framsteg synkroniseras mellan webben och CoddyKit-appen. Kursen i Prestandaoptimering och frågeoptimering i PostgreSQL innehåller totalt 4 lektioner.

Varför uppskattningar förändras

PostgreSQL-planeraren väljer joinordning, joinmetoder och grupperingsstrategier utifrån uppskattat antal rader. När uppskattningarna är felaktiga får ni nästlade loopar över miljontals rader eller en hashtabell dimensionerad för fel kardinalitet.

Två statistikkolumner på kolumnnivå styr de flesta av dessa uppskattningar:

n_distinct — hur många distinkta värden planeraren antar att en kolumn innehåller. Det används vid gruppering och för kardinaliteten i joiner.
most_common_vals (MCV) — listan över vanliga värden och deras frekvenser, som används för selektiviteten hos likhetspredikat.

I den här lektionen får ni lära er att läsa, diagnostisera och korrigera båda när standardurvalet ger fel värden.

Läsa pg_stats

Allt som planeraren vet om en kolumn finns i vyn pg_stats, ett läsbart omslag runt pg_statistic. Börja alltid diagnostiseringen här.

Viktiga kolumner: n_distinct, most_common_vals, most_common_freqs och null_frac.

SELECT attname,
       n_distinct,
       null_frac,
       most_common_vals,
       most_common_freqs
FROM pg_stats
WHERE schemaname = 'public'
  AND tablename = 'orders'
  AND attname IN ('customer_id', 'status');

Så kodas n_distinct

Värdet n_distinct har två betydelser:

Ett positivt tal är ett absolut antal distinkta värden (t.ex. 4200).
Ett negativt tal mellan -1 och 0 är ett förhållande mellan antalet distinkta värden och det totala antalet rader. -1 betyder att varje rad är unik; -0.5 betyder att antalet distinkta värden är hälften av antalet rader.

Den negativa formen väljs av ANALYZE när antalet distinkta värden verkar öka med tabellen, så att värdet kan skalas när tabellen växer. Denna skillnad är viktig när ni åsidosätter värdet manuellt.

Problemet med urvalet

ANALYZE uppskattar n_distinct från ett slumpmässigt urval (som standard ungefär 300 × default_statistics_target rader), inte genom en fullständig genomsökning. Att uppskatta antalet distinkta värden från ett urval är notoriskt svårt.

Det klassiska felet uppstår i en kolumn med hög kardinalitet där distinkta värden är glest fördelade. Urvalet ser få upprepningar, så skattningen räknar kraftigt för lågt. En kolumn med 5 miljoner verkligt distinkta värden kan registreras som 50 000.

Planeraren tror då att en GROUP BY skapar 50 000 grupper, väljer ett hashaggregat dimensionerat för detta och spiller till disk när verkligheten visar sig vara 5 miljoner.

Identifiera ett felaktigt n_distinct

Jämför vad planeraren tror med det faktiska utfallet. Kör en exakt räkning av distinkta värden och jämför den med pg_stats:

Om n_distinct lagras som ett litet positivt tal men det verkliga antalet är många storleksordningar större har ni en underskattning. Kom ihåg att omvandla den negativa kvotformen: verklig uppskattning = -n_distinct × reltuples.

-- ground truth
SELECT count(DISTINCT customer_id) AS real_distinct
FROM orders;

-- what the planner thinks
SELECT n_distinct
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'customer_id';

Åsidosätta n_distinct

När ni känner till den verkliga kardinaliteten bättre än vad urvalsmetoden någonsin kan göra, låser ni den med ALTER TABLE ... ALTER COLUMN ... SET (n_distinct = ...).

Använd den negativa kvotformen för kolumner som skalar med tabellstorleken — den fungerar även när tabellen växer. Använd ett positivt heltal endast för en stabil, begränsad domän.

Åsidosättningen lagras i pg_attribute och tillämpas vid nästa ANALYZE, så kör alltid en ny analys efteråt.

-- distinct count grows ~linearly with rows: use the ratio form
ALTER TABLE orders
  ALTER COLUMN customer_id SET (n_distinct = -0.8);

ANALYZE orders;

n_distinct_inherited för partitioner

Partitionerade tabeller har ytterligare en inställning: n_distinct_inherited. Den vanliga åsidosättningen av n_distinct gäller tabellens egna rader, medan n_distinct_inherited gäller statistik som samlas in över hela arv-/partitionsträdet.

För en partitionerad tabell orders söker frågor vanligtvis i den överordnade tabellen, så det är den ärvda formen som planeraren läser. Ange båda för säkerhets skull när en kolumn uppskattas kraftigt fel.

ALTER TABLE orders
  ALTER COLUMN customer_id SET (n_distinct_inherited = -0.8);

ANALYZE orders;

MCV: selektivitet för likhet

För ett likhetspredikat som status = 'shipped' letar planeraren efter värdet i most_common_vals. Om det hittas använder den direkt den tillhörande frekvensen från most_common_freqs. Om det inte hittas antar den att värdet är ett av de värden som inte finns i MCV och fördelar den återstående selektiviteten jämnt mellan dem.

MCV-statistikens noggrannhet avgör alltså om ett skevt predikat får en rimlig uppskattning av antalet rader eller ett platt genomsnitt som blir helt fel för ett vanligt värde.

SELECT unnest(most_common_vals::text::text[]) AS val,
       unnest(most_common_freqs)            AS freq
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

När MCV-listan är för kort

MCV-listans längd begränsas av kolumnens statistikmål. Om en skev kolumn har 200 betydelsefullt vanliga värden men målet bara behåller 100, uppskattar planeraren värdena som föll bort från listan fel.

Lösningen är att utöka histogrammet och MCV-listan genom att höja statistikmålet för kolumnen och sedan köra en ny analys. Detta är den vanligaste korrigeringen med lägst risk för skeva uppskattningar av likhet och gruppering.

-- keep up to 1000 MCV entries + histogram buckets for this column
ALTER TABLE orders
  ALTER COLUMN status SET STATISTICS 1000;

ANALYZE orders;

Verifiera korrigeringen med EXPLAIN

Lita aldrig blint på en åsidosättning — bekräfta att uppskattningen har närmat sig verkligheten. Kör EXPLAIN ANALYZE och jämför planerarens uppskattade antal rader med det faktiska antalet rader som exekveringen såg.

Vid gruppering granskar ni antalet rader som noden HashAggregate / GroupAggregate producerar. I en välfungerande plan skiljer sig uppskattat och faktiskt antal endast med en liten faktor.

EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, count(*)
FROM orders
GROUP BY customer_id;

Korrelerade kolumner behöver utökad statistik

MCV och n_distinct per kolumn förutsätter att kolumner är oberoende. När två kolumner är korrelerade (t.ex. city och country) underskattar produkten av selektiviteterna för de enskilda kolumnerna antalet kombinerade grupper.

Det är precis detta som multivariat CREATE STATISTICS ... (ndistinct, mcv) korrigerar — den lagrar ett gemensamt n_distinct och en gemensam MCV-lista för kolumngruppen och korrigerar uppskattningarna för GROUP BY med flera kolumner och AND-predikat.

CREATE STATISTICS orders_geo (ndistinct, mcv)
  ON city, country
  FROM orders;

ANALYZE orders;

Snabbkontroll

Ni har en kolumn med hög kardinalitet vars antal distinkta värden växer linjärt när tabellen växer, och ANALYZE fortsätter att underskatta det, vilket förstör GROUP BY-planerna. Vilken korrigering är bäst?

Besvara frågan i den interaktiva lektionen

Sammanfattning

Ni har lärt er att korrigera de två statistikvärden som orsakar de flesta kardinalitetsfelen:

Diagnostisera i pg_stats: läs n_distinct, most_common_vals och most_common_freqs; jämför med en exakt count(DISTINCT ...).
n_distinct är positivt för absoluta antal och negativt för en radkvot. Åsidosätt värdet med ALTER COLUMN ... SET (n_distinct = ...). Använd kvotformen för växande kolumner och n_distinct_inherited för partitionerade överordnade tabeller.
MCV styr selektiviteten för likhet. Förläng listan med SET STATISTICS när skeva värden faller bort från den.
Kör alltid ANALYZE efter en ändring och bekräfta med EXPLAIN ANALYZE att det uppskattade antalet rader nu följer det faktiska.
För korrelerade kolumner använder ni multivariat CREATE STATISTICS (ndistinct, mcv).

Gratis att börja

Lär dig SQL med en AI-lärare – gratis

Skriv och kör riktig kod i webbläsaren, få omedelbar hjälp av en AI-lärare dygnet runt och fortsätt där du slutade – på webben eller i appen.

Kurser: 22
Lektioner: 88

Börja lära dig gratis Visa hela kursen

Vanliga frågor

Är lektionen ”MCV- och N-Distinct-korrigeringar” gratis?

Ja – du kan läsa vilka 3 lektioner som helst i lärvägen Prestandaoptimering och frågeoptimering i PostgreSQL, inklusive ”MCV- och N-Distinct-korrigeringar”, kostnadsfritt i sin helhet här på webben. Därefter låser CoddyKit PRO upp alla lektioner, plus interaktiv övning med en inbyggd kodredigerare och en AI-lärare dygnet runt. Kursen i Prestandaoptimering och frågeoptimering i PostgreSQL innehåller totalt 4 lektioner.

Vad lär jag mig i ”MCV- och N-Distinct-korrigeringar”?

Använd ndistinct- och statistik över vanligaste värden för att korrigera uppskattningar av joiner och grupperingar. Ni övar på Prestandaoptimering och frågeoptimering i PostgreSQL med praktisk kod som körs direkt i webbläsaren, medan en AI-handledare som är tillgänglig dygnet runt svarar på Era frågor under lektionen.

Behöver jag någon erfarenhet för att börja lära mig Prestandaoptimering och frågeoptimering i PostgreSQL?

Du behöver inga förkunskaper. Utbildningen i Prestandaoptimering och frågeoptimering i PostgreSQL på CoddyKit är upplagd för allt från nybörjare till avancerade elever, så att du kan börja här eller från början och gå fram i din egen takt. Detta är lektion 3 av 4.

Hur lång tid tar lektionen ”MCV- och N-Distinct-korrigeringar”?

De flesta CoddyKit-lektioner tar cirka 5–10 minuter. Varje lektion är kort och interaktiv, så att du gör stadiga framsteg och kan fortsätta precis där du slutade – på webben eller i appen.

Kan jag skriva och köra kod i den här Prestandaoptimering och frågeoptimering i PostgreSQL-lektionen?

Ja. Varje Prestandaoptimering och frågeoptimering i PostgreSQL-lektion innehåller en inbyggd kodredigerare, så att du kan skriva och köra riktig kod direkt i webbläsaren och få omedelbar AI-feedback – utan lokal installation.

Alla lektioner i den här kursen

Hur planeraren uppskattar antal rader
Multivariata statistiska uppgifter för korrelerade kolumner
MCV- och N-Distinct-korrigeringar
Validera uppskattningar mot faktiska rader

← Tillbaka till Prestandaoptimering och frågeoptimering i PostgreSQL