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 72BFED1EC67 for ; Wed, 3 Mar 2004 21:41:15 +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 93398-08 for ; Wed, 3 Mar 2004 17:41:08 -0400 (AST) Received: from cayenne.palmdigitalmedia.com (cayenne.palmdigitalmedia.com [63.110.43.230]) by svr1.postgresql.org (Postfix) with SMTP id 8A377D1EC69 for ; Wed, 3 Mar 2004 17:40:54 -0400 (AST) Received: (qmail 14398 invoked from network); 3 Mar 2004 19:54:15 -0000 Received: from unknown (HELO ?192.168.124.23?) (63.110.43.226) by 0 with SMTP; 3 Mar 2004 19:54:15 -0000 User-Agent: Microsoft-Entourage/10.1.1.2418 Date: Wed, 03 Mar 2004 14:56:06 -0500 Subject: Feature request: smarter use of conditional indexes From: John Siracusa To: Postgres Performance Message-ID: Mime-version: 1.0 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200403/47 X-Sequence-Number: 5903 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. Thanks for you consideration :) -John