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@postgresql.org
>