Re: indexing with lower(...) -> queries are not optimised very well

Hi,

> hi,
>
> Martin Hampl wrote, On 11/18/2003 7:24 PM:
>> 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)).
> And after analyze token; ?

No, doesn't work (I tried that of course). But this might be the
problem: how to analyse properly for the use of an index with
lower(...).

Thanks for the answer,
Martin.

>
> C.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo(at)postgresql(dot)org
>