From: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: John Siracusa <siracusa@mindspring.com>
Cc: Postgres Performance <pgsql-performance@postgresql.org>
Subject: Re: Feature request: smarter use of conditional indexes
Date: Thu, 04 Mar 2004 09:31:48 +0800
Message-ID: <40468704.9030601@familyhealth.com.au> (raw)
In-Reply-To: <19341.1078358036@sss.pgh.pa.us>
References: <BC6BA286.833F4%siracusa@mindspring.com>
<19341.1078358036@sss.pgh.pa.us>
>>Given an index like this:
>> CREATE UNIQUE INDEX i1 ON t1 (c1) WHERE c1 IS NOT NULL;
>>and a query like this:
>> SELECT * FROM t1 WHERE c1 = 123;
>>I'd like the planner to be smart enough to use an index scan using i1.
>
>
> Send a patch ;-)
>
> The routine you want to teach about this is pred_test_simple_clause() in
> src/backend/optimizer/path/indxpath.c. ISTM that it's legitimate to
> conclude that "foo IS NOT NULL" is implied by "foo op anything" or
> "anything op foo" if the operator is marked strict.
I've actually mentioned this one before in that of all the partial
indexes I have, almost all of then are a WHERE x IS NOT NULL format. I
don't know if that's a common use, but if it is, then maybe it's worth
just adding the knowledge for IS NOT NULL...
The other thing is that at the moment, cascading foreign keys will not
use partial indexes even if they match the predicate. Maybe an IS NOT
NULL hack will help there...
Chris
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: chriskl@familyhealth.com.au, tgl@sss.pgh.pa.us, siracusa@mindspring.com
Subject: Re: Feature request: smarter use of conditional indexes
In-Reply-To: <40468704.9030601@familyhealth.com.au>
* 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