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.

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
  • I've been trying a few different searches of the PostgreSQL archives. I found a couple describing performance differences between writing it with ON versus USING, although in the particular case the plan with USING came out better:

    http://archives.postgresql.org/pgsql-performance/2005-08/msg00266.php

    I still haven't found the post that really explains the issue, yet.

     – kgrittn Apr 12 '12 at 15:03
  • I found a thread on modifications to the planner where FULL JOIN USING was causing problems. Probably not entirely unrelated, if you want to get a sense of the issues at the level of planner code: http://archives.postgresql.org/pgsql-hackers/2008-04/msg01322.php – kgrittn Apr 12 '12 at 15:16
  • 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. – Erwin Brandstetter Apr 12 '12 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: http://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 '12 at 15:51
  • @kgrittn: Thanks for your efforts! This thread is educating on some corner case issues with USING. So the clause may cause trouble with VIEWs in backups after underlying tables are altered. Sounds more like a security/stability than a performance issue. I ran into a similar issue with functions using table types just yesterday in this answer on SO. It's long, look close to the end. – Erwin Brandstetter Apr 12 '12 at 16:14
  • 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 08 '12 at 20:07
  • 1
    And of course if you use the SQl Antipattern of naming your id fields ID, then USING will likely join to the wrong field. – HLGEM Jun 28 '12 at 20:58
  • 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. – Erwin Brandstetter Jun 28 '12 at 21:03
  • @ErwinBrandstetter, I brought it up more for people reading this later, I could tell by your question that you knew how to use USING. I just didn't want someone to come along later and say "oh cool" and not realize that there were unfortunate effects if you use ID. – HLGEM Jun 28 '12 at 21:28
  • Erwin, you probably get more detailed answers if you post this to the PostgreSQL mailing list. I'd try -performance or even -hackers. –  Aug 08 '12 at 21:39
  • @a_horse_with_no_name Would you still recommend that today? If not, you may opt to delete your comment so that newcomers don't leave SO with their question if, in fact, that's the go-to place for detailed answers on Postgres questions today and concentrate effort here. – Christiaan Westerbeek Apr 23 '18 at 15:56
  • 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 –  Apr 23 '18 at 16:17

1 Answers1

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.

Chris Travers
  • 13,042
  • 50
  • 95