Skip site navigation (1) Skip section navigation (2)

Re: query plans and immutable functions

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 (view raw or flat)
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


In response to

Responses

sfpug by date

Next:From: eleinDate: 2006-05-09 18:49:46
Subject: Re: query plans and immutable functions
Previous:From: Reece HartDate: 2006-05-09 17:51:00
Subject: query plans and immutable functions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group