Re: Eliminating unnecessary left joins

On Wed, 11 Apr 2007, Jim Nasby wrote:

> I agree with others that the way that query is constructed is a bit
> odd, but it does bring another optimization to mind: when doing an
> inner-join between a parent and child table when RI is defined
> between them, if the query only refers to the child table you can
> drop the parent table from the join, because each row in the child
> table must have one and only one row in the parent.

I don't think that's quite true without qualifications. First, I think it
needs to be an immediate constraint (and I don't remember how we handle
set constraints inside functions that might be called from a statement, so
it might need to be not deferrable). Second, I think you also need to take
care of NULLs since child rows with NULLs in the key pass the constraint
but have no rows in the parent and would get culled by the inner join.

Also, there's a possible issue that constraints do not actually guarantee
that they always hold true, merely that they hold true at particular
times. I don't know if it's possible to get a statement executed such that
it would see the table state between the action and constraint check or
if such is allowed by spec.