read - ¿Cómo puedo "pensar mejor" cuando leo un plan de consulta PostgreSQL?

postgresql analyze query

Question

Hoy pasé más de una hora confundiéndome con un plan de consulta que no podía entender. La consulta fue una UPDATE y simplemente no se ejecutó en absoluto. Totalmente bloqueado: pg_locks mostró que no estaba esperando nada tampoco. Ahora, no me considero el mejor o el peor lector de planes de consulta, pero este me parece excepcionalmente difícil. Me pregunto ¿cómo se lee esto? ¿Hay alguna metodología que sigan los ases de Pg para identificar el error?

Planeo hacer otra pregunta sobre cómo solucionar este problema, pero ahora mismo estoy hablando específicamente sobre cómo leer este tipo de planes .

                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Nested Loop Anti Join  (cost=47680.88..169413.12 rows=1 width=77)
   Join Filter: ((co.fkey_style = v.chrome_styleid) AND (co.name = o.name))
   ->  Nested Loop  (cost=5301.58..31738.10 rows=1 width=81)
         ->  Hash Join  (cost=5301.58..29722.32 rows=229 width=40)
               Hash Cond: ((io.lot_id = iv.lot_id) AND ((io.vin)::text = (iv.vin)::text))
               ->  Seq Scan on options io  (cost=0.00..20223.32 rows=23004 width=36)
                     Filter: (name IS NULL)
               ->  Hash  (cost=4547.33..4547.33 rows=36150 width=24)
                     ->  Seq Scan on vehicles iv  (cost=0.00..4547.33 rows=36150 width=24)
                           Filter: (date_sold IS NULL)
         ->  Index Scan using options_pkey on options co  (cost=0.00..8.79 rows=1 width=49)
               Index Cond: ((co.fkey_style = iv.chrome_styleid) AND (co.code = io.code))
   ->  Hash Join  (cost=42379.30..137424.09 rows=16729 width=26)
         Hash Cond: ((v.lot_id = o.lot_id) AND ((v.vin)::text = (o.vin)::text))
         ->  Seq Scan on vehicles v  (cost=0.00..4547.33 rows=65233 width=24)
         ->  Hash  (cost=20223.32..20223.32 rows=931332 width=44)
               ->  Seq Scan on options o  (cost=0.00..20223.32 rows=931332 width=44)
(17 rows)

El problema con este plan de consulta, creo que lo comprendo, es probablemente el mejor dicho por RhodiumToad (definitivamente es mejor en esto, así que RhodiumToad que su explicación sea mejor) de irc://irc.freenode.net/#postgresql :

Oh, ese plan es potencialmente desastroso. El problema con ese plan es que está ejecutando un hashjoin enormemente costoso para cada fila. El problema es la estimación de filas = 1 de la otra combinación y el planificador cree que está bien poner una consulta sumamente costosa en el interior. ruta de acceso de un nestloop donde se estima que la ruta externa retorna solo una fila. ya que, obviamente, según la estimación del planificador, la parte costosa solo se ejecutará una vez, pero esto tiene una tendencia obvia a desordenar en la práctica. El problema es que el planificador cree que sus propias estimaciones son ideales, el planificador necesita saber la diferencia entre "estimado para devolver 1 fila "y" no es posible devolver más de 1 fila "pero no está del todo claro cómo incorporar eso en el código existente

Él continúa diciendo:

puede afectar a cualquier combinación, pero generalmente las combinaciones contra subconsultas son las más probables

Ahora, cuando leí este plan, lo primero que noté fue la Nested Loop Anti Join , esto tuvo un costo de 169,413 (me 169,413 a los límites superiores). Este Anti-Join analiza el resultado de un Nested Loop con un costo de 31,738 , y el resultado de un Hash Join con un costo de 137,424 . Ahora, el 137,424 es mucho mayor que 31,738 así que sabía que el problema era el Hash Join.

Luego procedo a EXPLAIN ANALYZE el segmento Hash Join fuera de la consulta. Se ejecutó en 7 segundos. Me aseguré de que hubiera índices en (lot_id, vin) y (co.code y v.code) - había. Deshabilité seq_scan y hashjoin individualmente y noté un aumento de velocidad de menos de 2 segundos. No lo suficiente como para explicar por qué no progresaba después de una hora.

Pero, después de todo esto, estoy totalmente equivocado! Sí, fue la parte más lenta de la consulta, pero debido a que las rows="1" bit (supongo que estaba en la Nested Loop Anti Join ). ¿Aquí hay un error (falta de capacidad) en el planificador que calcula mal la cantidad de filas? ¿Cómo se supone que debo leer esto para llegar a la misma conclusión que hizo RhodiumToad ?

¿Es simplemente rows="1" que se supone que me dispare a resolver esto?

VACUUM FULL ANALYZE en todas las tablas involucradas, y esto es Postgresql 8.4.

Answer 1

¿Analizaste las tablas? ¿Y qué dice pg_stats sobre estas tablas? El queryplan se basa en las estadísticas, estas tienen que estar bien. ¿Y qué versión usas? 8.4?

Los costos se pueden calcular utilizando las estadísticas, la cantidad de relpages, la cantidad de filas y la configuración en postgresql.conf para las Constantes de costos del planificador.

work_mem también está involucrado, podría ser demasiado bajo y forzar al planificador a hacer un segundo, a matar el rendimiento ...

Answer 2

Ver a través de temas como este requiere algo de experiencia sobre dónde las cosas pueden salir mal. Pero para encontrar problemas en los planes de consulta, intente validar el plan producido desde adentro hacia afuera, verifique si el número de filas estimadas es razonable y las estimaciones de costo coinciden con el tiempo empleado. Por cierto las dos estimaciones de costos no son los límites superior e inferior, primero es el costo estimado para producir la primera fila de producción, el segundo número es el costo total estimado, consulte la documentación de detalles para obtener más detalles, también hay documentación disponible para el planificador . También ayuda saber cómo funcionan los diferentes métodos de acceso. Como punto de partida, Wikipedia tiene información sobre bucles anidados , hash y combinaciones de combinación .

En tu ejemplo, comenzarías con:

           ->  Seq Scan on options io  (cost=0.00..20223.32 rows=23004 width=36)
                 Filter: (name IS NULL)

Ejecute EXPLAIN ANALYZE SELECT * FROM options WHERE name IS NULL; y ver si las filas devueltas coinciden con la estimación. Un factor de 2 apagado no suele ser un problema, se trata de detectar diferencias de orden de magnitud.

Luego vea EXPLAIN ANALYZE SELECT * FROM vehicles WHERE date_sold IS NULL; devuelve la cantidad esperada de filas.

Luego sube un nivel para unir hash:

     ->  Hash Join  (cost=5301.58..29722.32 rows=229 width=40)
           Hash Cond: ((io.lot_id = iv.lot_id) AND ((io.vin)::text = (iv.vin)::text))

Ver si EXPLAIN ANALYZE SELECT * FROM vehicles AS iv INNER JOIN options io ON (io.lot_id = iv.lot_id) AND ((io.vin)::text = (iv.vin)::text) WHERE iv.date_sold IS NULL AND io.name IS NULL; resultados en 229 filas.

Un nivel más agrega las INNER JOIN options co ON (co.fkey_style = iv.chrome_styleid) AND (co.code = io.code) y se espera que devuelva solo una fila. Probablemente, el problema es que si el número real de filas va de 1 a 100, la estimación del costo total de atravesar el bucle interno del bucle anidado que contiene está desactivada por un factor de 100.

El error subyacente que el planificador está cometiendo es probablemente que espera que los dos predicados para unirse a co sean independientes entre sí y multipliquen sus selectividades. Mientras que en realidad pueden estar fuertemente correlacionados y la selectividad está más cerca de MIN (s1, s2) y no s1 * s2.