Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id D79759FB265 for ; Tue, 31 Oct 2006 00:40:32 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 80752-10 for ; Tue, 31 Oct 2006 04:40:30 +0000 (UTC) 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 4FB729FB1BA for ; Tue, 31 Oct 2006 00:40:29 -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 k9V4eTLb023895; Mon, 30 Oct 2006 23:40:29 -0500 (EST) To: Leif Mortenson cc: Postgres Performance Subject: Re: Index ignored on column containing mostly 0 values In-reply-to: <4546CB3C.4020206@tanukisoftware.com> References: <4546CB3C.4020206@tanukisoftware.com> Comments: In-reply-to Leif Mortenson message dated "Tue, 31 Oct 2006 13:04:12 +0900" Date: Mon, 30 Oct 2006 23:40:29 -0500 Message-ID: <23894.1162269629@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200610/623 X-Sequence-Number: 21752 Leif Mortenson writes: > Having a column containing large numbers of null or 0 values seems fairly > common. You would likely be better off to use NULL as a no-value placeholder, instead of an arbitrarily chosen regular value (which the planner cannot be certain does not match any entries in the other table...) > Is there way to tell Postgres to create an index of all values with > meaning. Ie all non-0 values? None that I could find. Partial index. Though I'm not sure that would help here. The problem is that the nestloop join you want would be spectacularly awful if there happened to be any zeroes in bars.id, and the planner's statistical estimates allow some probability of that happening. regards, tom lane