indexing with lower(...) -> queries are not optimised very well - Please Help

Поиск
Список
Период
Сортировка
От Martin Hampl
Тема indexing with lower(...) -> queries are not optimised very well - Please Help
Дата
Msg-id 64AEDDDE-19F4-11D8-9CB8-000393674318@gmx.de
обсуждение исходный текст
Ответы Re: indexing with lower(...) -> queries are not optimised very well  (CoL <col@mportal.hu>)
Re: indexing with lower(...) -> queries are not optimised very well - Please Help  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
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.


В списке pgsql-general по дате отправления:

Предыдущее
От: "Keith C. Perry"
Дата:
Сообщение: Re: building 7.4 with plperl
Следующее
От: "pw"
Дата:
Сообщение: problem running postmaster