Re: Partial index where clause not filtering through

On Mon, Jun 16, 2003 at 01:43:34AM -0400, Tom Lane wrote:
> "Jim C. Nasby" <jim@nasby.net> writes:
> > As-is, it won't use the index. i.address_v IS NOT NULL AND s.addr_high_v
> > >= i.address_v should mandate that s.addr_high_v must be not-null,
>
> Actually, if the >= operator is strict then it implies both NOT NULL
> conditions.  But I am not excited about putting some kind of theorem
> prover into the partial-index logic.  That is a recipe for chewing up
> huge numbers of cycles trying (and, likely, failing) to prove that
> a partial index is safe to use with the current query.
>
> Inference rules that are limited to strict operators and NOT NULL
> clauses wouldn't cost as much as a general theorem prover, but they'd
> not find useful improvements as often, either.  So the question is
> still whether the game is worth the candle.  How often do you think
> this would win, and is that worth the planner cycles expended on every
> query to find out if it wins?

Well, it would only need to make the checks if the table had partial
indexes. Even then, it probably makes sense to only do the check if
other query planning steps decide it would be useful to use the partial
index. So that means that for a lot of general use cases, performance
won't be impacted.

When you get to the cases that would be impacted, the planner should
probably look for key clauses first; so if you were worried about
planning time, you would put an explicit clause in the query (I'm in the
habit of doing this for joins when joining three tables on the same
key... FROM a, b, c WHERE a.f1=b.f1 and b.f1=c.f1 and a.f1=c.f1. I would
hope the planner would figure out that a.f1 must = c.f1, but some
don't). In many cases, planning time isn't a big deal; either the query
is run often enough that it should stay in the plan cache (pgsql does
cache plans, right?), or it's run infrequently enough that it's not a
big deal.

Of course, this might extend well beyond just partial indexes, as my a,
b, c example shows.
--
Jim C. Nasby (aka Decibel!)                    jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
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?"