From: | Reece Hart <reece(at)harts(dot)net> |
---|---|
To: | SF PostgreSQL <sfpug(at)postgresql(dot)org> |
Subject: | Re: query plans and immutable functions |
Date: | 2006-05-09 18:16:58 |
Message-ID: | 1147198618.27835.61.camel@tallac.gene.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | sfpug |
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
From | Date | Subject | |
---|---|---|---|
Next Message | elein | 2006-05-09 18:49:46 | Re: query plans and immutable functions |
Previous Message | Reece Hart | 2006-05-09 17:51:00 | query plans and immutable functions |