¿Importan siempre los Equi-Joins?

De vez en cuando me topo con afirmaciones sobre cómo funcionan las bases de datos y cómo deberían consultarse. Y me gusta leer esas recomendaciones. Especialmente si vienen con una explicación teórica. Pero aún más me gusta leer esos comentarios cuando vienen con tests que prueban lo que la teoría predijo. El mejor caso siempre es cuando los scripts se publican y se pueden reproducir.

Pero a menudo ocurre que tales afirmaciones sobre rendimiento se hacen de manera general sin prueba alguna: que siempre el patrón x rinde mejor que y. Y esto puede sostenerse si tiene más conocimiento del contenido de sus datos del que la base puede tener. O si hay una explicación matemática para tal comportamiento.

En mi opinión, en muchos casos sobre indexación o estrategias de join, las afirmaciones deberían limitarse a tecnologías y versiones de bases de datos. Además, las afirmaciones sin pruebas reproducibles no deberían darse por sentadas, sino testarse.

Si testeo algo en SQL Server 2008 y lo comparo con SQL Server 2019, normalmente obtendré comportamientos completamente distintos, ya que SQL Server ha evolucionado masivamente. Si comparo Postgres con Snowflake y Exasol, obtengo comportamientos distintos, ya que estos últimos se crearon pensando en consultas analíticas.

Esa es la razón por la que quiero cuestionar la afirmación:

«Los Equi-Joins (inner joins) son siempre mejores que los left joins»

Por qué importa

Si se sigue el consejo de usar siempre cierto patrón sin testarlo, puede llevar a un mal rendimiento. Como estamos en el negocio de la automatización con nuestra herramienta Datavault Builder, elegir el patrón equivocado se multiplica cientos o incluso miles de veces.

Por qué los left joins pueden ser mejores

Lo primero: en ciertas condiciones los inner joins son mejores. Por ejemplo, permiten al optimizador de consultas iniciar las consultas en cualquier extremo de una cadena de joins, y si esto permite limitar masivamente el resultado intermedio, ayudará mucho. Hay muchos otros escenarios.

Pero esto no se cumple siempre:

Imagine 2 escenarios:

• tiene 10 tablas. Una tiene 15 millones de registros y tiene 9 otras tablas con 45 millones de registros. Todos los links van desde su tabla de 15m directamente a las tablas circundantes. Los 15m registros tienen una entrada coincidente en las 9 otras tablas.

• tiene 10 tablas. Una tiene 30 millones de registros y tiene 9 otras tablas con 45 millones de registros. Todos los links van desde su tabla de 30m directamente a las tablas circundantes. 15m registros tienen una entrada coincidente en las 9 otras tablas. 15m no.

Ese es el escenario que ejecuté en Snowflake el 26 de agosto de 2021 (es relevante porque Snowflake mejora constantemente).

Mi suposición era: los left joins rinden mejor para este tipo de consulta. ¿Por qué? Hice los mismos tests para SQL Server (2017) y Oracle (12c) hace 2 años y concluí que los LEFT JOINS rinden mejor en este escenario.

La teoría aquí: al menos en el segundo caso la tabla 30m puede filtrarse antes de unirse, por lo que debería rendir mejor. Para el caso en que todo coincide, esperaría el mismo rendimiento o quizá ligeramente peor (unos pocos por ciento).

Veremos que mis expectativas coinciden con mis mediciones. En el caso de usar un left join también puedo almacenar menos datos, ya que puedo usar un NULL para indicar que no hay registro coincidente.

Pruebas

Desactivé los cachés de resultados. Pero no limpié todos los cachés de datos después de cada consulta. Todos los tests se ejecutaron al menos 3 veces. Los resultados se aceptaban si la desviación entre ejecuciones era inferior al 10%.

Resultados

He hecho 2 tipos de tests: usar 2 columnas para unir entre la tabla 15m/30m y las 9 otras, o solo 1 columna (usando una surrogate key para combinar los 2 primeros campos).

En todos los casos con mi volumen de datos en Snowflake hoy, la versión con left joins rindió aproximadamente igual O mejor.

Para ser justos: el INNER JOIN sobre una columna escanea aproximadamente 20-25% menos datos (porque no necesita filtrar). Pero como es un escaneo local, no duele y acelera la parte del JOIN.

También vemos que el join sobre 1 columna es, como esperaba, mucho más eficiente que el join sobre 2 columnas.

Resumen

En ciertos casos los INNER JOINs tienen mejor rendimiento que los left joins. Pero no en todos. He demostrado con tests reproducibles que en ciertas condiciones los LEFT JOINs no rinden peor, o incluso rinden mucho mejor que los INNER JOINs.

No quiero hacer una afirmación universal sobre mi escenario. Solo digo: en ciertas bases de datos, en ciertas versiones, para ciertas consultas, los LEFT JOINs podrían ser la mejor opción.

También vimos que en Snowflake el join usando 1 columna es mucho mejor que usando 2 columnas.

Pruébelo usted mismo

Como es posible que se me haya escapado algo. ¿Definí mal alguna clave? ¿Limpié los cachés de la forma equivocada o escribí mi SQL de manera no óptima? Por favor, no dude en reproducir mis resultados.

El script de prueba puede descargarse aquí:

AUTOMATED_PIT_TEST