quarta-feira, 18 de setembro de 2013

Nada a temer com Lateral Joins no PostgreSQL 9.3!

A Junção Lateral, ou lateral join, permite subselects mais legíveis e de fácil entendimento, dentro da cláusula FROM dos comandos SELECT. Através deste recurso, um item da cláusula FROM pode referenciar outro que esteja à sua esquerda, isto é, colunas de tabelas já listadas na cláusula FROM podem ser diretamente referenciadas. Lateral joins são compatíveis com CROSS, INNER ou LEFT joins.




Adicionalmente, é possível fazer lateral joins utilizando o resultado de funções executadas na cláusula FROM como tabelas.

Antes de mostrar o funcionamento desta nova funcionalidade da versão 9.3 do postgres, vamos criar algumas tabelas básicas para utilização nos exemplos.

CREATE TABLE r1 (c1 integer, c2 integer);
INSERT INTO r1 VALUES (1,1);
INSERT INTO r1 VALUES (2,2);
INSERT INTO r1 VALUES (null,null);

CREATE TABLE r2 (c1 integer, c2 integer);
INSERT INTO r2 VALUES (1,1);
INSERT INTO r2 VALUES (2,2);
INSERT INTO r2 VALUES (null,null);

* Sintaxe com CROSS JOIN

Com o uso de LATERAL, ao invés de colocar referência ao campo da tabela r1 na cláusula WHERE, a mesma é feita dentro da cláusula FROM consulta, facilitando a legibilidade e apresentando o mesmo resultado final.

Isso ocorre tanto no produto cartesiano, ou cross join, quanto nas outras modalidades de lateral join.

postgres=# SELECT * FROM r1 CROSS JOIN LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL;
 c1 | c2 | c1 | c2
----+----+----+----
  1 |  1 |  1 |  1
  1 |  1 |  2 |  2
  2 |  2 |  1 |  1
  2 |  2 |  2 |  2
(4 registros)


* Sintaxe com INNER JOIN

postgres=# SELECT * FROM r1 INNER JOIN LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL ON r1.c1 = RLATERAL.c1;
 c1 | c2 | c1 | c2
----+----+----+----
  1 |  1 |  1 |  1
  2 |  2 |  2 |  2
(2 registros)

* Exemplo de sintaxe com LEFT JOIN

postgres=# SELECT * FROM r1 LEFT JOIN LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL ON r1.c1 = RLATERAL.c1;
 c1 | c2 | c1 | c2
----+----+----+----
  1 |  1 |  1 |  1
  2 |  2 |  2 |  2
    |    |    |  
(3 registros)

* Sintaxe com JOIN tradicional.




postgres=# SELECT * FROM r1 JOIN LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL ON r1.c1 = RLATERAL.c1;
 c1 | c2 | c1 | c2
----+----+----+----
  1 |  1 |  1 |  1
  2 |  2 |  2 |  2
(2 registros)





postgres=# SELECT * FROM r1, LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL
postgres-# WHERE r1.c1 = RLATERAL.c1;
 c1 | c2 | c1 | c2
----+----+----+----
  1 |  1 |  1 |  1
  2 |  2 |  2 |  2
(2 registros)


* Não funciona com FULL JOIN

postgres=# SELECT * FROM r1 FULL JOIN LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL ON r1.c1 = RLATERAL.c1;
ERRO:  referência inválida para tabela "r1" na cláusula FROM
LINHA 1: ...N LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS N...
                                                              ^
DETALHE:  The combining JOIN type must be INNER or LEFT for a LATERAL reference.

* Também não funciona com RIGHT JOIN

postgres=# SELECT * FROM r1 RIGHT JOIN LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL ON r1.c1 = RLATERAL.c1;
ERRO:  referência inválida para tabela "r1" na cláusula FROM
LINHA 1: ...N LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS N...
                                                              ^
DETALHE:  The combining JOIN type must be INNER or LEFT for a LATERAL reference.

* Lateral Join envolvendo três tabelas

Para fazer este teste, é necessário criar a tabela r3. Observe que a segunda junção lateral referencia os dados com base no alias RLATERAL, utilizado na primeira junção.

CREATE TABLE r3 (c1 integer, c2 integer);
INSERT INTO r3 VALUES (1,1);
INSERT INTO r3 VALUES (2,2);
INSERT INTO r3 VALUES (null,null);

postgres=# SELECT *
postgres-# FROM r1
postgres-# JOIN LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL ON r1.c1 = RLATERAL.c1
postgres-# JOIN LATERAL (SELECT * FROM r3 WHERE r3.c1 <> 10 and RLATERAL.c1 IS NOT NULL) AS RLATERAL2 ON RLATERAL.c1 = RLATERAL2.c1;
 c1 | c2 | c1 | c2 | c1 | c2
----+----+----+----+----+----
  1 |  1 |  1 |  1 |  1 |  1
  2 |  2 |  2 |  2 |  2 |  2
(2 registros)

* Lateral Joins com Funções

Podem ser utilizadas subconsultas com o retorno de funções e lateral join. O exemplo abaixo ilustra esta funcionalidade.

Exemplo 1: produto cartesiano com a cláusula lateral.

postgres=# SELECT * FROM r1, LATERAL (SELECT generate_series(1,3) AS SERIE) AS RLATERAL;
 c1 | c2 | serie
----+----+-------
  1 |  1 |     1
  1 |  1 |     2
  1 |  1 |     3
  2 |  2 |     1
  2 |  2 |     2
  2 |  2 |     3
    |    |     1
    |    |     2
    |    |     3
(9 registros)

Exemplo 2: referenciando tabela da função.

postgres=# SELECT * FROM r1, LATERAL (SELECT generate_series(1,3) AS SERIE WHERE r1.c1 IS NULL) AS RLATERAL;
 c1 | c2 | serie
----+----+-------
    |    |     1
    |    |     2
    |    |     3
(3 registros)
 
* Lateral Joins e Desempenho

Não foi detectada qualquer diferença em termos de desempenho entre o lateral join e o uso de subqueries. O seu uso deve ser feito em virtude da maior clareza que dá à consulta. Os exemplos abaixo corroboram esta afirmativa:

Exemplo 1: Cross Join

postgres=# EXPLAIN SELECT * FROM r1 CROSS JOIN LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL;
                            QUERY PLAN                           
------------------------------------------------------------------
 Nested Loop  (cost=0.00..56731.49 rows=4532641 width=16)
   ->  Seq Scan on r1  (cost=0.00..31.40 rows=2129 width=8)
         Filter: (c1 IS NOT NULL)
   ->  Materialize  (cost=0.00..47.40 rows=2129 width=8)
         ->  Seq Scan on r2  (cost=0.00..36.75 rows=2129 width=8)
               Filter: (c1 <> 10)
(6 registros)

postgres=# EXPLAIN SELECT * FROM r1 CROSS JOIN (SELECT * FROM r2 WHERE r2.c1 <> 10) AS RLATERAL
postgres-# WHERE r1.c1 IS NOT NULL;
                            QUERY PLAN                           
------------------------------------------------------------------
 Nested Loop  (cost=0.00..56731.49 rows=4532641 width=16)
   ->  Seq Scan on r1  (cost=0.00..31.40 rows=2129 width=8)
         Filter: (c1 IS NOT NULL)
   ->  Materialize  (cost=0.00..47.40 rows=2129 width=8)
         ->  Seq Scan on r2  (cost=0.00..36.75 rows=2129 width=8)
               Filter: (c1 <> 10)
(6 registros)


Exemplo 2: Join Tradicional

postgres=# EXPLAIN
postgres-# SELECT * FROM r1 JOIN LATERAL (SELECT * FROM r2 WHERE r2.c1 <> 10 and r1.c1 IS NOT NULL) AS RLATERAL ON r1.c1 = RLATERAL.c1;
                            QUERY PLAN                           
------------------------------------------------------------------
 Merge Join  (cost=303.53..654.12 rows=22663 width=16)
   Merge Cond: (r1.c1 = r2.c1)
   ->  Sort  (cost=149.09..154.41 rows=2129 width=8)
         Sort Key: r1.c1
         ->  Seq Scan on r1  (cost=0.00..31.40 rows=2129 width=8)
               Filter: (c1 IS NOT NULL)
   ->  Sort  (cost=154.44..159.76 rows=2129 width=8)
         Sort Key: r2.c1
         ->  Seq Scan on r2  (cost=0.00..36.75 rows=2129 width=8)
               Filter: (c1 <> 10)
(10 registros)

postgres=# EXPLAIN
postgres-# SELECT * FROM r1 JOIN (SELECT * FROM r2 WHERE r2.c1 <> 10) AS RLATERAL ON r1.c1 = RLATERAL.c1
postgres-# WHERE r1.c1 IS NOT NULL;
                            QUERY PLAN                           
------------------------------------------------------------------
 Merge Join  (cost=303.53..654.12 rows=22663 width=16)
   Merge Cond: (r1.c1 = r2.c1)
   ->  Sort  (cost=149.09..154.41 rows=2129 width=8)
         Sort Key: r1.c1
         ->  Seq Scan on r1  (cost=0.00..31.40 rows=2129 width=8)
               Filter: (c1 IS NOT NULL)
   ->  Sort  (cost=154.44..159.76 rows=2129 width=8)
         Sort Key: r2.c1
         ->  Seq Scan on r2  (cost=0.00..36.75 rows=2129 width=8)
               Filter: (c1 <> 10)
(10 registros)

* Considerações finais

Lateral joins são úteis para gerar códigos mais legíveis, evitando a separação entre as condições de junção da cláusula WHERE e a tabela referenciada na cláusula FROM dos SELECTS. Este recurso parece ser particularmente útil para comandos muito grandes, que se tornam difíceis de manter. No entanto, se os desenvolvedores forem familiarizados com a sintaxe padrão, a mesma pode ser mantida sem problemas, evitando problemas no entendimento do SQL na manutenção das consultas.

Para quem deseja maior desempenho, o comando não trouxe benefícios.

Evite o uso de lateral joins caso necessite de portabilidade com outros SGBDs, optando pelas sintaxes mais tradicionais para a realização de consultas.

Nenhum comentário: