Re: Index ignored on column containing mostly 0 values

[Michael Glaesemann <grzm(at)seespotcode(dot)net>]

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