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

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; ?

C.