Planner not using UNIQUEness of index properly

From: John Burger <john(at)mitre(dot)org>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Planner not using UNIQUEness of index properly
Date: 2007-12-10 15:32:22
Message-ID: A4860E46-4D65-4211-B165-CCB0E6CCC3E3@mitre.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ted Byers 2007-12-10 15:34:11 Re: Simpler dump?
Previous Message Tom Lane 2007-12-10 15:30:20 Re: Re: [GENERAL] cannot dump structures