¿Cómo calcula Postgres la estimación del recuento de filas durante la planificación de consultas?

Question

Tengo una consulta simple como:

SELECT 
    * 
FROM 
    example 
WHERE 
    filter_1 = ? 
    AND filter_2 = ? 
LIMIT 
    10

La tabla es bastante grande (alrededor de 100 millones de filas) y tiene un índice similar al siguiente (el índice real tiene una columna más en el lado derecho, pero no debería hacer ninguna diferencia):

CREATE INDEX example_idx
ON public.example 
USING btree (filter_1, filter_2, (...));

Así que ahora describamos el problema: cuando ejecuto mi consulta en una declaración preparada, las primeras 5 ejecuciones usan un plan personalizado. Entonces, el plan genérico se considera menos costoso y se utiliza para recordar la vida útil de la declaración preparada.

Aquí hay un EXPLICAR ANALIZAR cuando Postgres usa el plan personalizado:

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

Aquí hay una EXPLICACIÓN cuando Postgres usa el plan genérico:

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))

Aquí, podemos ver claramente que el costo del plan genérico es menor.

Mi problema es cómo se calcula la estimación del recuento de filas en el escaneo de índice y el escaneo secuencial.

La documentación explica cómo y si sigo su cálculo, llego a 3183cuál es el recuento de filas estimado para el plan personalizado:

rare_value_1y frequent_value_2ambos están en la lista MCV. Y su frecuencia es 0.00002667y 0.99783respectivamente. Además, el recuento estimado de filas de la tabla es 119622152.

0.00002667 * 0.99783 * 119622152 = 3183

La pregunta restante es, ¿cómo se hace para el plan genérico?

Descubrí que, por alguna razón desconocida, se ignoran las frecuencias de MCV. Y Postgresql solo mira los n_distinctvalores de las columnas filter_1 y filter_2 (13 y 3 respectivamente):

estimated row count = estimated total number of rows in table / ( n_distinct("filter_1") * n_distinct("filter_2") )
                    = 119622152 / (13 * 3)
                    = 3067235

Mi pregunta es ¿por qué? ¿Por qué Postgresql utiliza una forma tan primitiva de estimar el recuento de filas, ya que tiene acceso a mejores estadísticas en forma de frecuencias MCV?

Versión de Postgresql: 11 (por lo que usar la opción "force_custom_plan" no es posible para nosotros en este momento).

Answer 1

No hay mejor manera de estimar el recuento de filas del plan genérico.

Si elige ir por las frecuencias de un valor más común (MCV), habría varios problemas:

• ¿Qué MCV eliges?

• La estimación sería mejor asumiendo que ambos parámetros son MCV, pero esa es una suposición infundada. Si ambos fueran valores raros, la estimación estaría incluso más lejos de la marca que la estimación actual.

Elegir el promedio es lo mejor que podemos hacer: es algo así como un término medio. Su caso está muy lejos de la marca porque uno de los valores es extremadamente raro, lo que reduce la estimación del plan personalizado, pero como hay pocos valores distintos y uno de ellos es muy frecuente, la selectividad es mala en promedio y el plan genérico la estimación es demasiado alta.

Tienes dos opciones:

• Utilice SQL dinámico para que siempre obtenga un plan personalizado.

• Cambiar la consulta para leer

  WHERE filter_1 || '' = ?
  

  para que no pueda utilizar el índice.