What's the benefit of CROSS JOIN vs INNER JOIN in recursive CTE queries?

Question

In docs, the join of non-recursive part to recursive is done via CROSS JOIN. Is there some benefit to using it instead of INNER JOIN? Is it just a subjective choice?

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

using CROSS 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
)

using INNER 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
)

score 3 · Accepted Answer · answered Dec 30 '18 at 16:13

Independent of the CTE context, comma-separated items in the FROM list are equivalent to the same with CROSS JOIN replacing the commas. And [INNER] JOIN is exactly the same as CROSS JOIN with a WHERE clause. The manual:

FROM T1 CROSS JOIN T2 is equivalent to FROM T1 INNER JOIN T2 ON TRUE (see below). It is also equivalent to FROM T1, T2.

But there is a subtle difference between comma and explicit JOIN syntax. Comma separates more strictly. See:

What does [FROM x, y] mean in Postgres?

score 1 · Answer 2 · answered Dec 30 '18 at 13:47

As @Bijujose suggested, the seeming CROSS JOIN actually acts as an INNER JOIN (taking into account the tree.parent = p.id join condition), and thus there's no semantical difference.

EXPLAIN ANALYZE output for each:

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

What's the benefit of CROSS JOIN vs INNER JOIN in recursive CTE queries?

2 Answers2