Re: Superfluous merge/sort

On Wed, 26 Feb 2003, Anuradha Ratnaweera wrote:

> On Tue, Feb 25, 2003 at 07:56:14AM -0800, Stephan Szabo wrote:
> >
> > On Tue, 25 Feb 2003, Anuradha Ratnaweera wrote:
> >
> > > Question in brief: does the planner/optimizer take into account the
> > > foreign key constraints?
> > >
> > > If the answer is "no", please stop reading here.
> >
> > Not really. However, as a note, from t1,t2 where t1.id=t2.id is not
> > necessarily an identity even with the foreign key due to NULLs.
>
> "not null" doesn't make a difference, either :-(

No, but the two queries you gave aren't equivalent without a not null
constraint and as such treating the second as the first is simply wrong
without it. ;)

The big thing is that checking this would be a cost to all queries (or at
least any queries with joins). You'd probably have to come up with a
consistent set of rules on when the optimization applies (*) and then show
that there's a reasonable way to check for the case that's significantly
not expensive (right now I think it'd involve looking at the constraint
table, making sure that all columns of the constraint are referenced and
only in simple ways).

(*) - I haven't done enough checking to say that the following is
sufficient, but it'll give an idea:
Given t1 and t2 where t2 is the foreign key table and t1 is the
primary key table in a foreign key constraint, a select that has no
column references to t1 other than to the key fields of the foreign key
directly in the where clause where the condition is simply
t1.pcol = t2.fcol (or reversed) and all key fields of the constraint
are so referenced then there exist two possible optimizations
if all of the foreign key constraint columns in t2 are marked as
not null, the join to t1 is redundant and it and the conditions
that reference it can be simply removed
otherwise, the join to t1 and the conditions that reference may be
replaced with a set of conditions (t2.fcol1 is not null [and t2.fcol2
is not null ...]) anded to any other where clause elements