Re: Query with rightmost function does not use index

From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of - -
Sent: Monday, August 08, 2011 3:40 AM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Query with rightmost function does not use index

Here are the EXPLAIN ANALYZE outputs:

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) (ac
tual 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 filter_hash index uses a "character(n)" data type - the ::bpchar. The
second query is of unknown type and thus is converted to "character" and
then used in the index. The first query use a function that outputs a
"text". Since the output type is known the left-side of the equals is
casted to that known type. Since the index is one the "character" version
of the filter_hash but the comparison requires a "text" version the index
cannot be used. You would need to manually cast the result of the md5
function call to "character" in order to get the index usage; or convert the
filter_hash column to text, the latter option probably being preferred.

It is not a bug, in cases of uncertainty the types of the value and the
indexed field must be the same, but it could possibly be more user-friendly.

I'll leave it to other to comment on whether this is different in more
recent versions. Text-character are binary compatible and so it is not be
unreasonable to assume, like you did, that indexes of one should be usable
by the other.

David J.