www net postgresql

postgresql - www - grafana net



¿Qué explica PostgreSQL al decirme exactamente? (7)

La parte que siempre encuentro confusa es el costo inicial comparado con el costo total. Lo busco en Google cada vez que lo olvido, lo que me lleva de vuelta a este punto, lo que no explica la diferencia, y es por eso que estoy escribiendo esta respuesta. Esto es lo que obtuve de la documentación de Postgres EXPLAIN , explicada tal como lo entiendo.

Aquí hay un ejemplo de una aplicación que administra un foro:

EXPLAIN SELECT * FROM post LIMIT 50; Limit (cost=0.00..3.39 rows=50 width=422) -> Seq Scan on post (cost=0.00..15629.12 rows=230412 width=422)

Aquí está la explicación gráfica de PgAdmin:

(Cuando usa PgAdmin, puede apuntar con el mouse a un componente para leer los detalles del costo).

El costo se representa como una tupla, por ejemplo, el costo del LIMIT es el cost=0.00..3.39 y el costo del escaneo secuencial de la post es el cost=0.00..15629.12 . El primer número en la tupla es el costo inicial y el segundo número es el costo total . Como utilicé EXPLAIN y no EXPLAIN ANALYZE , estos costos son estimaciones, no medidas reales.

  • El costo de inicio es un concepto complicado. No solo representa la cantidad de tiempo antes de que comience ese componente. Representa la cantidad de tiempo entre el momento en que el componente comienza a ejecutarse (lectura en datos) y cuando el componente genera su primera fila .
  • El costo total es el tiempo total de ejecución del componente, desde que comienza a leer en datos hasta cuando termina de escribir su salida.

Como complicación, los costos de cada nodo "padre" incluyen el costo de sus nodos secundarios. En la representación de texto, el árbol se representa con sangría, por ejemplo, LIMIT es un nodo padre y Seq Scan es su hijo. En la representación de PgAdmin, las flechas apuntan de niño a padre, la dirección del flujo de datos, lo que podría ser contradictorio si está familiarizado con la teoría de grafos.

La documentación dice que los costos incluyen todos los nodos secundarios, pero observe que el costo total del padre 3.39 es mucho más pequeño que el costo total de su hijo 15629.12 . El costo total no es inclusivo porque un componente como LIMIT no necesita procesar toda su entrada. Vea EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2; ejemplo en la documentación de Postgres EXPLAIN .

En el ejemplo anterior, el tiempo de inicio es cero para ambos componentes, porque ninguno de los componentes necesita hacer ningún procesamiento antes de comenzar a escribir filas: un escaneo secuencial lee la primera fila de la tabla y la emite. El LIMIT lee su primera fila y luego la emite.

¿Cuándo necesitaría un componente realizar un gran procesamiento antes de que pueda comenzar a generar filas? Hay muchas razones posibles, pero veamos un ejemplo claro. Aquí está la misma consulta de antes, pero ahora contiene una cláusula ORDER BY :

EXPLAIN SELECT * FROM post ORDER BY body LIMIT 50; Limit (cost=23283.24..23283.37 rows=50 width=422) -> Sort (cost=23283.24..23859.27 rows=230412 width=422) Sort Key: body -> Seq Scan on post (cost=0.00..15629.12 rows=230412 width=422)

Y gráficamente:

Una vez más, el escaneo secuencial en la post no tiene costo de inicio: comienza a generar filas inmediatamente. Pero el tipo tiene un costo de inicio significativo 23283.24 porque tiene que ordenar toda la tabla antes de que pueda generar incluso una sola fila . El costo total del tipo 23859.27 es solo un poco más alto que el costo de inicio, lo que refleja el hecho de que una vez que se ha ordenado todo el conjunto de datos, los datos ordenados se pueden emitir muy rápidamente.

Tenga en cuenta que el tiempo de inicio del LIMIT 23283.24 es exactamente igual al tiempo de inicio del ordenamiento. Esto no es porque LIMIT sí tiene un alto tiempo de inicio. En realidad, tiene cero tiempo de inicio por sí mismo, pero EXPLAIN acumula todos los costos secundarios para cada padre, por lo que el tiempo de inicio de LIMIT incluye la suma de los tiempos de inicio de sus hijos.

Este paquete de costos puede dificultar la comprensión del costo de ejecución de cada componente individual. Por ejemplo, nuestro LIMIT tiene cero tiempo de inicio, pero eso no es obvio a primera vista. Por este motivo, hay otras personas vinculadas a explain.depesz.com , una herramienta creada por Hubert Lubaczewski (también conocida como depesz) que ayuda a entender EXPLAIN , entre otras cosas, restando los costos de los hijos de los costos de los padres. Él menciona algunas otras complejidades en una breve publicación de blog sobre su herramienta.

La explicación de MySQL es bastante simple. PostgreSQL es un poco más complicado. No he podido encontrar un buen recurso que lo explique tampoco.

¿Puedes describir qué es exactamente lo que se dice al decir o al menos dirigirme hacia un buen recurso?

Se ejecuta desde la mayoría de las sangrías hasta las menos sangradas, y creo desde el final del plan hasta la parte superior. (Entonces, si hay dos secciones sangradas, la que se encuentra más abajo en la página se ejecuta primero, luego cuando se encuentran con la otra, se ejecuta la regla que las une).

La idea es que en cada paso haya 1 o 2 conjuntos de datos que lleguen y sean procesados ​​por alguna regla. Si solo un conjunto de datos, esa operación se realiza para ese conjunto de datos. (Por ejemplo, escanee un índice para descubrir qué filas quiere, filtre un conjunto de datos u ordene). Si son dos, los dos conjuntos de datos son las dos cosas que se sangran más y se unen a la regla que ve. El significado de la mayoría de las reglas puede ser razonablemente fácil de adivinar (especialmente si ha leído un montón de explicaciones antes), sin embargo, puede tratar de verificar elementos individuales ya sea mirando la documentación o (más fácil) simplemente tirando la frase a Google junto con algunas palabras clave como EXPLAIN .

Evidentemente, esto no es una explicación completa, pero proporciona un contexto suficiente como para poder descubrir lo que quiera. Por ejemplo, considere este plan desde una base de datos real:

explain analyze select a.attributeid, a.attributevalue, b.productid from orderitemattribute a, orderitem b where a.orderid = b.orderid and a.attributeid = ''display-album'' and b.productid = ''ModernBook''; ------------------------------------------------------------------------------------------------------------------------------------------------------------ Merge Join (cost=125379.14..125775.12 rows=3311 width=29) (actual time=841.478..841.478 rows=0 loops=1) Merge Cond: (a.orderid = b.orderid) -> Sort (cost=109737.32..109881.89 rows=57828 width=23) (actual time=736.163..774.475 rows=16815 loops=1) Sort Key: a.orderid Sort Method: quicksort Memory: 1695kB -> Bitmap Heap Scan on orderitemattribute a (cost=1286.88..105163.27 rows=57828 width=23) (actual time=41.536..612.731 rows=16815 loops=1) Recheck Cond: ((attributeid)::text = ''display-album''::text) -> Bitmap Index Scan on (cost=0.00..1272.43 rows=57828 width=0) (actual time=25.033..25.033 rows=16815 loops=1) Index Cond: ((attributeid)::text = ''display-album''::text) -> Sort (cost=15641.81..15678.73 rows=14769 width=14) (actual time=14.471..16.898 rows=1109 loops=1) Sort Key: b.orderid Sort Method: quicksort Memory: 76kB -> Bitmap Heap Scan on orderitem b (cost=310.96..14619.03 rows=14769 width=14) (actual time=1.865..8.480 rows=1114 loops=1) Recheck Cond: ((productid)::text = ''ModernBook''::text) -> Bitmap Index Scan on id_orderitem_productid (cost=0.00..307.27 rows=14769 width=0) (actual time=1.431..1.431 rows=1114 loops=1) Index Cond: ((productid)::text = ''ModernBook''::text) Total runtime: 842.134 ms (17 rows)

Intenta leerlo por ti mismo y ver si tiene sentido.

Lo que leo es que la base de datos primero escanea el índice id_orderitem_productid , usando eso para encontrar las filas que quiere de orderitem , luego ordena ese conjunto de datos usando un quicksort (el tipo utilizado cambiará si los datos no encajan en la RAM), luego establece que aparte.

A continuación, escanea orditematt_attributeid_idx para encontrar las filas que quiere de orderitemattribute y luego ordena ese conjunto de datos utilizando un quicksort.

Luego toma los dos conjuntos de datos y los fusiona. (Una combinación de combinación es una especie de operación de "compresión" en la que recorre los dos conjuntos de datos ordenados en paralelo, emitiendo la fila unida cuando coinciden).

Como dije, trabajas a través de la parte interna del plan hacia la parte exterior, de abajo hacia arriba.

Si instala pgadmin, hay un botón de Explicación que además de proporcionar la salida de texto dibuja diagramas de lo que está sucediendo, mostrando los filtros, ordena y subconjunta las fusiones que me parecen realmente útiles para ver qué está sucediendo.

También hay disponible una herramienta de ayuda en línea, Depesz , que resaltará dónde están las partes costosas de los resultados del análisis.

también tiene uno, aquí están los mismos resultados , que para mí lo aclaran dónde está el problema.

PgAdmin le mostrará una representación gráfica del plan de explicación. Alternar entre los dos puede realmente ayudarlo a comprender lo que significa la representación de texto. Sin embargo, si solo quiere saber lo que está haciendo todo, puede usar siempre la GUI.