0

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
)
  • the first one you mentioned is an old way of writing an INNER join. I believe all major DBMS vendors translate it to the same execution plan – Biju jose Dec 30 '18 at 13:12
  • TRY the EXPLAIN and see if there is a difference – Biju jose Dec 30 '18 at 13:14
  • @Bijujose no difference.. I thought there might be some use case of using (what seemed to be) CROSS JOIN for some type of CTEs, but I guess not. You can put your comment into an answer and I'll accept, if you want. – dwelle Dec 30 '18 at 13:31
  • 1
    I don't have a Postgres environment to show the EXPLAIN output and show that there is no difference and also not well versed with Postgres. Thanks for your nice gesture. – Biju jose Dec 30 '18 at 13:37
1

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:

0

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
  • Not that I expect difference but you should try this with a bigger table than 3 rows only and with indexes on the columns involved. – ypercubeᵀᴹ Dec 30 '18 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.