Thread: Join syntax and join order

Join syntax and join order

From
Peter Eisentraut
Date:
Can we make the fact that the "explicit" inner join syntax also controls
the join order optional?  It's pretty annoying, because that syntax is
supposed to be a convenience but with PostgreSQL it's a recipe to slow
down your applications.

-- 
Peter Eisentraut   peter_e@gmx.net



Re: Join syntax and join order

From
Tom Lane
Date:
Peter Eisentraut <peter_e@gmx.net> writes:
> Can we make the fact that the "explicit" inner join syntax also controls
> the join order optional?

I'm certainly not wedded to it, but what's the implementation plan,
and how will you make the control available to them that needs it?
        regards, tom lane


Re: Join syntax and join orde

From
Don Baccus
Date:
Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
> 
>>Can we make the fact that the "explicit" inner join syntax also controls
>>the join order optional?
> 
> 
> I'm certainly not wedded to it, but what's the implementation plan,
> and how will you make the control available to them that needs it?

How do you do it now with the non-standard syntax?  You can get some 
control by use of subselects.  In Oracle we use this trick in some cases 
because it has the nasty habit of applying PL/SQL functions to all rows 
of a relationship *before* qualification.

The problem with the current situtation is that someone who writes a 
standard SQL92 query mixing inner and outer joins gets the benefit of 
optimizer decisions in Oracle 9i and other commercial databases that 
supports the syntax, but not PostgreSQL.

For those of us who write applications designed to work on multiple 
RDBMSs this poses something of a problem.  In our case not much of a 
current one because we still support Oracle 8i which doesn't have SQL92 
join syntax, so we're forced to write different outer join queries for 
it and PG anyway.  But eventually we'll drop Oracle 8i support and look 
to share queries, at least for new code.

-- 
Don Baccus
Portland, OR
http://donb.photo.net, http://birdnotes.net, http://openacs.org