Re: query plans and immutable functions

On Tue, 2006-05-09 at 10:51 -0700, Reece Hart wrote:
> I've got a simple 1-predicate query that uses an index when the
> condition is literal but does a seq scan when the condition uses the
> result of an immutable function. This leads to terrible performance in
> the latter case. I believe that this is a bug candidate and I'll
> forward it to pgsql-bugs if other sfpug folks agree.

I hate to reply to myself, but I just figured out that the problem is in
casting.

Compare:
unison(at)csb-dev=> explain select pseq_id from pseq where md5=md5(clean_sequence('SDVLELTDEN'));
QUERY PLAN
--------------------------------------------------------------------
Seq Scan on pseq (cost=0.00..441233.47 rows=36772 width=4)
Filter: ((md5)::text = 'f6c80739f2f752132b89535c070192b9'::text)


unison(at)csb-dev=> explain select pseq_id from pseq where md5=md5(clean_sequence('SDVLELTDEN'))::bpchar;
QUERY PLAN
---------------------------------------------------------------------
Index Scan using pseq_md5 on pseq (cost=0.00..6.01 rows=1 width=4)
Index Cond: (md5 = 'f6c80739f2f752132b89535c070192b9'::bpchar)

When md5() is explicitly cast to bpchar, the index is used. I ran
across this issue long ago (in 7.x), but I thought that casting was now
part of query optimization and that explicit casting was no longer
required. I was wrong.

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0