Risposta alla domanda
PostgreSQL mantiene istogrammi per colonna in pg_statistic e assume indipendenza statistica tra le colonne quando stima la selettività per filtri multi-colonna. Quando filtri su colonne altamente correlate—come city e state_code o make e model in un database di veicoli—il pianificatore moltiplica le selettività individuali, sottostimando drasticamente il conteggio delle righe effettive. Questo errore di cardinalità spesso causa all'ottimizzatore di preferire join Nested Loop rispetto a join Hash o Merge, risultando in prestazioni catastrofiche su grandi tabelle.
Per risolvere questo, puoi creare un oggetto di statistiche estese usando CREATE STATISTICS, che costruisce dati di correlazione multivariata. In particolare, il tipo dependencies tiene traccia delle dipendenze funzionali tra le colonne, consentendo al pianificatore di riconoscere che il filtraggio su state_code = 'CA' limita già city ai valori della California, evitando l'errore di moltiplicazione.
-- Crea statistiche estese per colonne correlate CREATE STATISTICS stats_vehicle_make_model ON make, model FROM vehicles; -- Popola le statistiche ANALYZE vehicles;
Situazione dalla vita reale
Una piattaforma di logistica aveva problemi con un cruscotto di tracciamento delle spedizioni che univa una tabella shipments da 50 milioni di righe con customers. La query filtrava per origin_state e origin_city, dove il 95% delle righe per 'Springfield' come città erano effettivamente in 'IL', ma il pianificatore assumeva che solo il 2% delle spedizioni corrispondesse a entrambi i predicati in modo indipendente. Stimava 500 righe e sceglieva un join Nested Loop, iterando tra milioni di record di clienti e andando in timeout dopo 90 secondi.
Una soluzione considerata era disabilitare completamente i join Nested Loop tramite SET enable_nestloop = off nella sessione. Questo forzava un Hash Join e completava in 3 secondi per questa query specifica, ma comportava gravi rischi: le modifiche di configurazione globali si propagano tra i pool di connessione, potenzialmente facendo regredire altri piani legittimi di join Nested Loop che funzionano bene su piccole tabelle con ricerche indicizzate. Inoltre, questa soluzione richiedeva modifiche al codice a livello di applicazione per impostare il parametro prima di interrogare.
Un'altra opzione prevedeva la creazione di un indice composite su (origin_city, origin_state). Sebbene questo migliorasse la selezione dell'indice, non risolveva la sottostima della cardinalità; il pianificatore pensava ancora che poche righe sarebbero emerse dalla scansione dell'indice e manteneva la strategia Nested Loop, eseguendola semplicemente più velocemente attraverso l'indice coprente. Inoltre, l'ampio indice composito consumava 4 GB di spazio su disco aggiuntivo e rallentava le operazioni di scrittura sulla tabella shipments ad alta velocità.
Il team ha infine implementato statistiche estese eseguendo CREATE STATISTICS stats_origin_correlation ON origin_city, origin_state FROM shipments, seguito da ANALYZE. Questo approccio non richiedeva riscritture delle query e aggiungeva un sovraccarico di memorizzazione trascurabile. Dopo l'implementazione, il pianificatore stimava correttamente 45.000 righe e sceglieva un Hash Join, riducendo la latenza della query a 400 millisecondi mantenendo piani ottimali per carichi di lavoro non correlati.
Cosa spesso manca ai candidati
Come il comando ANALYZE aggiorna le statistiche estese e perché un oggetto statistico potrebbe apparire inutilizzato subito dopo la creazione?
ANALYZE calcola le statistiche estese solo quando invocato esplicitamente sulla tabella target o quando il processo di autovacuum analizza la tabella dopo che l'oggetto statistico esiste. Molti candidati assumono che CREATE STATISTICS influisca istantaneamente sulla pianificazione, ma le tabelle di catalogo pg_statistic_ext e pg_statistic_ext_data rimangono vuote fino al successivo ciclo di analisi. Di conseguenza, il pianificatore continua a utilizzare istogrammi a colonna singola e assunzioni di indipendenza fino a quando ANALYZE shipments; popola i dati multivariati. Puoi verificare l'uso controllando la vista pg_stats_ext per valori dependencies o ndistinct non nulli.
Qual è la differenza funzionale tra dependencies e ndistinct in CREATE STATISTICS, e quali pattern di query beneficiano di ciascuno?
Dependencies catturano le relazioni funzionali dove una colonna determina un'altra (ad es., zip_code determina city), correggendo direttamente le stime di selettività della clausola WHERE. Ndistinct calcola il numero esatto di combinazioni distinte per gruppi di colonne, migliorando le stime di GROUP BY e DISTINCT piuttosto che la selettività del filtro. I candidati confondono spesso questi due aspetti, creando dependencies quando la loro query lenta contiene un GROUP BY su colonne correlate, o viceversa. Per risultati ottimali, specifica entrambi i tipi: CREATE STATISTICS stats_complex WITH (dependencies, ndistinct) ON (...).
Perché le statistiche estese potrebbero non aiutare con le query che usano condizioni OR su colonne correlate?
Le statistiche estese attualmente assistono solamente con clausole AND dove si verifica la moltiplicazione della selettività. Quando filtri con OR (ad es., city = 'Springfield' OR state = 'IL'), PostgreSQL calcola la selettività usando la formula P(A) + P(B) - P(A ∩ B), e non può applicare coefficienti di dipendenza al termine di intersezione perché le statistiche tengono traccia della selettività congiunta per congiunzioni, non disgiunzioni. I candidati spesso non notano questa limitazione e tentano di utilizzare CREATE STATISTICS per correggere errori di cardinalità basati su OR, il che richiede riscritture di query (ad es., suddividendo in rami UNION ALL) o indici parziali invece.