Re: Recognizing range constraints (was Re: Plan for relatively simple query seems to be very inefficient)

On Wed, Apr 06, 2005 at 18:09:37 -0400,
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 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
>
> probably can be treated as a range restriction on a.x for this purpose,
> but I'm much less sure that the same is true of
>
> a.x > b.y AND a.x < c.z
>
> Thoughts?

I think it makes sense to guess that a smaller fraction of the rows will
be returned when a column value is bounded above and below than if it
is only bounded on one side, even if the bounds aren't fixed. You can
certainly be wrong. The difference between this and the normal case is that
column statistics aren't normally going to be that useful.

If date/time ranges are the common use for this construct, it might be better
to create date and/or time range types that use rtree or gist indexes.