sql - left - Diferencia de rendimiento: condición colocada en la cláusula INNER JOIN vs WHERE

left join sql server ejemplos

Question

Digamos que tengo un order mesa como

id | clientid | type | amount | itemid | date
---|----------|------|--------|--------|-----------
23 | 258 | B | 150 | 14 | 2012-04-03
24 | 258 | S | 69 | 14 | 2012-04-03
25 | 301 | S | 10 | 20 | 2012-04-03
26 | 327 | B | 54 | 156 | 2012-04-04

• clientid es una clave foránea que regresa a la tabla del client
• itemid es una clave externa para volver a una tabla de item
• type es solo B o S
• amount es un número entero

y una mesa processed como

id | orderid | processed | date
---|---------|-----------|---------
41 | 23 | true | 2012-04-03
42 | 24 | true | 2012-04-03
43 | 25 | false | <NULL>
44 | 26 | true | 2012-04-05

Necesito obtener todas las filas del order que para el mismo clientid de clientid en la misma date tienen valores de type opuestos. Tenga en cuenta que el type solo puede tener uno de dos valores: B o S En el ejemplo anterior, esto sería las filas 23 y 24 .

La otra restricción es que la fila correspondiente en processed debe ser true para el orderid .

Mi consulta hasta ahora

SELECT c1.clientid,
c1.date,
c1.type,
c1.itemid,
c1.amount,
c2.date,
c2.type,
c2.itemid,
c2.amount
FROM order c1
INNER JOIN order c2 ON c1.itemid = c2.itemid AND
c1.date = c2.date AND
c1.clientid = c2.clientid AND
c1.type <> c2.type AND
c1.id < c2.id
INNER JOIN processed p1 ON p1.orderid = c1.id AND
p1.processed = true
INNER JOIN processed p2 ON p2.orderid = c2.id AND
p2.processed = true

PREGUNTA: Mantener el valor processed = true como parte de la cláusula de unión está ralentizando la consulta. Si lo muevo a la cláusula WHERE, el rendimiento es mucho mejor. Esto ha despertado mi interés y me gustaría saber por qué .

Las claves primarias y las columnas de clave externa respectivas se indexan, mientras que las columnas de value ( value , processed , etc.) no se indexan.

Descargo de responsabilidad: he heredado esta estructura de base de datos y la diferencia de rendimiento es de aproximadamente 6 segundos.

Answer 1

La razón por la que está viendo una diferencia se debe al plan de ejecución que el planificador está armando, esto obviamente es diferente según la consulta (podría decirse que debería estar optimizando las 2 consultas para que sean iguales y esto puede ser un error). ). Esto significa que el planificador piensa que tiene que trabajar de una manera particular para obtener el resultado en cada declaración.

Cuando lo haga dentro de UNIR, el planificador probablemente tendrá que seleccionar de la tabla, filtrar por la parte "Verdadera" y luego unir los conjuntos de resultados. Me imagino que se trata de una tabla grande y, por lo tanto, de una gran cantidad de datos para revisar y no puede usar los índices de manera tan eficiente.

Sospecho que si lo hace en una cláusula WHERE, el planificador está eligiendo una ruta que sea más eficiente (es decir, ya sea un índice basado en datos o un conjunto de datos prefiltrados).

Probablemente podría hacer que la unión funcione tan rápido (si no más rápido) agregando un índice en las dos columnas (no está seguro si Postgres admite todavía las columnas incluidas y los índices de columnas múltiples).

En resumen, el planificador es el problema: está eligiendo 2 rutas diferentes para llegar a los conjuntos de resultados, y una de ellas no es tan eficiente como la otra. Es imposible para nosotros saber cuáles son las razones sin la información completa de la tabla y la información de EXPLAIN ANALYZE.

Si desea información específica sobre por qué su consulta específica está haciendo esto, deberá proporcionar más información. Sin embargo la razón es que el planificador elige diferentes rutas.

Material de lectura adicional:

http://www.postgresql.org/docs/current/static/explicit-joins.html

Sólo rozado, parece que el planificador de postgres no reordena las combinaciones para optimizarlo. intente cambiar el orden de las uniones en su declaración para ver si luego obtiene el mismo rendimiento ... solo un pensamiento.