sakila postgres database performance postgresql sql-execution-plan postgresql-performance

postgresql sakila database



Mantenga PostgreSQL a veces eligiendo un plan de consulta incorrecto (4)

Tengo un problema extraño con el rendimiento de PostgreSQL para una consulta, usando PostgreSQL 8.4.9. Esta consulta consiste en seleccionar un conjunto de puntos dentro de un volumen 3D, utilizando un LEFT OUTER JOIN para agregar una columna de ID relacionada donde exista ese ID relacionado. Pequeños cambios en el rango x pueden hacer que PostgreSQL elija un plan de consulta diferente, lo que lleva el tiempo de ejecución de 0.01 segundos a 50 segundos. Esta es la consulta en cuestión:

SELECT treenode.id AS id, treenode.parent_id AS parentid, (treenode.location).x AS x, (treenode.location).y AS y, (treenode.location).z AS z, treenode.confidence AS confidence, treenode.user_id AS user_id, treenode.radius AS radius, ((treenode.location).z - 50) AS z_diff, treenode_class_instance.class_instance_id AS skeleton_id FROM treenode LEFT OUTER JOIN (treenode_class_instance INNER JOIN class_instance ON treenode_class_instance.class_instance_id = class_instance.id AND class_instance.class_id = 7828307) ON (treenode_class_instance.treenode_id = treenode.id AND treenode_class_instance.relation_id = 7828321) WHERE treenode.project_id = 4 AND (treenode.location).x >= 8000 AND (treenode.location).x <= (8000 + 4736) AND (treenode.location).y >= 22244 AND (treenode.location).y <= (22244 + 3248) AND (treenode.location).z >= 0 AND (treenode.location).z <= 100 ORDER BY parentid DESC, id, z_diff LIMIT 400;

Esa consulta toma casi un minuto y, si agrego EXPLAIN al frente de esa consulta, parece estar usando el siguiente plan de consulta:

Limit (cost=56185.16..56185.17 rows=1 width=89) -> Sort (cost=56185.16..56185.17 rows=1 width=89) Sort Key: treenode.parent_id, treenode.id, (((treenode.location).z - 50::double precision)) -> Nested Loop Left Join (cost=6715.16..56185.15 rows=1 width=89) Join Filter: (treenode_class_instance.treenode_id = treenode.id) -> Bitmap Heap Scan on treenode (cost=148.55..184.16 rows=1 width=81) Recheck Cond: (((location).x >= 8000::double precision) AND ((location).x <= 12736::double precision) AND ((location).z >= 0::double precision) AND ((location).z <= 100::double precision)) Filter: (((location).y >= 22244::double precision) AND ((location).y <= 25492::double precision) AND (project_id = 4)) -> BitmapAnd (cost=148.55..148.55 rows=9 width=0) -> Bitmap Index Scan on location_x_index (cost=0.00..67.38 rows=2700 width=0) Index Cond: (((location).x >= 8000::double precision) AND ((location).x <= 12736::double precision)) -> Bitmap Index Scan on location_z_index (cost=0.00..80.91 rows=3253 width=0) Index Cond: (((location).z >= 0::double precision) AND ((location).z <= 100::double precision)) -> Hash Join (cost=6566.61..53361.69 rows=211144 width=16) Hash Cond: (treenode_class_instance.class_instance_id = class_instance.id) -> Seq Scan on treenode_class_instance (cost=0.00..25323.79 rows=969285 width=16) Filter: (relation_id = 7828321) -> Hash (cost=5723.54..5723.54 rows=51366 width=8) -> Seq Scan on class_instance (cost=0.00..5723.54 rows=51366 width=8) Filter: (class_id = 7828307) (20 rows)

Sin embargo, si reemplazo el 8000 en la condición de rango x con 10644 , la consulta se realiza en una fracción de segundo y usa este plan de consulta:

Limit (cost=58378.94..58378.95 rows=2 width=89) -> Sort (cost=58378.94..58378.95 rows=2 width=89) Sort Key: treenode.parent_id, treenode.id, (((treenode.location).z - 50::double precision)) -> Hash Left Join (cost=57263.11..58378.93 rows=2 width=89) Hash Cond: (treenode.id = treenode_class_instance.treenode_id) -> Bitmap Heap Scan on treenode (cost=231.12..313.44 rows=2 width=81) Recheck Cond: (((location).z >= 0::double precision) AND ((location).z <= 100::double precision) AND ((location).x >= 10644::double precision) AND ((location).x <= 15380::double precision)) Filter: (((location).y >= 22244::double precision) AND ((location).y <= 25492::double precision) AND (project_id = 4)) -> BitmapAnd (cost=231.12..231.12 rows=21 width=0) -> Bitmap Index Scan on location_z_index (cost=0.00..80.91 rows=3253 width=0) Index Cond: (((location).z >= 0::double precision) AND ((location).z <= 100::double precision)) -> Bitmap Index Scan on location_x_index (cost=0.00..149.95 rows=6157 width=0) Index Cond: (((location).x >= 10644::double precision) AND ((location).x <= 15380::double precision)) -> Hash (cost=53361.69..53361.69 rows=211144 width=16) -> Hash Join (cost=6566.61..53361.69 rows=211144 width=16) Hash Cond: (treenode_class_instance.class_instance_id = class_instance.id) -> Seq Scan on treenode_class_instance (cost=0.00..25323.79 rows=969285 width=16) Filter: (relation_id = 7828321) -> Hash (cost=5723.54..5723.54 rows=51366 width=8) -> Seq Scan on class_instance (cost=0.00..5723.54 rows=51366 width=8) Filter: (class_id = 7828307) (21 rows)

Estoy lejos de ser un experto en analizar estos planes de consulta, pero la clara diferencia parece ser que con un rango x usa un Hash Left Join para LEFT OUTER JOIN (que es muy rápido), mientras que con el otro rango usa un Nested Loop Left Join (que parece ser muy lento). En ambos casos, las consultas devuelven aproximadamente 90 filas. Si SET ENABLE_NESTLOOP TO FALSE antes de la versión lenta de la consulta, va muy rápido, pero entiendo que usar esa configuración en general es una mala idea .

¿Puedo, por ejemplo, crear un índice particular para hacer más probable que el planificador de consultas elija la estrategia claramente más eficiente? ¿Podría alguien sugerir por qué el planificador de consultas de PostgreSQL debería elegir una estrategia tan deficiente para una de estas consultas? A continuación he incluido detalles del esquema que pueden ser útiles.

La tabla treenode tiene 900,000 filas, y se define de la siguiente manera:

Table "public.treenode" Column | Type | Modifiers ---------------+--------------------------+------------------------------------------------------ id | bigint | not null default nextval(''concept_id_seq''::regclass) user_id | bigint | not null creation_time | timestamp with time zone | not null default now() edition_time | timestamp with time zone | not null default now() project_id | bigint | not null location | double3d | not null parent_id | bigint | radius | double precision | not null default 0 confidence | integer | not null default 5 Indexes: "treenode_pkey" PRIMARY KEY, btree (id) "treenode_id_key" UNIQUE, btree (id) "location_x_index" btree (((location).x)) "location_y_index" btree (((location).y)) "location_z_index" btree (((location).z)) Foreign-key constraints: "treenode_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES treenode(id) Referenced by: TABLE "treenode_class_instance" CONSTRAINT "treenode_class_instance_treenode_id_fkey" FOREIGN KEY (treenode_id) REFERENCES treenode(id) ON DELETE CASCADE TABLE "treenode" CONSTRAINT "treenode_parent_id_fkey" FOREIGN KEY (parent_id) REFERENCES treenode(id) Triggers: on_edit_treenode BEFORE UPDATE ON treenode FOR EACH ROW EXECUTE PROCEDURE on_edit() Inherits: location

El tipo compuesto double3d se define de la siguiente manera:

Composite type "public.double3d" Column | Type --------+------------------ x | double precision y | double precision z | double precision

Las otras dos tablas involucradas en la unión son treenode_class_instance :

Table "public.treenode_class_instance" Column | Type | Modifiers -------------------+--------------------------+------------------------------------------------------ id | bigint | not null default nextval(''concept_id_seq''::regclass) user_id | bigint | not null creation_time | timestamp with time zone | not null default now() edition_time | timestamp with time zone | not null default now() project_id | bigint | not null relation_id | bigint | not null treenode_id | bigint | not null class_instance_id | bigint | not null Indexes: "treenode_class_instance_pkey" PRIMARY KEY, btree (id) "treenode_class_instance_id_key" UNIQUE, btree (id) "idx_class_instance_id" btree (class_instance_id) Foreign-key constraints: "treenode_class_instance_class_instance_id_fkey" FOREIGN KEY (class_instance_id) REFERENCES class_instance(id) ON DELETE CASCADE "treenode_class_instance_relation_id_fkey" FOREIGN KEY (relation_id) REFERENCES relation(id) "treenode_class_instance_treenode_id_fkey" FOREIGN KEY (treenode_id) REFERENCES treenode(id) ON DELETE CASCADE "treenode_class_instance_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id) Triggers: on_edit_treenode_class_instance BEFORE UPDATE ON treenode_class_instance FOR EACH ROW EXECUTE PROCEDURE on_edit() Inherits: relation_instance

... y class_instance :

Table "public.class_instance" Column | Type | Modifiers ---------------+--------------------------+------------------------------------------------------ id | bigint | not null default nextval(''concept_id_seq''::regclass) user_id | bigint | not null creation_time | timestamp with time zone | not null default now() edition_time | timestamp with time zone | not null default now() project_id | bigint | not null class_id | bigint | not null name | character varying(255) | not null Indexes: "class_instance_pkey" PRIMARY KEY, btree (id) "class_instance_id_key" UNIQUE, btree (id) Foreign-key constraints: "class_instance_class_id_fkey" FOREIGN KEY (class_id) REFERENCES class(id) "class_instance_user_id_fkey" FOREIGN KEY (user_id) REFERENCES "user"(id) Referenced by: TABLE "class_instance_class_instance" CONSTRAINT "class_instance_class_instance_class_instance_a_fkey" FOREIGN KEY (class_instance_a) REFERENCES class_instance(id) ON DELETE CASCADE TABLE "class_instance_class_instance" CONSTRAINT "class_instance_class_instance_class_instance_b_fkey" FOREIGN KEY (class_instance_b) REFERENCES class_instance(id) ON DELETE CASCADE TABLE "connector_class_instance" CONSTRAINT "connector_class_instance_class_instance_id_fkey" FOREIGN KEY (class_instance_id) REFERENCES class_instance(id) TABLE "treenode_class_instance" CONSTRAINT "treenode_class_instance_class_instance_id_fkey" FOREIGN KEY (class_instance_id) REFERENCES class_instance(id) ON DELETE CASCADE Triggers: on_edit_class_instance BEFORE UPDATE ON class_instance FOR EACH ROW EXECUTE PROCEDURE on_edit() Inherits: concept

Lo que dijo Erwin sobre las estadísticas. También:

ORDER BY parentid DESC, id, z_diff

Ordenando

parentid DESC, id, z

podría darle al optimizador un poco más de espacio para mezclar. (No creo que importe mucho ya que es el último trimestre, y el tipo no es tan caro, pero podrías probarlo)

Soy escéptico de que esto tenga algo que ver con malas estadísticas a menos que considere la combinación de estadísticas de base de datos y su tipo de datos personalizados.

Mi suposición es que PostgreSQL está escogiendo una unión de bucle anidado porque mira los predicados (treenode.location).x >= 8000 AND (treenode.location).x <= (8000 + 4736) y hace algo funky en la aritmética de tu comparación Normalmente, se utilizará un bucle anidado cuando tenga una pequeña cantidad de datos en el lado interno de la unión.

Pero, una vez que cambias la constante a 10736, obtienes un plan diferente. Siempre es posible que el plan sea suficientemente complejo como para que la Genetic Query Optimization (GEQO) esté funcionando y vea los efectos colaterales de la construcción de un plan no determinista . Hay suficientes discrepancias en el orden de evaluación en las consultas para hacerme pensar que eso es lo que está sucediendo.

Una opción sería examinar el uso de una declaración parametrizada / preparada para esto en lugar de usar un código ad hoc. Dado que está trabajando en un espacio tridimensional, es posible que desee considerar el uso de PostGIS . Si bien puede ser excesivo, también puede proporcionarle el rendimiento que necesita para que estas consultas se ejecuten correctamente.

Si bien forzar el comportamiento del planificador no es la mejor opción, a veces terminamos tomando mejores decisiones que el software.

Si el planificador de consultas toma malas decisiones, es principalmente una de dos cosas:

1. Las estadísticas están apagadas.

Significado "inexacto", no "apagado".

¿Corres ANALYZE suficiente? También popular en su forma combinada VACUUM ANALYZE . Si el autovacuum está activado (que es el predeterminado en Postgres de hoy en día), ANALYZE se ejecuta automáticamente. Pero considera:

(Las dos respuestas más importantes aún se aplican a Postgres 9.6).

Si su tabla es grande y la distribución de datos es irregular , puede ser útil aumentar el valor default_statistics_target . O más bien, simplemente configure el objetivo de estadísticas para las columnas relevantes (aquellas en las cláusulas WHERE o JOIN de sus consultas, básicamente):

ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 1234; -- calibrate number

El objetivo se puede establecer en el rango de 0 a 10000;

Ejecute ANALYZE nuevamente después de eso (en tablas relevantes).

2. La configuración de costo para las estimaciones del planificador está desactivada.

Lea el capítulo Constantes de costos del planificador en el manual.

Mire los capítulos default_statistics_target y random_page_cost en esta página Wiki de PostgreSQL generalmente útil .

Por supuesto, puede haber muchas otras razones posibles, pero estas son las más comunes de lejos.