Re: Planner not using UNIQUEness of index properly

On Dec 10, 2007 9:32 AM, John Burger <john@mitre.org> wrote:
> I have a unique function index on one of my tables:
>
> create table allWords (
>    wordID       serial  PRIMARY KEY,
>    word         text    NOT NULL
> );
> create unique index ix_allWords_lower on allWords (lower(word));
>
> To my surprise, the planner does not seem to realize that only one
> row can result from using this index:

that's certainly not what I'm seeing in pgsql 8.2.5 here.

I'm guessing it has to do with being stuck on 7.4.  I found a LOT of
cases where 8.2.5 handles index conditions smarter than 7.4 did.  For
instance this:

select * from table where timestampfield between now() - interval '1
day' and now()

will always generate a seq scan in 7.4 regardless of indexes.  In 8.1
and 8.2 pgsql knows how to use an index.