Re: Query with rightmost function does not use index

Поиск
Список
Период
Сортировка
От Andres Freund
Тема Re: Query with rightmost function does not use index
Дата
Msg-id 3662180.qZgrGOlDoE@alap2
обсуждение исходный текст
Ответ на Re: Query with rightmost function does not use index  (- - <loh.law@hotmail.com>)
Список pgsql-general
Hi,

On Monday, August 08, 2011 15:40:20 - - wrote:
> explain analyze select * from filter_item where filter_hash = MD5('');
>
>                                                       QUERY PLAN
> ----------------------------------------------------------------------------
> ------------------------------------------- Seq Scan on filter_item
> (cost=0.00..424644.96 rows=86108 width=49) (actual time=8177.807..12421.921
> rows=77 loops=1) Filter: ((filter_hash)::text =
> 'd41d8cd98f00b204e9800998ecf8427e'::text) Total runtime: 12421.959 ms
> (3 rows)
>
>
> explain analyze select * from filter_item where filter_hash =
> 'd41d8cd98f00b204e9800998ecf8427e'
>
>                                                              QUERY PLAN
> ----------------------------------------------------------------------------
> ---------------------------------------------------------- Bitmap Heap Scan
> on filter_item  (cost=77.92..6609.02 rows=3534 width=49) (actual
> time=0.055..0.100 rows=77 loops=1) Recheck Cond: (filter_hash =
> 'd41d8cd98f00b204e9800998ecf8427e'::bpchar) ->  Bitmap Index Scan on
> filter_item__filter_hash  (cost=0.00..77.04 rows=3534 width=0) (actual
> time=0.049..0.049 rows=77 loops=1) Index Cond: (filter_hash =
> 'd41d8cd98f00b204e9800998ecf8427e'::bpchar) Total runtime: 0.130 ms
> (5 rows)
The problem is that your filter_hash columns seems to be of type char(n). Thats
not directly compatible with text (which is the type returned by the md5
function).
So either change the column type or cast the return type of md5 to char(n).

I do have to admit that this is somewhat strange.

Greetings,

Andres

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

Предыдущее
От: Martin Gainty
Дата:
Сообщение: Re: Russian Language Spam
Следующее
От: hubert depesz lubaczewski
Дата:
Сообщение: Problem with planner