X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BCAA452869 for ; Tue, 31 May 2005 01:18:51 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 32757-02 for ; Tue, 31 May 2005 04:18:47 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 59D085280A for ; Tue, 31 May 2005 01:18:46 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j4V4IXG3007924; Tue, 31 May 2005 00:18:33 -0400 (EDT) To: Bruno Wolff III Cc: Tobias Brox , pgsql-performance@postgresql.org Subject: Re: Index on a NULL-value In-reply-to: <20050531033633.GA20015@wolff.to> References: <20050531030207.GD26100@tobias.exoweb.net> <20050531032119.GH26100@tobias.exoweb.net> <20050531033633.GA20015@wolff.to> Comments: In-reply-to Bruno Wolff III message dated "Mon, 30 May 2005 22:36:33 -0500" Date: Tue, 31 May 2005 00:18:33 -0400 Message-ID: <7923.1117513113@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200505/461 X-Sequence-Number: 12625 Bruno Wolff III writes: > Looked back at your first example and saw that you didn't use a partial > index which is why you had to contort things to make it possible to > use an indexed search. FWIW, there is code in CVS tip that recognizes the connection between an index on a boolean expression and a WHERE clause testing that expression. It's not quite perfect --- using Tobias' example I see regression=# explain select * from mock where b is NULL; QUERY PLAN ------------------------------------------------------------------------ Index Scan using b_is_null on mock (cost=0.00..51.67 rows=10 width=8) Index Cond: ((b IS NULL) = true) Filter: (b IS NULL) (3 rows) so there's a useless filter condition still being generated. But it gets the job done as far as using the index, anyway. > You want something like this: > CREATE INDEX b_is_null ON mock(b) WHERE b IS NULL; I think best practice for something like this is to make the partial index's columns be something different from what the partial condition tests. Done as above, every actual index entry will be a null, so the entry contents are just dead weight. Instead do, say, CREATE INDEX b_is_null ON mock(a) WHERE b IS NULL; where a is chosen as a column that you frequently also test in conjunction with "b IS NULL". That is, the above index can efficiently handle queries like ... WHERE a = 42 AND b IS NULL ... regards, tom lane