From: Michael Glaesemann <grzm@seespotcode.net>
To: Leif Mortenson <leiflists@tanukisoftware.com>
Cc: Postgres Performance <pgsql-performance@postgresql.org>
Subject: Re: Index ignored on column containing mostly 0 values
Date: Tue, 31 Oct 2006 13:31:35 +0900
Message-ID: <B62BD380-870A-42F9-8C56-52734A313AFD@seespotcode.net> (raw)
In-Reply-To: <4546CB3C.4020206@tanukisoftware.com>
References: <4546CB3C.4020206@tanukisoftware.com>
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
<snip />
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: grzm@seespotcode.net, leiflists@tanukisoftware.com
Subject: Re: Index ignored on column containing mostly 0 values
In-Reply-To: <B62BD380-870A-42F9-8C56-52734A313AFD@seespotcode.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox