Обсуждение: Planner not using UNIQUEness of index properly

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:

=> explain analyze select * from allwords where lower(word) = 'dog';
                                                             QUERY PLAN
------------------------------------------------------------------------
----------------------------------------------------------
  Index Scan using ix_allwords_lower on allwords  (cost=0.00..2278.27
rows=763 width=19) (actual time=5.385..5.390 rows=1 loops=1)
    Index Cond: (lower(word) = 'dog'::text)
  Total runtime: 5.482 ms
(3 rows)

Oddly, it does project one row for the caseful lookup.

=> explain analyze select * from allwords where word = 'dog';
                                                QUERY PLAN
------------------------------------------------------------------------
---------------------------------
  Seq Scan on allwords  (cost=0.00..3007.16 rows=1 width=19) (actual
time=76.197..303.770 rows=1 loops=1)
    Filter: (word = 'dog'::text)
  Total runtime: 303.858 ms
(3 rows)

It's using the index in the first query, but the bad projection seems
to screw up more complicated joins where the planner decides to use a
table scan, resulting in plans a thousand times slower (yes, I did
explain analyze).  This is with a stats target of 100.

I would think UNIQUE => one row is pretty obvious - what am I
missing?  (Unless it's that I'm still stuck in 7.4.)  Sorry if this
is well-known - I couldn't find anything in the archives.

Thanks.

- John D. Burger
   MITRE

John Burger <john@mitre.org> writes:
> 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:

> I would think UNIQUE => one row is pretty obvious - what am I
> missing?  (Unless it's that I'm still stuck in 7.4.)

That would be the problem :-( ... a look at the code suggests that the
ability to do anything intelligent with expression indexes was added
in 8.0.

            regards, tom lane

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.

Tom Lane wrote:

>> I would think UNIQUE => one row is pretty obvious - what am I
>> missing?  (Unless it's that I'm still stuck in 7.4.)
>
> That would be the problem :-( ... a look at the code suggests that the
> ability to do anything intelligent with expression indexes was added
> in 8.0.

Whaa.  Okay, more ammunition, thanks.

- John D. Burger
   MITRE