¿Cuál es la diferencia entre una LATERAL JOIN y una subconsulta en PostgreSQL?

Resuelto jdotjdot asked hace 9 años • 5 respuestas

Desde que PostgreSQL apareció con la capacidad de realizar LATERALuniones, he estado leyendo sobre ello ya que actualmente hago volcados de datos complejos para mi equipo con muchas subconsultas ineficientes que hacen que la consulta general demore cuatro minutos o más.

Entiendo que LATERALlas uniones pueden ayudarme, pero incluso después de leer artículos como este de Heap Analytics, todavía no lo sigo.

¿ Cuál es el caso de uso de una LATERALunión? ¿ Cuál es la diferencia entre una LATERALunión y una subconsulta?

jdotjdot avatar Feb 17 '15 04:02 jdotjdot
Aceptado

¿ Qué es una LATERALunión?

La característica se introdujo con PostgreSQL 9.3. El manual :

Las subconsultas que aparecen en FROMpueden ir precedidas de la palabra clave LATERAL. Esto les permite hacer referencia a columnas proporcionadas por FROMelementos anteriores. (Sin LATERAL, cada subconsulta se evalúa de forma independiente y, por lo tanto, no puede hacer referencia cruzada a ningún otro FROMelemento).

Las funciones de la tabla que aparecen en FROMtambién pueden ir precedidas de la palabra clave LATERAL, pero para las funciones la palabra clave es opcional; Los argumentos de la función pueden contener referencias a columnas proporcionadas por FROMelementos anteriores en cualquier caso.

Allí se proporcionan ejemplos de código básico.

Más bien una subconsulta correlacionada

Una LATERALcombinación se parece más a una subconsulta correlacionada , no a una subconsulta simple, en el sentido de que las expresiones a la derecha de una LATERALcombinación se evalúan una vez para cada fila a la izquierda de ella, al igual que una subconsulta correlacionada , mientras que una subconsulta simple (expresión de tabla) se evalúa una vez . solo. (Sin embargo, el planificador de consultas tiene formas de optimizar el rendimiento para cualquiera de los dos).
Respuesta relacionada con ejemplos de código para ambos uno al lado del otro, resolviendo el mismo problema:

  • Optimice la consulta GROUP BY para recuperar la última fila por usuario

Para devolver más de una columna , una LATERALunión suele ser más sencilla, limpia y rápida.
Además, recuerde que el equivalente de una subconsulta correlacionada es LEFT JOIN LATERAL ... ON true:

  • Llame a una función de retorno de conjunto con un argumento de matriz varias veces

Cosas que una subconsulta no puede hacer

Hay cosas que una uniónLATERAL puede hacer, pero una subconsulta (correlacionada) no puede (fácilmente). Una subconsulta correlacionada solo puede devolver un único valor, no varias columnas ni varias filas, con la excepción de las llamadas a funciones simples (que multiplican las filas de resultados si devuelven varias filas). Pero incluso ciertas funciones de devolución de conjuntos solo están permitidas en la FROMcláusula. Como unnest()con múltiples parámetros en Postgres 9.4 o posterior. El manual:

Esto sólo está permitido en la FROMcláusula;

Entonces esto funciona, pero no se puede reemplazar (fácilmente) con una subconsulta:

CREATE TABLE tbl (a1 int[], a2 int[]);
SELECT * FROM tbl, unnest(a1, a2) u(elem1, elem2);  -- implicit LATERAL

La coma ( ,) en la FROMcláusula es una notación abreviada para CROSS JOIN.
LATERALse asume automáticamente para funciones de tabla.
Sobre el caso especial de UNNEST( array_expression [, ... ] ):

  • ¿Cómo se declara que una función de retorno de conjunto solo se permitirá en la cláusula FROM?

Funciones de retorno de conjuntos en la SELECTlista

También puede utilizar funciones de devolución de conjuntos como unnest()en la SELECTlista directamente. Esto solía exhibir un comportamiento sorprendente con más de una función de este tipo en la misma SELECTlista hasta Postgres 9.6. Pero finalmente ha sido saneado con Postgres 10 y ahora es una alternativa válida (aunque no sea SQL estándar). Ver:

  • ¿Cuál es el comportamiento esperado para múltiples funciones de retorno de conjuntos en la cláusula SELECT?

Basado en el ejemplo anterior:

SELECT *, unnest(a1) AS elem1, unnest(a2) AS elem2
FROM   tbl;

Comparación:

violín para la página 9.6
violín para la página 10

Para tener en cuenta: una (combinación de) funciones de retorno de conjuntos en la SELECTlista que no produce filas elimina la fila. Internamente se traduce como CROSS JOIN LATERAL ROWS FROM ..., ¡no como LEFT JOIN LATERAL ... ON true!

violín para la página 16 que demuestra la diferencia.

Aclarar información errónea

El manual:

Para los tipos de unión INNERy OUTER, se debe especificar una condición de unión, es decir, exactamente una de NATURAL, ON condición_unión o USING( columna_unión [, ...]). Consulte el significado a continuación.
Para CROSS JOIN, ninguna de estas cláusulas puede aparecer.

Entonces estas dos consultas son válidas (aunque no sean particularmente útiles):

SELECT *
FROM   tbl t
LEFT   JOIN LATERAL (SELECT * FROM b WHERE b.t_id = t.t_id) t ON true;

SELECT *
FROM   tbl t, LATERAL (SELECT * FROM b WHERE b.t_id = t.t_id) t;

Si bien este no lo es:

SELECT *
FROM   tbl t
LEFT   JOIN LATERAL (SELECT * FROM b WHERE b.t_id = t.t_id) t;

Es por eso que el ejemplo de código de AndomarCROSS JOIN es correcto ( no requiere una condición de unión) y el de Atila no lo era.

Erwin Brandstetter avatar Feb 17 '2015 08:02 Erwin Brandstetter

La diferencia entre una unión lateraly una no lateralunión radica en si puede mirar la fila de la tabla de la izquierda. Por ejemplo:

select  *
from    table1 t1
cross join lateral
        (
        select  *
        from    t2
        where   t1.col1 = t2.col1 -- Only allowed because of lateral
        ) sub

Esta "mirada hacia afuera" significa que la subconsulta debe evaluarse más de una vez. Al fin y al cabo, t1.col1se pueden asumir muchos valores.

Por el contrario, la subconsulta después de una no lateralunión se puede evaluar una vez:

select  *
from    table1 t1
cross join
        (
        select  *
        from    t2
        where   t2.col1 = 42 -- No reference to outer query
        ) sub

Como se requiere sin lateral, la consulta interna no depende de ninguna manera de la consulta externa. Una lateralconsulta es un ejemplo de correlatedconsulta, debido a su relación con filas fuera de la consulta misma.

Andomar avatar Feb 16 '2015 22:02 Andomar

tabla de base de datos

Contando con la siguiente blogtabla de base de datos almacenando los blogs alojados en nuestra plataforma:

mesa de blogs

Y actualmente tenemos dos blogs alojados:

identificación creado en título URL
1 2013-09-30 El blog de Vlad Mihalcea https://vladmihalcea.com
2 2017-01-22 Hipersistencia https://hipersistencia.io

Obtener nuestro informe sin utilizar SQL LATERAL JOIN

Necesitamos crear un informe que extraiga los siguientes datos de la blogtabla:

  • la identificación del blog
  • la edad del blog, en años
  • la fecha para el próximo aniversario del blog
  • el número de días que quedan hasta el próximo aniversario.

Si está utilizando PostgreSQL, debe ejecutar la siguiente consulta SQL:

SELECT
  b.id as blog_id,
  extract(
    YEAR FROM age(now(), b.created_on)
  ) AS age_in_years,
  date(
    created_on + (
      extract(YEAR FROM age(now(), b.created_on)) + 1
    ) * interval '1 year'
  ) AS next_anniversary,
  date(
    created_on + (
      extract(YEAR FROM age(now(), b.created_on)) + 1
    ) * interval '1 year'
  ) - date(now()) AS days_to_next_anniversary
FROM blog b
ORDER BY blog_id

Como puede ver, age_in_yearsdebe definirse tres veces porque lo necesita al calcular los valores next_anniversaryy days_to_next_anniversary.

Y ahí es exactamente donde LATERAL JOIN puede ayudarnos.

Obtener el informe utilizando SQL LATERAL JOIN

Los siguientes sistemas de bases de datos relacionales admiten la LATERAL JOINsintaxis:

  • Oráculo desde 12c
  • PostgreSQL desde 9.3
  • MySQL desde 8.0.14

SQL Server puede emular el LATERAL JOINuso CROSS APPLYy OUTER APPLY.

LATERAL JOIN nos permite reutilizar el age_in_yearsvalor y simplemente pasarlo más al calcular los valores next_anniversaryy days_to_next_anniversary.

La consulta anterior se puede reescribir para utilizar LATERAL JOIN, de la siguiente manera:

SELECT
  b.id as blog_id,
  age_in_years,
  date(
    created_on + (age_in_years + 1) * interval '1 year'
  ) AS next_anniversary,
  date(
    created_on + (age_in_years + 1) * interval '1 year'
  ) - date(now()) AS days_to_next_anniversary
FROM blog b
CROSS JOIN LATERAL (
  SELECT
    cast(
      extract(YEAR FROM age(now(), b.created_on)) AS int
    ) AS age_in_years
) AS t
ORDER BY blog_id

Y el age_in_yearsvalor se puede calcular y reutilizar para los cálculos next_anniversaryy days_to_next_anniversary:

id_blog edad en años próximo_aniversario días_para_el_próximo_aniversario
1 7 2021-09-30 295
2 3 2021-01-22 44

Mucho mejor, ¿verdad?

El age_in_yearsse calcula para cada registro de la blogtabla. Entonces, funciona como una subconsulta correlacionada, pero los registros de la subconsulta están unidos con la tabla primaria y, por esta razón, podemos referenciar las columnas producidas por la subconsulta.

Vlad Mihalcea avatar Jan 22 '2021 15:01 Vlad Mihalcea

Una cosa que nadie ha señalado es que puede utilizar LATERALconsultas para aplicar una función definida por el usuario en cada fila seleccionada.

Por ejemplo:

CREATE OR REPLACE FUNCTION delete_company(companyId varchar(255))
RETURNS void AS $$
    BEGIN
        DELETE FROM company_settings WHERE "company_id"=company_id;
        DELETE FROM users WHERE "company_id"=companyId;
        DELETE FROM companies WHERE id=companyId;
    END; 
$$ LANGUAGE plpgsql;

SELECT * FROM (
    SELECT id, name, created_at FROM companies WHERE created_at < '2018-01-01'
) c, LATERAL delete_company(c.id);

Esa es la única manera que conozco de hacer este tipo de cosas en PostgreSQL.

Theodore R. Smith avatar Oct 05 '2018 18:10 Theodore R. Smith