Hi,
I am using PostgreSQL 7.4, but I did have the same problem with the
last version.
I indexed the column word (defined as varchar(64)) using lower(word).
If I use the following query, everything is fine, the index is used and
the query is executed very quickly:
select * from token where lower(word) = 'saxophone';
However, with EXPLAIN you get the following:
QUERY PLAN
------------------------------------------------------------------------
----------------
Index Scan using word_lower_idx on token (cost=0.00..98814.08
rows=25382 width=16)
Index Cond: (lower((word)::text) = 'saxophone'::text)
I indexed the same column without the use of lower(...). Now
explain select * from token where word = 'saxophone';
results in:
QUERY PLAN
------------------------------------------------------------------------
-----
Index Scan using word_idx on token (cost=0.00..6579.99 rows=1676
width=16)
Index Cond: ((word)::text = 'saxophone'::text)
Please note the difference in the estimated cost! Why is there such a
huge difference? Both queries almost exactly need the same time to
execute (all instances of 'saxophone' in the table are lower-case (this
is a coincidence)).
The Problem is, if I use this query as part of a more complicated query
the optimiser chooses a *very* bad query plan.
Please help me. What am I doing wrong? I would appreciate any help an
this very much.
Regards,
Martin.
