Multivariate statistieken voor gecorreleerde kolommen

Neem een tabel addresses waarin city functioneel bepalend is voor state — elke rij met city = 'New York' heeft ook state = 'NY'. De twee predicaten selecteren dezelfde rijen, dus de gecombineerde selectiviteit is gelijk aan alleen sel(city).

De queryplanner vermenigvuldigt ze echter: sel(city) * sel(state), wat een schatting oplevert die 10× of 100× te klein kan zijn. Let in de onderstaande uitvoer van EXPLAIN ANALYZE op het verschil tussen het geschatte en werkelijke aantal rijen op het scanknooppunt.

Met PostgreSQL 10+ kun je de queryplanner over relaties tussen kolommen leren met objecten voor uitgebreide statistieken, die je aanmaakt via CREATE STATISTICS.

Een object voor uitgebreide statistieken benoemt een reeks kolommen (of expressies) in één tabel en één of meer typen statistieken die daarover moeten worden verzameld. Nadat het object is aangemaakt en geanalyseerd, raadpleegt de queryplanner deze multivariate statistieken in plaats van blindweg selectiviteiten per kolom te vermenigvuldigen.

De drie typen zijn:

• ndistinct — aantal verschillende combinaties van de vermelde kolommen.
• dependencies — graden van functionele afhankelijkheid tussen kolommen.
• mcv — lijsten met meest voorkomende waarden voor de kolomgroep.

Het type dependencies legt functionele afhankelijkheden vast: hoe sterk de waarde van de ene kolom de waarde van een andere kolom impliceert. PostgreSQL slaat voor elke richting een graad tussen 0 en 1 op.

Voor onze tabel heeft city → state een graad dicht bij 1.0 (als je de stad kent, ligt de staat volledig vast), terwijl state → city veel lager is (een staat heeft veel steden).

Wanneer de queryplanner WHERE city = ? AND state = ? evalueert en een sterke afhankelijkheid city → state vindt, stopt hij met vermenigvuldigen en houdt hij in essentie de selectiviteit van de bepalende kolom aan.

Na ANALYZE staan de berekende waarden in de catalogusweergave pg_stats_ext (onbewerkte vorm) en in pg_stats_ext_exprs voor statistieken over expressies. De kolom dependencies toont de graad voor elke richting.

Een graad van of dicht bij 1.000000 voor "1 => 2" (kolom 1 impliceert kolom 2) bevestigt een vrijwel perfecte functionele afhankelijkheid — precies de situatie waarin de aanname van onafhankelijkheid je benadeelde.

Het type ndistinct registreert het aantal verschillende combinaties van de vermelde kolommen. Zonder dit type schat de queryplanner het aantal verschillende combinaties als het product van de aantallen verschillende waarden per kolom, wat bij gecorreleerde kolommen sterk te hoog uitvalt.

Dit is vooral belangrijk voor GROUP BY a, b, c (het schatten van het aantal groepen) en voor gegroepeerde aggregaties die een hash-aggregatie voeden. Een verkeerde schatting van het aantal groepen leidt tot te kleine hashtabellen en wegschrijven naar schijf, of tot een verkeerd gekozen sorteergebaseerde aggregatie.

Functionele afhankelijkheden gaan uit van een uniforme relatie voor de hele tabel. Soms is de correlatie echter waardespecifiek — bepaalde combinaties komen extreem vaak voor, terwijl andere nooit voorkomen. Daar komt het mcv (meest voorkomende waarden) goed van pas.

Een MCV-lijst voor een kolomgroep slaat de werkelijke veelvoorkomende combinaties en hun frequenties op. Daardoor kan de planner predicaten zoals WHERE category = 'A' AND status = 'shipped' schatten op basis van de werkelijk waargenomen frequentie van dat paar, in plaats van een afgeleide benadering.

MCV is de krachtigste, maar ook de opslagintensiefste soort. Gebruik deze wanneer afhankelijkheden alleen de schatting niet verbeteren.

Je kunt meerdere soorten opvragen in één statistiekobject. Als je de lijst met soorten helemaal weglaat, bouwt PostgreSQL alle toepasselijke soorten voor die kolommenset.

Een veelgebruikte, praktische aanpak is om ndistinct, dependencies, mcv samen op te geven voor een set kolommen die samen voorkomen in zowel WHERE- als GROUP BY-clausules. Met één ANALYZE wordt alles gevuld.

Let op: mcv en dependencies ondersteunen maximaal een beperkt aantal kolommen. Houd statistiekobjecten bovendien gericht op kolommen die daadwerkelijk samen worden opgevraagd — niet op elk kolommenpaar in de tabel.

PostgreSQL 14+ breidt CREATE STATISTICS uit met ondersteuning voor expressies, niet alleen voor losse kolommen. Als je query's filteren op date_trunc('month', created_at) of lower(email), heeft de planner normaal gesproken geen statistieken voor die berekende waarde en valt deze terug op een algemene schatting.

Een statistiekobject met één expressie geeft de planner statistieken per expressie. Een object met meerdere kolommen waarin expressies en kolommen worden gecombineerd, legt de correlatie vast tussen een berekende waarde en een opgeslagen kolom.

Uitgebreide statistieken worden niet automatisch aangemaakt — je maakt ze bewust aan op basis van vastgestelde onjuiste schattingen. Een betrouwbaar werkproces:

• Zoek een query waarvan EXPLAIN ANALYZE een groot verschil tussen de schatting en de werkelijke waarde laat zien bij een predicaat met meerdere kolommen.
• Maak een statistiekobject aan voor precies die gecorreleerde kolommen.
• Voer ANALYZE uit op de tabel (of wacht tot de analyse door autovacuum wordt uitgevoerd) om het object te vullen.
• Voer EXPLAIN ANALYZE opnieuw uit en controleer of de schatting nu de werkelijkheid volgt.

Statistiekobjecten worden bij elke ANALYZE vernieuwd en blijven na het aanmaken dus automatisch actueel. Verwijder objecten die je niet meer nodig hebt met DROP STATISTICS, zodat je niet blijft betalen voor de analysekosten.

De planner gaat ervan uit dat kolommen onafhankelijk zijn en vermenigvuldigt de selectiviteit per kolom — daardoor schat hij bij gecorreleerde kolommen het aantal rijen te laag in, wat tot slechte plannen leidt.

• CREATE STATISTICS leert de planner relaties tussen meerdere kolommen.
• dependencies corrigeert te lage schattingen bij gelijkheidsfilters door functionele afhankelijkheden (bijvoorbeeld city → state).
• ndistinct corrigeert schattingen van unieke combinaties voor GROUP BY en gegroepeerde aggregaties.
• mcv legt waardespecifieke scheve combinaties vast voor de nauwkeurigste selectiviteit per paar.
• Maak statistieken aan voor expressies (PG14+), vernieuw ze met ANALYZE, controleer ze met EXPLAIN ANALYZE en bekijk de resultaten in pg_stats_ext.

Richt je alleen op kolommen die echt samen worden opgevraagd en controleer daarna of het verschil tussen de schatting en de werkelijke waarde kleiner wordt.