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 0DA3BD1CA50 for ; Wed, 3 Mar 2004 23:50:53 +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 37943-06 for ; Wed, 3 Mar 2004 19:50:58 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id C95BDD1C517 for ; Wed, 3 Mar 2004 19:50:48 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i23Noi1Y049764 for ; Wed, 3 Mar 2004 23:50:44 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i23NZk9Y047698 for pgsql-performance@postgresql.org; Wed, 3 Mar 2004 23:35:46 GMT From: CoL X-Newsgroups: comp.databases.postgresql.performance Subject: Re: Feature request: smarter use of conditional indexes Date: Thu, 04 Mar 2004 00:37:46 +0100 Organization: Hub.Org Networking Services Lines: 29 Message-ID: References: Reply-To: col@mportal.hu Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7a) Gecko/20040219 X-Accept-Language: en-us, en In-Reply-To: To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/115 X-Sequence-Number: 5971 hi, John Siracusa wrote, On 3/3/2004 20:56: > 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. Yes, > I can change the query to this: > > SELECT * FROM t1 WHERE c1 = 123 AND c1 IS NOT NULL; > > In which case the index will be used, but I shouldn't have to. More > practically, since a lot of my SQL is auto-generated, it's difficult to make > this query change just in the cases where I need it. And I'm loathe to > change every "column = value" pair in my auto-generated SQL into a double > pair of "(column = value and column is not null)" It's redundant and looks > pretty silly, IMO. how about: CREATE UNIQUE INDEX i1 ON t1 (c1); WHERE c1 IS NOT NULL in this case what is the point of doing this? You do not need this condition. C.