Re: Query with rightmost function does not use index

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