From: Jim C. Nasby <jim@nasby.net>
To: pgsql-performance@postgresql.org
Subject: Re: Partial index where clause not filtering through
Date: Mon, 16 Jun 2003 01:24:56 -0500
Message-ID: <20030616012456.U66185@flake.decibel.org> (raw)
In-Reply-To: <26793.1055742214@sss.pgh.pa.us>
References: <20030616003118.S66185@flake.decibel.org>
<26793.1055742214@sss.pgh.pa.us>
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?"
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: jim@nasby.net
Subject: Re: Partial index where clause not filtering through
In-Reply-To: <20030616012456.U66185@flake.decibel.org>
References: <20030616003118.S66185@flake.decibel.org> <26793.1055742214@sss.pgh.pa.us> <20030616012456.U66185@flake.decibel.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox