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.
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
