Ydelsesoptimering og optimering af forespørgsler i PostgreSQL · Lektion

MCV- og N-Distinct-korrektioner

Brug ndistinct- og most-common-value-statistikker til at forbedre estimater for joins og grupperinger.

Lektion 3 af 413 trin

MCV- og N-Distinct-korrektioner er en gratis Ydelsesoptimering og optimering af forespørgsler i PostgreSQL-lektion på CoddyKit. Dette er lektion 3 af 4. Du kan læse alle 3 lektioner i dette læringsspor gratis i deres fulde længde — derefter låser CoddyKit PRO alle lektioner op samt praktiske øvelser med en indbygget kodeeditor og en AI-underviser døgnet rundt. Den er en del af læringsforløbet i Ydelsesoptimering og optimering af forespørgsler i PostgreSQL, og dine fremskridt synkroniseres på tværs af nettet og CoddyKit-appen. Ydelsesoptimering og optimering af forespørgsler i PostgreSQL-kurset indeholder 4 lektioner i alt.

Hvorfor estimater ændrer sig

PostgreSQL-planlæggeren vælger join-rækkefølge, join-metoder og grupperingsstrategier ud fra estimater for antal rækker. Når estimaterne er forkerte, får du indlejrede løkker over millioner af rækker eller en hashtabel, der er dimensioneret til den forkerte kardinalitet.

To kolonnestatistikker driver de fleste af disse estimater:

n_distinct — hvor mange forskellige værdier planlæggeren mener, at en kolonne indeholder. Den bruges til gruppering og join-kardinalitet.
most_common_vals (MCV) — listen over hyppige værdier og deres frekvenser, som bruges til selektiviteten af lighedsprædikater.

I denne lektion lærer du at læse, diagnosticere og korrigere begge dele, når standardudtagningen giver forkerte resultater.

Læsning af pg_stats

Alt, hvad planlæggeren ved om en kolonne, findes i visningen pg_stats, som er en læsevenlig indpakning af pg_statistic. Start altid din diagnosticering her.

Vigtige kolonner er: n_distinct, most_common_vals, most_common_freqs og 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ådan er n_distinct kodet

Værdien n_distinct har to betydninger:

Et positivt tal er et absolut antal forskellige værdier (f.eks. 4200).
Et negativt tal mellem -1 og 0 er forholdet mellem antallet af forskellige værdier og det samlede antal rækker. -1 betyder, at hver række er unik; -0.5 betyder, at antallet af forskellige værdier er halvdelen af antallet af rækker.

Den negative form vælges af ANALYZE, når antallet af forskellige værdier ser ud til at vokse med tabellen, så den skalerer, efterhånden som tabellen vokser. Denne forskel er vigtig, når du tilsidesætter værdien manuelt.

Problemet med udtagning

ANALYZE estimerer n_distinct ud fra en tilfældig stikprøve (som standard cirka 300 × default_statistics_target rækker), ikke ud fra en fuld gennemgang. Det er notorisk svært at estimere antallet af forskellige værdier ud fra en stikprøve.

Det klassiske problem opstår i en kolonne med høj kardinalitet, hvor de forskellige værdier er spredt tyndt. Stikprøven ser få gentagelser, så estimatoren tæller kraftigt for lavt. En kolonne med 5 millioner reelt forskellige værdier kan blive registreret som 50.000.

Planlæggeren tror derefter, at en GROUP BY producerer 50.000 grupper, vælger en hash-aggregation dimensioneret til det antal og skriver til disken, når virkeligheden rammer 5 millioner.

Find et forkert n_distinct

Sammenlign det, planlæggeren tror, med den faktiske sandhed. Kør en nøjagtig optælling af forskellige værdier, og hold resultatet op mod pg_stats:

Hvis n_distinct er gemt som et lille positivt tal, men det reelle antal er mange størrelsesordener større, har du en undervurdering. Husk at omregne den negative forholdsform: reelt estimat = -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';

Tilsidesættelse af n_distinct

Når du kender den sande kardinalitet bedre, end stikprøven nogensinde kan gøre, kan du fastlåse den med ALTER TABLE ... ALTER COLUMN ... SET (n_distinct = ...).

Brug den negative forholdsform for kolonner, der skalerer med tabellens størrelse — den overlever vækst. Brug kun et positivt heltal for et stabilt, afgrænset værdiområde.

Tilsidesættelsen gemmes i pg_attribute og anvendes ved den næste ANALYZE, så kør altid en ny analyse bagefter.

-- 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 for partitioner

Partitionerede tabeller har en ekstra indstilling: n_distinct_inherited. Den almindelige tilsidesættelse af n_distinct gælder tabellens egne rækker; n_distinct_inherited gælder statistik indsamlet på tværs af hele nedarvnings-/partitionstræet.

For en partitioneret orders-tabel scanner forespørgsler normalt overtabellen, så det er den nedarvede form, planlæggeren læser. Sæt begge for en sikkerheds skyld, når en kolonne estimeres meget forkert.

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

ANALYZE orders;

MCV: Selektivitet for lighed

For et lighedsprædikat som status = 'shipped' leder planlæggeren efter værdien i most_common_vals. Hvis den findes, bruger planlæggeren den tilsvarende frekvens fra most_common_freqs direkte. Hvis den ikke findes, antager planlæggeren, at værdien er blandt de ikke-MCV-værdier, og fordeler den resterende selektivitet ligeligt mellem dem.

MCV-nøjagtigheden afgør derfor, om et skævt prædikat får et fornuftigt estimat for antal rækker eller et fladt gennemsnit, der er helt forkert for en hyppig værdi.

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-listen er for kort

MCV-listens længde begrænses af kolonnens statistikmål. Hvis en skæv kolonne har 200 betydeligt hyppige værdier, men målet kun gemmer 100, estimerer planlæggeren værdierne, der faldt ud af listen, forkert.

Løsningen er at gøre histogrammet og MCV-listen større ved at hæve statistikmålet for den enkelte kolonne og derefter køre en ny analyse. Det er den mest almindelige korrektion med lavest risiko for skæve estimater ved lighed og gruppering.

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

ANALYZE orders;

Bekræft rettelsen med EXPLAIN

Stol aldrig blindt på en tilsidesættelse — bekræft, at estimatet bevægede sig mod virkeligheden. Kør EXPLAIN ANALYZE, og sammenlign planlæggerens estimerede antal rækker med det faktiske antal rækker, som udføreren så.

Ved gruppering skal du se på det antal rækker, som HashAggregate / GroupAggregate-noden udsender. En sund plan har estimerede og faktiske værdier inden for en lille faktor af hinanden.

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

Korrelerede kolonner kræver udvidet statistik

MCV og n_distinct pr. kolonne antager, at kolonner er uafhængige. Når to kolonner er korrelerede (f.eks. city og country), undervurderer produktet af selektiviteten for de enkelte kolonner det samlede antal grupper.

Det er præcis det, som den multivariate CREATE STATISTICS ... (ndistinct, mcv) retter — den gemmer en fælles n_distinct og en fælles MCV-liste for kolonnegruppen og retter estimater for GROUP BY med flere kolonner og AND-prædikater.

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

ANALYZE orders;

Hurtigt tjek

Du har en kolonne med høj kardinalitet, hvor antallet af forskellige værdier vokser lineært, efterhånden som tabellen vokser, og ANALYZE bliver ved med at undervurdere det, så GROUP BY-planerne ødelægges. Hvilken korrektion er bedst?

Besvar den i den interaktive lektion

Opsummering

Du har lært at rette de to statistikker, der forårsager de fleste kardinalitetsfejl:

Diagnosticér i pg_stats: læs n_distinct, most_common_vals og most_common_freqs, og sammenlign med en nøjagtig count(DISTINCT ...).
n_distinct er positivt for absolutte antal og negativt for et forhold til antallet af rækker. Tilsidesæt det med ALTER COLUMN ... SET (n_distinct = ...); brug forholdsformen for kolonner i vækst og n_distinct_inherited for partitionerede overtabeller.
MCV styrer selektiviteten ved lighed. Gør listen længere med SET STATISTICS, når skæve værdier falder ud af listen.
Kør altid ANALYZE efter enhver ændring, og bekræft med EXPLAIN ANALYZE, at det estimerede antal rækker nu følger det faktiske.
Brug multivariat CREATE STATISTICS (ndistinct, mcv) til korrelerede kolonner.

Gratis at komme i gang

Lær SQL med en AI-underviser — gratis

Skriv og kør rigtig kode i din browser, få øjeblikkelig hjælp fra en AI-underviser døgnet rundt, og fortsæt, hvor du slap, på web eller i appen.

Kurser: 22
Lektioner: 88

Start gratis Se hele kurset

Ofte stillede spørgsmål

Er lektionen “MCV- og N-Distinct-korrektioner” gratis?

Ja — alle 3 lektioner i læringssporet Ydelsesoptimering og optimering af forespørgsler i PostgreSQL, inklusive “MCV- og N-Distinct-korrektioner”, kan læses gratis i deres fulde længde her på webstedet. Derefter låser CoddyKit PRO alle lektioner op samt interaktive øvelser med en indbygget kodeeditor og en AI-underviser døgnet rundt. Ydelsesoptimering og optimering af forespørgsler i PostgreSQL-kurset indeholder 4 lektioner i alt.

Hvad lærer jeg i “MCV- og N-Distinct-korrektioner”?

Brug ndistinct- og most-common-value-statistikker til at forbedre estimater for joins og grupperinger. Du øver dig i Ydelsesoptimering og optimering af forespørgsler i PostgreSQL med praktisk kode, som du kører direkte i browseren, og en AI-vejleder døgnet rundt besvarer dine spørgsmål, mens du arbejder dig gennem lektionen.

Skal jeg have erfaring for at begynde på Ydelsesoptimering og optimering af forespørgsler i PostgreSQL?

Der kræves ingen tidligere erfaring. Ydelsesoptimering og optimering af forespørgsler i PostgreSQL på CoddyKit er tilrettelagt for både begyndere og øvede, så du kan starte her eller fra begyndelsen og lære i dit eget tempo. Dette er lektion 3 af 4.

Hvor lang tid tager lektionen “MCV- og N-Distinct-korrektioner”?

De fleste CoddyKit-lektioner tager cirka 5–10 minutter. Hver lektion er kort og interaktiv, så du gør løbende fremskridt og kan fortsætte, hvor du slap – på både web og app.

Kan jeg skrive og køre kode i denne Ydelsesoptimering og optimering af forespørgsler i PostgreSQL-lektion?

Ja. Alle Ydelsesoptimering og optimering af forespørgsler i PostgreSQL-lektioner har en indbygget kodeeditor, så du kan skrive og køre rigtig kode direkte i din browser og få øjeblikkelig feedback fra AI – uden lokal opsætning.

Alle lektioner i dette kursus

Sådan estimerer planner’en antal rækker
Multivariate statistikker for korrelerede kolonner
MCV- og N-Distinct-korrektioner
Validering af estimater mod faktiske rækker

← Tilbage til Ydelsesoptimering og optimering af forespørgsler i PostgreSQL