X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 9AD3A9DCCA3 for ; Fri, 24 Mar 2006 19:48:16 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 82148-03 for ; Fri, 24 Mar 2006 19:48:19 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 7424A9DC8AF for ; Fri, 24 Mar 2006 19:48:14 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k2ONmHBf012728; Fri, 24 Mar 2006 18:48:17 -0500 (EST) To: "Daniel Caune" cc: pgsql-sql@postgresql.org Subject: Re: Index on nullable column In-reply-to: <1E293D3FF63A3740B10AD5AAD88535D201DA051D@UBIMAIL1.ubisoft.org> References: <1E293D3FF63A3740B10AD5AAD88535D201DA051D@UBIMAIL1.ubisoft.org> Comments: In-reply-to "Daniel Caune" message dated "Fri, 24 Mar 2006 17:13:15 -0500" Date: Fri, 24 Mar 2006 18:48:17 -0500 Message-ID: <12727.1143244097@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.114 required=5 tests=[AWL=0.114] X-Spam-Score: 0.114 X-Spam-Level: X-Archive-Number: 200603/404 X-Sequence-Number: 24750 "Daniel Caune" writes: > Is an index on a nullable column useful for retrieving rows having that > column null? Nope, because IS NULL isn't an indexable operator. You can make an end-run around that with a partial index, eg create index fooi on foo(f1) where f1 is null This can be used to satisfy queries using "where f1 is null", but it's not any good for any other purpose. If you often do "where f1 is null and something-about-f2", you might get better mileage with create index fooi on foo(f2) where f1 is null but it's still a very specialized index. regards, tom lane