Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 8D6989FB1BA for ; Tue, 31 Oct 2006 00:31:43 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 43796-02 for ; Tue, 31 Oct 2006 00:31:39 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from proton.planetargon.com (unknown [198.145.37.35]) by postgresql.org (Postfix) with ESMTP id 249159F9FE1 for ; Tue, 31 Oct 2006 00:31:39 -0400 (AST) Received: from [172.16.1.106] (unknown [61.197.227.146]) by proton.planetargon.com (Postfix) with ESMTP id 9A87E8721E9; Mon, 30 Oct 2006 20:31:37 -0800 (PST) In-Reply-To: <4546CB3C.4020206@tanukisoftware.com> References: <4546CB3C.4020206@tanukisoftware.com> Mime-Version: 1.0 (Apple Message framework v752.2) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: Cc: Postgres Performance Content-Transfer-Encoding: 7bit From: Michael Glaesemann Subject: Re: Index ignored on column containing mostly 0 values Date: Tue, 31 Oct 2006 13:31:35 +0900 To: Leif Mortenson X-Mailer: Apple Mail (2.752.2) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200610/622 X-Sequence-Number: 21751 On Oct 31, 2006, at 13:04 , Leif Mortenson wrote: > Hello, > I have been having a problem with the following query ignoring an > index > on the foos.bar column. > > SELECT c.id > FROM foos c, bars r > WHERE r.id != 0 > AND r.modified_time > '2006-10-20 10:00:00.000' > AND r.modified_time <= '2006-10-30 15:20:00.000' > AND c.bar = r.id > Having a column containing large numbers of null or 0 values seems > fairly > common. 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. Try create index foo_non_zero_bar_index on foos(bar) where bar <> 0; Take a look on the docs on partial indexes for more information. http://www.postgresql.org/docs/current/interactive/indexes-partial.html Hope this helps. Michael Glaesemann grzm seespotcode net