Re: Another planner oddity

Philip Warner <pjw@rhyme.com.au> writes:
> At 10:53 3/11/01 -0500, Tom Lane wrote:
>> What's your point?  We can't omit the join to g, as that would change
>> the set of returned rows.

> Oops. Left out too much. Make each of the ref'd tables unique (so only one
> match for given t.p):

Hmm.  That in combination with the LEFT OUTER JOIN might be sufficient
to ensure that the output is the same with or without scanning g ...
but it seems far too fragile and specialized a chain of reasoning to
consider trying to get the planner to duplicate it.

We have to consider not only the potential benefit of any suggested
planner optimization, but also how often it's likely to win and how
many cycles we're likely to waste testing for the condition when it
doesn't hold.  This seems very unpromising.

My thoughts here are probably colored by bad past experience: before
about 6.5, the planner would in fact discard unreferenced relations
from its plan, with the result that it gave wrong answers for
perfectly-reasonable queries like "SELECT count(1) FROM foo".
I won't put back such an optimization without strong guarantees that
it's correct, and that implies a lot of cycles expended to determine
whether the optimization applies.
        regards, tom lane