Re: Strange logic for partial index proving

On Tue, Jun 21, 2005 at 10:33:45PM +0100, Simon Riggs wrote:
> On Tue, 2005-06-21 at 16:29 -0400, Tom Lane wrote:
> > > create index idx1 on tenk (col1) where col1 > 1 and col1 < 10;
> >
> > > explain select * from tenk where col1 > 5 and col1 < -5;
> > > [ uses that index ]
> >
> > This is a perfectly legitimate situation.
>
> Like I said, its correct. I didn't suggest changing it.
>
> > "col1 > 5" implies "col1 > 1"
> > and "col1 < -5" implies "col1 < 10", therefore the query WHERE condition
> > implies the index predicate, therefore the index contains all tuples
> > that could pass the WHERE condition, therefore the index is usable.
>
> ..."all tuples that pass the WHERE condition", like none.
>
> Guess I'm not Mr Logic.

Has anyone looked at how hard it would be to identify impossible
conditions as part of planning the query? In this case, you obviously
can't get any results, so there's no point in even planning anything. Of
course this is a somewhat nonsensical example, but I suspect that there
are cases where QBE or other front-ends will generate queries that
contain some impossible conditions that can be eliminated.
--
Jim C. Nasby, Database Consultant decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"