Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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: 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
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 '12 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 '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
1  
@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
show 5 more comments

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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