Início / dba / Perguntas / 226018
Accepted
dwelle
Asked: 2018-12-31 05:05:17 +0800 CST

Qual é o benefício de CROSS JOIN vs INNER JOIN em consultas CTE recursivas?

  • 772

Em docs , a junção da parte não recursiva com a recursiva é feita via CROSS JOIN. Existe algum benefício em usá-lo em vez de INNER JOIN? É apenas uma escolha subjetiva?

CREATE TEMPORARY TABLE folders (id INT, parent INT) ON COMMIT DROP;
INSERT INTO folders
    (id, parent)
    VALUES
    (1, null),
    (2, 1),
    (3, 2);

usandoCROSS JOIN

WITH RECURSIVE tree (id, parent) AS (
    SELECT id, parent
    FROM folders
    WHERE id = 3
    UNION ALL
    SELECT p.id, p.parent
    FROM folders p, tree
    WHERE tree.parent = p.id
)

usandoINNER JOIN

WITH RECURSIVE tree (id, parent) AS (
    SELECT id, parent
    FROM folders
    WHERE id = 3
    UNION ALL
    SELECT p.id, p.parent
    FROM folders p
        INNER JOIN tree
        ON tree.parent = p.id
)
postgresql join

2 respostas

  1. Best Answer

    Independente do contexto CTE, os itens separados por vírgulas na FROMlista são equivalentes ao mesmo que CROSS JOINsubstituir as vírgulas. E [INNER] JOINé exatamente o mesmo que CROSS JOINcom uma WHEREcláusula. O manual:

    FROM T1 CROSS JOIN T2é equivalente a FROM T1 INNER JOIN T2 ON TRUE(veja abaixo). Também é equivalente a FROM T1, T2.

    Mas há uma diferença sutil entre vírgula e JOINsintaxe explícita. A vírgula separa mais estritamente. Ver:

    • 3

  2. Como @Bijujose sugeriu, a aparência CROSS JOINrealmente atua como um INNER JOIN(levando em conta a tree.parent = p.idcondição de junção) e, portanto, não há diferença semântica.

    EXPLAIN ANALYZEsaída para cada:

    Limit  (cost=857.87..858.07 rows=10 width=8) (actual time=0.016..0.109 rows=3 loops=1)
  CTE tree
    ->  Recursive Union  (cost=0.00..857.87 rows=12441 width=8) (actual time=0.012..0.104 rows=3 loops=1)
          ->  Seq Scan on folders  (cost=0.00..38.25 rows=11 width=8) (actual time=0.011..0.011 rows=1 loops=1)
                Filter: (id = 3)
                Rows Removed by Filter: 2
          ->  Hash Join  (cost=3.58..57.08 rows=1243 width=8) (actual time=0.025..0.026 rows=1 loops=3)
                Hash Cond: (p.id = tree_1.parent)
                ->  Seq Scan on folders p  (cost=0.00..32.60 rows=2260 width=8) (actual time=0.002..0.003 rows=3 loops=2)
                ->  Hash  (cost=2.20..2.20 rows=110 width=4) (actual time=0.003..0.003 rows=1 loops=3)
                      Buckets: 1024  Batches: 1  Memory Usage: 8kB
                      ->  WorkTable Scan on tree tree_1  (cost=0.00..2.20 rows=110 width=4) (actual time=0.001..0.001 rows=1 loops=3)
  ->  CTE Scan on tree  (cost=0.00..248.82 rows=12441 width=8) (actual time=0.013..0.107 rows=3 loops=1)
Planning time: 0.168 ms
Execution time: 0.177 ms

Limit  (cost=857.87..858.07 rows=10 width=8) (actual time=0.017..0.081 rows=3 loops=1)
  CTE tree
    ->  Recursive Union  (cost=0.00..857.87 rows=12441 width=8) (actual time=0.013..0.074 rows=3 loops=1)
          ->  Seq Scan on folders  (cost=0.00..38.25 rows=11 width=8) (actual time=0.012..0.012 rows=1 loops=1)
                Filter: (id = 3)
                Rows Removed by Filter: 2
          ->  Hash Join  (cost=3.58..57.08 rows=1243 width=8) (actual time=0.015..0.016 rows=1 loops=3)
                Hash Cond: (p.id = tree_1.parent)
                ->  Seq Scan on folders p  (cost=0.00..32.60 rows=2260 width=8) (actual time=0.003..0.003 rows=3 loops=2)
                ->  Hash  (cost=2.20..2.20 rows=110 width=4) (actual time=0.003..0.003 rows=1 loops=3)
                      Buckets: 1024  Batches: 1  Memory Usage: 8kB
                      ->  WorkTable Scan on tree tree_1  (cost=0.00..2.20 rows=110 width=4) (actual time=0.001..0.001 rows=1 loops=3)
  ->  CTE Scan on tree  (cost=0.00..248.82 rows=12441 width=8) (actual time=0.015..0.078 rows=3 loops=1)
Planning time: 0.240 ms
Execution time: 0.130 ms
    • 1

relate perguntas