Re: Recognizing range constraints (was Re: [PERFORM] Plan

Tom Lane wrote:
> "Jim C. Nasby" <decibel@decibel.org> writes:
>
>>On Wed, Apr 06, 2005 at 06:09:37PM -0400, Tom Lane wrote:
>>
>>>Can anyone suggest a more general rule?  Do we need for example to
>>>consider whether the relation membership is the same in two clauses
>>>that might be opposite sides of a range restriction?  It seems like
>>>
>>>a.x > b.y AND a.x < b.z
>
>
>>In a case like this, you could actually look at the  data in b and see
>>what the average range size is.
>
>
> Not with the current statistics --- you'd need some kind of cross-column
> statistics involving both y and z.  (That is, I doubt it would be
> helpful to estimate the average range width by taking the difference of
> independently-calculated mean values of y and z ...)  But yeah, in
> principle it would be possible to make a non-default estimate.
>
>             regards, tom lane

Actually, I think he was saying do a nested loop, and for each item in
the nested loop, re-evaluate if an index or a sequential scan is more
efficient.

I don't think postgres re-plans once it has started, though you could
test this in a plpgsql function.

John
=:->