¿Por qué IN/EXISTS es mucho más lento que NOT IN/NOT EXISTS al comparar columnas indexadas? (PostgreSQL)
Digamos que tengo 2 tablas que comparten una columna (mismo nombre y tipo) que está indexada en ambas.
¿Por qué es que esta comparación es instantánea mientras que usar IN o EXISTS (sin NOT) toma mucho tiempo?
WITH cte1 AS (SELECT DISTINCT mycol FROM table1),
cte2 AS (SELECT DISTINCT mycol FROM table2)
SELECT mycol
FROM cte1 a
WHERE a.mycol NOT IN (SELECT mycol FROM cte1)
<pre><code>-- O "WHERE NOT EXISTS(SELECT 1 FROM cte2 b WHERE b.mycol = a.mycol)"
</code></pre>
(Utilizando PostgreSQL 13.4 y TimescaleDB 2.4.2. Esto es una hipertabla, ¡no estoy seguro si está relacionado!)
EDIT: Al ejecutar EXPLAIN, la única diferencia que veo es que la versión lenta es un “Merge Join” mientras que la versión rápida es un “Merge Anti Join” (y al ejecutarlo con EXPLAIN ANALYZE también se vuelve lento por alguna razón)
davy.ai
La razón por la cual la comparación en la consulta SQL proporcionada es instantánea mientras que el uso de
INoEXISTS(sinNOT) toma mucho tiempo, se debe al hecho de que los operadoresINyEXISTSresultan en un “Nested Loop Join”, el cual puede volverse muy lento cuando el número de filas en ambas tablas es grande.Mientras se ejecuta la consulta con
INoEXISTS, el motor de la base de datos debe realizar un escaneo completo de una tabla por cada fila de la otra tabla. Esto es computacionalmente costoso y puede rápidamente convertirse en un cuello de botella cuando se trabaja con conjuntos de datos grandes.Por otro lado, la consulta SQL proporcionada está utilizando el operador
NOT IN, el cual es equivalente a un “left anti join”. Esta unión se realiza como un “Merge Anti Join” en PostgreSQL, el cual es un operador más eficiente que retorna solo las filas de la primera tabla que no tienen un resultado en la segunda tabla.Además, los índices pueden acelerar significativamente las consultas, especialmente cuando se trabaja con conjuntos de datos grandes. En el ejemplo dado, ambas tablas tienen un índice en la columna compartida, lo cual permite al motor de la base de datos comparar fácilmente las dos tablas, resultando en tiempos de ejecución más rápidos.
Cabe destacar que el hecho de que la base de datos sea una “hypertable” puede tener algún impacto en el rendimiento, pero no debería ser la razón principal de la diferencia de velocidad observada entre las dos consultas.