In che modo Postgres calcola la stima del conteggio delle righe durante la pianificazione delle query
Ho una semplice domanda come:
SELECT
*
FROM
example
WHERE
filter_1 = ?
AND filter_2 = ?
LIMIT
10
La tabella è abbastanza grande (circa 100 milioni di righe) e ha un indice simile al seguente (l'indice effettivo ha un'altra colonna sul lato destro ma non dovrebbe fare alcuna differenza):
CREATE INDEX example_idx
ON public.example
USING btree (filter_1, filter_2, (...));
Quindi ora descriviamo il problema: quando eseguo la mia query in un'istruzione preparata, le prime 5 esecuzioni utilizzano un piano personalizzato. Quindi il piano generico è visto come meno costoso e viene utilizzato per ricordare la durata della dichiarazione preparata.
Ecco un'ANALISI SPIEGAZIONE quando Postgres utilizza il piano personalizzato:
Limit (cost=0.57..39.35 rows=10 width=78) (actual time=0.024..0.034 rows=8 loops=1)
-> Index Scan using example_idx on example c0 (cost=0.57..12345.29 rows=3183 width=78) (actual time=0.024..0.032 rows=8 loops=1)
Index Cond: (((filter_1)::text = 'rare_value_1'::text) AND (filter_2 = 'frequent_value_2'::custom_enum))
Planning Time: 0.098 ms
Execution Time: 0.045 ms
Ecco una SPIEGAZIONE quando Postgres utilizza il piano generico:
Limit (cost=0.00..11.31 rows=10 width=78)
-> Seq Scan on example_idx c0 (cost=0.00..3469262.28 rows=3067235 width=78)
Filter: (((filter_1)::text = $1) AND (filter_2 = $2))
Qui possiamo vedere chiaramente che il costo del piano generico è inferiore.
Il mio problema è come vengono calcolate la stima del conteggio delle righe in Index Scan e Seq Scan.
La documentazione spiega come e se seguo il loro calcolo, arrivo a 3183
, che è il conteggio delle righe stimato per il piano personalizzato:
rare_value_1
e frequent_value_2
sono entrambi nell'elenco MCV. E la loro frequenza è 0.00002667
e 0.99783
rispettivamente. Inoltre, il conteggio delle righe della tabella stimato è 119622152
.
0.00002667 * 0.99783 * 119622152 = 3183
La domanda rimanente è: come si fa per il piano generico?
Ho scoperto che, per qualche motivo sconosciuto, le frequenze MCV vengono ignorate. E Postgresql guarda solo i n_distinct
valori per le colonne filter_1 e filter_2 (13 e 3 rispettivamente):
estimated row count = estimated total number of rows in table / ( n_distinct("filter_1") * n_distinct("filter_2") )
= 119622152 / (13 * 3)
= 3067235
La mia domanda è perché? Perché Postgresql utilizza un modo così primitivo per stimare il conteggio delle righe poiché ha accesso a statistiche migliori sotto forma di frequenze MCV?
Versione Postgresql: 11 (quindi non è possibile utilizzare l'opzione "force_custom_plan" al momento).
Risposte
Non esiste un modo migliore per stimare il conteggio delle righe per il piano generico.
Se scegliessi di andare dalle frequenze di un valore più comune (MCV), ci sarebbero diversi problemi:
Quale MCV scegli?
La stima sarebbe migliore ipotizzando che entrambi i parametri siano MCV, ma questa è un'ipotesi infondata. Se entrambi fossero valori rari, la stima sarebbe ancora più lontana dalla stima attuale.
Scegliere la media è il meglio che possiamo fare: è una sorta di via di mezzo. Il tuo caso è così lontano dal segno perché uno dei valori è estremamente raro, il che abbassa la stima del piano personalizzato, ma poiché ci sono pochi valori distinti e uno di questi è molto frequente, la selettività è mediamente pessima e il piano generico la stima è troppo alta.
Hai due scelte:
Usa SQL dinamico in modo da ottenere sempre un piano personalizzato.
Modifica la query in lettura
WHERE filter_1 || '' = ?
in modo che non possa utilizzare l'indice.