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. ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
