1

Let's take as an example a dummy query that looks like this

SELECT a.*
FROM a
JOIN b on a.id = b.id

UNION

SELECT a.*
FROM a
JOIN c on a.id = c.id

The documentation states:

The planner will rewrite explicit JOIN constructs (except FULL JOINs) into lists of FROM items whenever a list of no more than this many items would result.

This leads me to believe that join_collapse_limit will be evaluated for each sub-query in the query above, so it won't count as 2 joins for the entire query but as 1 join for each sub-query. However, it's unclear to me if that is actually the case and I can't find anything related in the docs.

So, will it really be evaluated for each sub-query? If the answer to that is yes, then is there any exceptional case where it wouldn't like that?

Edit: The explain output is the following

 Unique
   ->  Sort
         Sort Key: a.id
         ->  Append
               ->  Nested Loop
                     Join Filter: (a.id = b.id)
                     ->  Seq Scan on a
                     ->  Seq Scan on b
               ->  Nested Loop
                     Join Filter: (a_1.id = c.id)
                     ->  Seq Scan on a a_1
                     ->  Seq Scan on c

4
  • What is your end goal here? If you are aiming for performance, then start by running EXPLAIN followed by your current query. Commented Jul 7 at 10:28
  • I wasn't aiming for performance when asking this question but it has occurred to me in the past how performance would be affected by something like this. I'm mostly interested in understanding how things work behind the scenes.
    – george_1111
    Commented Jul 7 at 10:33
  • You should still check the explain plan to see what Postgres is doing. Commented Jul 7 at 10:39
  • I have updated my post with the explain output. However, I'm not sure if this explain answers my question. It appears from the output that the two joins are performed independently but I still don't get if that means that join_collapse_limit is evaluated for each one of them or for the entire query (as this is the output for the entire query after all). Like I said in my post, I assume it's independent but I would like to be sure.
    – george_1111
    Commented Jul 7 at 11:05

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.