40

It was brought to my attention that the USING construct (instead of ON) in the FROM clause of SELECT queries might introduce optimization barriers in certain cases.

I mean this key word:

SELECT *
FROM   a
JOIN   b USING (a_id)

Just in more complex cases.

Context: this comment to this question.

I use this a lot and have never noticed anything so far. I would be very interested in a test case demonstrating the effect or any links to further information. My search efforts came up empty.

The perfect answer would be a test case to show USING (a_id) with inferior performance when compared to the alternative join clause ON a.a_id = b.a_id - if that can actually happen.

12
  • 2
    @kgrittn: That's what I generally expected so far: that USING is slightly faster - as it results in one less column in the result matrix. Your findings date back to 2005 and 2008. I assume any issues have been fixed by now. However, I can see a possible limitation: JOINs with USING may have to be applied in order, as the resulting joining column are a joint product. Thereby potentially limiting options in reordering of JOINs. Apr 12, 2012 at 15:21
  • 1
    I found this thread which may have had something to do with putting me off from using it as often as I had, because a VIEW with a USING condition on a join can cause problems on dump/restore: archives.postgresql.org/pgsql-bugs/2011-06/msg00030.php I still have a nagging feeling there was another thread related to performance problems with USING where the workaround was to use ON, but I'm going to give up on finding it, I think. It's probably safe to use it outside of views and just remember to try ON instead as a diagnostic step if a query is slow.
    – kgrittn
    Apr 12, 2012 at 15:51
  • 1
    It looks like "using" make the code a little bit readable but I guess that both fields need the same name. I don't think that using will have a better performance than a "on", because the DB need to make the match anyway, it's like a select have the same performance than a join (correct me if I'm wrong), the difference is that Join is cleaner and easier to maintain.
    – jcho360
    May 8, 2012 at 20:07
  • 2
    @HLGEM: It's just a symbolic name, and with only two tables, like in my example, there is no room for confusion. Still, I amended the question. Wouldn't want to encourage the unfortunate use of id as column name. Jun 28, 2012 at 21:03
  • 3
    @ChristiaanWesterbeek: I disagree. The "go-to place" for in-depths Postgres answer is (still) the mailing. Only very few Postgres devs are active on SO, but all Postgres devs and experts read the mailing list
    – user1822
    Apr 23, 2018 at 16:17

1 Answer 1

12

Erwin: I would concur with the idea that USING causing rigid ordering could well create many edge cases where optimal plans would be ruled out. I recently helped someone out who had something like this in his query:

LEFT JOIN ( 
     a 
     JOIN b ON a.id = b.a_id
     JOIN c ON b.c_id = c.id
) ON a.id = something.a_id
LEFT JOIN (
     table1 t1
     JOIN table2 t2 ON t1.some_field = t2.other_field
     JOIN talbe3 t3 ON t2.yafield = t3.something_else
) ON ....
repeat a few more times

In his case the worst of these join blocks was causing a nested loop join through some 200k rows, about 20k times (do the math), and since keys couldn't be pushed to indexes, it was a sequential scan. This meant that the overall query took about 3 hours to run due to cascading plan changes. By distributing the left join, the keys could be pushed down and the query ran in a matter of seconds. Of course this isn't exactly equivalent which is why the planner can't treat them as equivalent and so it was left figuring out that plan as a hash join and then doing a nested loop in, which was painfully slow.

Any time you rigidly force the joins to go through in a certain order you introduce cases where key filter information may not be available yet in the execution of the plan, and so what might be possible to do later in a quick index scan/hash join might be have to be done much slower in a nested loop/sequential scan and so while the above fragment is not immediately equivalent, it shows the same problem.

Your Answer

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

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