X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 8954ED1BC4D for ; Thu, 4 Mar 2004 01:26:32 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 59123-06 for ; Wed, 3 Mar 2004 21:26:36 -0400 (AST) Received: from houston.familyhealth.com.au (fhnet.arach.net.au [203.22.197.21]) by svr1.postgresql.org (Postfix) with ESMTP id E27F7D1B9A6 for ; Wed, 3 Mar 2004 21:26:23 -0400 (AST) Received: from familyhealth.com.au (work-40.internal [192.168.0.40]) by houston.familyhealth.com.au (8.12.9p1/8.12.9) with ESMTP id i241QDGi070613; Thu, 4 Mar 2004 09:26:13 +0800 (WST) (envelope-from chriskl@familyhealth.com.au) Message-ID: <40468704.9030601@familyhealth.com.au> Date: Thu, 04 Mar 2004 09:31:48 +0800 From: Christopher Kings-Lynne User-Agent: Mozilla Thunderbird 0.5 (Windows/20040207) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: John Siracusa , Postgres Performance Subject: Re: Feature request: smarter use of conditional indexes References: <19341.1078358036@sss.pgh.pa.us> In-Reply-To: <19341.1078358036@sss.pgh.pa.us> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/56 X-Sequence-Number: 5912 >>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