Antwoord op de vraag
PostgreSQL onderhoudt histogrammen per kolom in pg_statistic en gaat ervan uit dat kolommen statistische onafhankelijkheid vertonen bij het schatten van selectiviteit voor multi-kolomfilters. Wanneer je filtert op sterk gecorreleerde kolommen - zoals city en state_code of make en model in een voertuigendatabase - vermenigvuldigt de planner individuele selectiviteiten, waarbij de werkelijke rijtelling drastisch wordt onderschat. Deze cardinaliteitsfout zorgt er vaak voor dat de optimizer Nested Loop joins boven Hash of Merge joins verkiest, wat resulteert in catastrofale prestaties op grote tabellen.
Om dit op te lossen, creëer je een uitgebreid statistisch object met CREATE STATISTICS, dat multivariate correlatiegegevens opbouwt. Specifically, het type dependencies volgt functionele afhankelijkheden tussen kolommen, zodat de planner kan herkennen dat filteren op state_code = 'CA' al city op Californische waarden constrains, waardoor de vermenigvuldigingsfout wordt vermeden.
-- Maak uitgebreide statistieken voor gecorreleerde kolommen CREATE STATISTICS stats_vehicle_make_model ON make, model FROM vehicles; -- Vul de statistieken ANALYZE vehicles;
Situatie uit het leven
Een logistiek platform had problemen met een verzendtracking-dashboard dat een shipments-tabel van 50 miljoen rijen koppelde aan customers. De query filterde op origin_state en origin_city, waar 95% van de rijen voor 'Springfield' als stad daadwerkelijk in 'IL' waren, maar de planner ging ervan uit dat slechts 2% van de zendingen aan beide voorwaarden onafhankelijk voldeed. Het schatte 500 rijen en koos voor een Nested Loop join, waarbij miljoenen klantrecords werden doorlopen en na 90 seconden afkapte.
Een overwogen oplossing was het volledig uitschakelen van Nested Loop joins via SET enable_nestloop = off in de sessie. Dit dwong een Hash Join af en voltooide in 3 seconden voor deze specifieke query, maar droeg ernstige risico's in zich: globale configuratiewijzigingen verspreiden zich over verbindingspools, wat andere legitieme Nested Loop plannen die goed presteren op kleine tabellen met geïndexeerde opzoekingen, mogelijk zou terugdraaien. Bovendien vereiste deze workaround wijzigingen in de applicatielaag om de parameter vóór het queryen in te stellen.
Een andere optie omvatte het creëren van een samengestelde index op (origin_city, origin_state). Hoewel dit de indexselectie verbeterde, loste het de cardinaliteitsonderschatting niet op; de planner dacht nog steeds dat er maar weinig rijen uit de indexscan zouden komen en behield de Nested Loop strategie, simpelweg sneller uitgevoerd via de dekkende index. Bovendien verbruikte de brede samengestelde index 4 GB aan extra schijfruimte en vertraagde schrijfoperaties op de hoge snelheid shipments-tabel.
Het team implementeerde uiteindelijk uitgebreide statistieken door CREATE STATISTICS stats_origin_correlation ON origin_city, origin_state FROM shipments, gevolgd door ANALYZE. Deze aanpak vereiste geen herschrijving van queries en voegde verwaarloosbare opslag overhead toe. Na de implementatie schatte de planner correct 45.000 rijen en koos voor een Hash Join, waardoor de query-latentie tot 400 milliseconden werd verlaagd terwijl optimale plannen voor niet-gelieerde workloads behouden bleven.
Wat kandidaten vaak missen
Hoe verfrist het ANALYZE commando uitgebreide statistieken, en waarom kan een statistisch object ongebruikt lijken direct na creatie?
ANALYZE berekent uitgebreide statistieken alleen wanneer het expliciet op de doeltabel wordt aangeroepen of wanneer autovacuum de tabel verwerkt nadat het statistische object bestaat. Veel kandidaten aannemen dat CREATE STATISTICS onmiddellijk invloed heeft op de planning, maar de catalogustabellen pg_statistic_ext en pg_statistic_ext_data blijven leeg totdat de volgende analysecylus. Dienovereenkomstig blijft de planner de histogrammen per kolom en onafhankelijkheidsveronderstellingen gebruiken totdat ANALYZE shipments; de multivariate gegevens bevult. Je kunt gebruik verifiëren door de pg_stats_ext weergave te controleren op niet-nul dependencies of ndistinct waarden.
Wat is het functionele verschil tussen dependencies en ndistinct in CREATE STATISTICS, en welke querypatronen profiteren van elk?
Dependencies leggen functionele relaties vast waarbij de ene kolom de andere bepaalt (bijvoorbeeld zip_code bepaalt city), wat selectiviteitsschattingen van de WHERE clausule rechtstreeks corrigeert. Ndistinct berekent het exacte aantal unieke combinaties voor kolomgroepen, wat de schattingen van GROUP BY en DISTINCT verbetert in plaats van filterselectiviteit. Kandidaten verwarren deze vaak, en creëren dependencies wanneer hun langzame query een GROUP BY op gecorreleerde kolommen bevat, of omgekeerd. Voor optimale resultaten, specificeer beide types: CREATE STATISTICS stats_complex WITH (dependencies, ndistinct) ON (...).
Waarom kunnen uitgebreide statistieken niet helpen met queries die OR voorwaarden gebruiken over gecorreleerde kolommen?
Uitgebreide statistieken helpen momenteel alleen met AND clausules waar selectiviteitsvermenigvuldiging plaatsvindt. Wanneer je filtert met OR (bijvoorbeeld city = 'Springfield' OR state = 'IL'), berekent PostgreSQL de selectiviteit met de formule P(A) + P(B) - P(A ∩ B), en kan geen afhankelijkheidscoëfficiënten toepassen op de intersectieterm omdat de statistieken de gezamenlijke selectiviteit voor conjuncties volgen, niet voor disjuncties. Kandidaten missen vaak deze beperking en proberen CREATE STATISTICS te gebruiken om OR-gebaseerde cardinaliteitsfouten te corrigeren, wat herschrijven van de query (bijvoorbeeld splitsen in UNION ALL takken) of gedeeltelijke indexen vereist.