In che modo Postgres calcola la stima del conteggio delle righe durante la pianificazione delle query

Aug 20 2020

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_1e frequent_value_2sono entrambi nell'elenco MCV. E la loro frequenza è 0.00002667e 0.99783rispettivamente. 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_distinctvalori 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

2 LaurenzAlbe Aug 20 2020 at 19:34

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.