Prestaties en queryoptimalisatie in PostgreSQL · Les

MCV- en N-Distinct-correcties

Gebruik ndistinct- en most-common-value-statistieken om schattingen voor joins en groeperingen te verbeteren.

Les 3 van 413 stappen

MCV- en N-Distinct-correcties is een gratis Prestaties en queryoptimalisatie in PostgreSQL-les op CoddyKit. Dit is les 3 van 4. Je kunt 3 lessen uit dit leerpad gratis volledig lezen — daarna ontgrendelt CoddyKit PRO alle lessen, plus praktische oefeningen met een ingebouwde code-editor en een AI-tutor die 24/7 beschikbaar is. Deze les maakt deel uit van het leertraject Prestaties en queryoptimalisatie in PostgreSQL. Je voortgang wordt gesynchroniseerd op het web en in de CoddyKit-app. De cursus Prestaties en queryoptimalisatie in PostgreSQL bevat in totaal 4 lessen.

Waarom schattingen afwijken

De PostgreSQL-planner kiest verbindingsvolgordes, verbindingsmethoden en groeperingsstrategieën op basis van schattingen van het aantal rijen. Als die schattingen onjuist zijn, krijg je geneste lussen over miljoenen rijen of een hashtabel die is afgestemd op de verkeerde kardinaliteit.

Twee statistieken op kolomniveau bepalen het grootste deel van deze schattingen:

n_distinct — hoeveel unieke waarden de planner denkt dat een kolom bevat. Deze waarde beïnvloedt groeperingen en de kardinaliteit van verbindingen.
most_common_vals (MCV) — de lijst met veelvoorkomende waarden en hun frequenties, gebruikt voor de selectiviteit van gelijkheidspredicaten.

In deze les leer je beide statistieken te lezen, problemen vast te stellen en te corrigeren wanneer de standaardsteekproef ze verkeerd bepaalt.

pg_stats lezen

Alles wat de planner over een kolom weet, staat in de weergave pg_stats, een leesbare omhulling rond pg_statistic. Begin elke diagnose hier.

Belangrijke kolommen zijn: n_distinct, most_common_vals, most_common_freqs en 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');

Hoe n_distinct wordt gecodeerd

De waarde n_distinct heeft twee verschillende betekenissen:

Een positief getal is een absoluut aantal unieke waarden (bijvoorbeeld 4200).
Een negatief getal tussen -1 en 0 is een verhouding tussen het aantal unieke waarden en het totale aantal rijen. -1 betekent dat elke rij uniek is; -0.5 betekent dat het aantal unieke waarden de helft van het aantal rijen is.

ANALYZE kiest de negatieve vorm wanneer het aantal unieke waarden mee lijkt te groeien met de tabel, zodat de waarde blijft schalen naarmate de tabel groeit. Dit onderscheid is belangrijk wanneer je de waarde handmatig overschrijft.

Het probleem met steekproeven

ANALYZE schat n_distinct op basis van een willekeurige steekproef (standaard ongeveer 300 × default_statistics_target rijen), niet op basis van een volledige scan. Het aantal unieke waarden uit een steekproef schatten is berucht moeilijk.

De klassieke misser ontstaat bij een kolom met veel unieke waarden die zeer verspreid voorkomen. In de steekproef worden weinig herhalingen gezien, waardoor de schatter het aantal sterk te laag inschat. Een kolom met in werkelijkheid 5 miljoen unieke waarden kan bijvoorbeeld als 50.000 worden geregistreerd.

De planner denkt dan dat een GROUP BY 50.000 groepen oplevert, kiest een hashaggregatie die daarop is afgestemd en schrijft gegevens naar schijf zodra de werkelijkheid 5 miljoen groepen blijkt te bevatten.

Een verkeerde n_distinct herkennen

Vergelijk wat de planner denkt met de werkelijke waarde. Voer een exacte telling van unieke waarden uit en leg die naast pg_stats:

Als n_distinct als een klein positief getal is opgeslagen, maar het werkelijke aantal vele ordes groter is, heb je een te lage schatting. Vergeet niet de negatieve verhouding om te rekenen: werkelijke schatting = -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';

n_distinct overschrijven

Als je de werkelijke kardinaliteit beter kent dan de steekproef ooit kan bepalen, leg je die vast met ALTER TABLE ... ALTER COLUMN ... SET (n_distinct = ...).

Gebruik de negatieve verhouding voor kolommen die meegroeien met de tabel; die blijft bruikbaar tijdens groei. Gebruik een positief geheel getal alleen voor een stabiel, begrensd domein.

De overschrijving wordt opgeslagen in pg_attribute en toegepast bij de volgende ANALYZE. Voer daarom daarna altijd opnieuw een analyse uit.

-- 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 voor partities

Gepartitioneerde tabellen hebben een tweede instelling: n_distinct_inherited. De gewone overschrijving van n_distinct geldt voor de eigen rijen van de tabel; n_distinct_inherited geldt voor statistieken die over de volledige overervings- of partitiestructuur zijn verzameld.

Bij een gepartitioneerde tabel orders lezen query's meestal de bovenliggende tabel. Daarom gebruikt de planner de overgeërfde vorm. Stel voor de zekerheid beide in wanneer een kolom sterk verkeerd wordt geschat.

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

ANALYZE orders;

MCV: selectiviteit van gelijkheid

Bij een gelijkheidspredicaat zoals status = 'shipped' zoekt de planner de waarde op in most_common_vals. Als de waarde wordt gevonden, gebruikt hij direct de bijbehorende frequentie uit most_common_freqs. Als de waarde niet wordt gevonden, gaat hij ervan uit dat het een van de niet-MCV-waarden is en verdeelt hij de resterende selectiviteit gelijkmatig over die waarden.

De nauwkeurigheid van MCV bepaalt dus of een scheef verdeeld predicaat een bruikbare schatting van het aantal rijen krijgt, of een vlak gemiddelde dat voor een veelvoorkomende waarde volledig verkeerd is.

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

Wanneer de MCV-lijst te kort is

De lengte van de MCV-lijst wordt begrensd door het statistiekdoel van de kolom. Als een scheve kolom 200 betekenisvol veelvoorkomende waarden heeft, maar het doel er slechts 100 bewaart, schat de planner de waarden die buiten de lijst vallen verkeerd.

Vergroot het histogram en de MCV-lijst door het statistiekdoel per kolom te verhogen en voer daarna opnieuw een analyse uit. Dit is de meest gebruikelijke correctie met het laagste risico voor scheve schattingen bij gelijkheid en groepering.

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

ANALYZE orders;

De correctie controleren met EXPLAIN

Vertrouw nooit blind op een overschrijving — controleer of de schatting dichter bij de werkelijkheid is gekomen. Voer EXPLAIN ANALYZE uit en vergelijk het door de planner geschatte aantal rijen met het werkelijke aantal rijen dat de uitvoerder zag.

Kijk bij groeperingen naar het aantal rijen dat het knooppunt HashAggregate / GroupAggregate produceert. In een gezond plan liggen de geschatte en werkelijke waarden binnen een kleine factor van elkaar.

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

Gecorreleerde kolommen hebben uitgebreide statistieken nodig

MCV en n_distinct per kolom gaan ervan uit dat kolommen onafhankelijk zijn. Wanneer twee kolommen gecorreleerd zijn (bijvoorbeeld city en country), onderschat het product van de selectiviteiten per kolom het gecombineerde aantal groepen.

Precies dat corrigeert multivariate CREATE STATISTICS ... (ndistinct, mcv): deze opslag bevat een gezamenlijke n_distinct en een gezamenlijke MCV-lijst voor de kolomgroep, waardoor schattingen voor GROUP BY met meerdere kolommen en AND-predicaten worden verbeterd.

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

ANALYZE orders;

Korte controle

Je hebt een kolom met veel unieke waarden waarvan het aantal unieke waarden lineair groeit naarmate de tabel groeit. ANALYZE blijft dit aantal te laag schatten, waardoor plannen met GROUP BY slecht uitpakken. Welke correctie is het beste?

Beantwoord dit in de interactieve les

Samenvatting

Je hebt geleerd hoe je de twee statistieken corrigeert die de meeste fouten in kardinaliteit veroorzaken:

Stel een diagnose in pg_stats: lees n_distinct, most_common_vals en most_common_freqs; vergelijk ze met een exacte count(DISTINCT ...).
n_distinct is positief voor absolute aantallen en negatief voor een verhouding tot het aantal rijen. Overschrijf de waarde met ALTER COLUMN ... SET (n_distinct = ...); gebruik de verhoudingsvorm voor groeiende kolommen en n_distinct_inherited voor gepartitioneerde bovenliggende tabellen.
MCV bepaalt de selectiviteit van gelijkheid. Maak de lijst langer met SET STATISTICS wanneer scheef verdeelde waarden buiten de lijst vallen.
Voer na elke wijziging ANALYZE uit en controleer met EXPLAIN ANALYZE of het geschatte aantal rijen nu overeenkomt met het werkelijke aantal.
Gebruik voor gecorreleerde kolommen multivariate CREATE STATISTICS (ndistinct, mcv).

Gratis beginnen

Leer SQL met een AI-tutor — gratis

Schrijf echte code en voer die uit in je browser, krijg direct hulp van een AI-tutor die 24/7 beschikbaar is en ga verder waar je gebleven bent op het web of in de app.

Cursussen: 22
Lessen: 88

Gratis beginnen met leren Bekijk de volledige cursus

Veelgestelde vragen

Is de les “MCV- en N-Distinct-correcties” gratis?

Ja — je kunt hier op het web alle 3 lessen van het leerpad Prestaties en queryoptimalisatie in PostgreSQL, waaronder “MCV- en N-Distinct-correcties”, gratis volledig lezen. Daarna ontgrendelt CoddyKit PRO alle lessen, plus interactieve oefeningen met een ingebouwde code-editor en een AI-tutor die 24/7 beschikbaar is. De cursus Prestaties en queryoptimalisatie in PostgreSQL bevat in totaal 4 lessen.

Wat leer ik in “MCV- en N-Distinct-correcties”?

Gebruik ndistinct- en most-common-value-statistieken om schattingen voor joins en groeperingen te verbeteren. Je oefent met Prestaties en queryoptimalisatie in PostgreSQL door code rechtstreeks in de browser uit te voeren. Een AI-begeleider die 24/7 beschikbaar is beantwoordt je vragen terwijl je de les doorwerkt.

Heb ik ervaring nodig om met Prestaties en queryoptimalisatie in PostgreSQL te beginnen?

Ervaring vooraf is niet nodig. Prestaties en queryoptimalisatie in PostgreSQL op CoddyKit is opgebouwd voor beginners tot gevorderden, zodat je hier of bij het begin kunt starten en in je eigen tempo kunt leren. Dit is les 3 van 4.

Hoe lang duurt de les “MCV- en N-Distinct-correcties”?

De meeste lessen van CoddyKit duren ongeveer 5–10 minuten. Elke les is kort en interactief, zodat je gestaag vooruitgaat en op het web en in de app precies verdergaat waar je was gebleven.

Kan ik code schrijven en uitvoeren in deze les over Prestaties en queryoptimalisatie in PostgreSQL?

Ja. Elke les over Prestaties en queryoptimalisatie in PostgreSQL bevat een ingebouwde code-editor, zodat je rechtstreeks in je browser echte code kunt schrijven en uitvoeren en direct feedback van AI krijgt — lokale installatie is niet nodig.

Alle lessen in deze cursus

Hoe de planner aantallen rijen schat
Multivariate statistieken voor gecorreleerde kolommen
MCV- en N-Distinct-correcties
Schattingen vergelijken met werkelijke rijen

← Terug naar Prestaties en queryoptimalisatie in PostgreSQL