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

Re: plpgsql functions vs. embedded queries

From: "Wright, George" <George(dot)Wright(at)infimatic(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "John DeSoi" <desoi(at)pgedit(dot)com>,<pgsql-novice(at)postgresql(dot)org>
Subject: Re: plpgsql functions vs. embedded queries
Date: 2008-09-30 13:42:23
Message-ID: 51548D6D5BEB57468163194A8C1A0E980161A5EE@MAGPTCPEXC02.na.mag-ias.net (view raw or flat)
Thread:
Lists: pgsql-novice
Thank you! That's a great reason to upgrade.


-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us] 
Sent: Tuesday, September 30, 2008 9:37 AM
To: Wright, George
Cc: John DeSoi; pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] plpgsql functions vs. embedded queries 

"Wright, George" <George(dot)Wright(at)infimatic(dot)com> writes:
> Hope this isn't too much detail. The prepared statement was barely
> faster and the raw stored proc was much slower.

Well, here's your problem:
 
> CREATE OR REPLACE FUNCTION TestStoredProc(integer, text, text) RETURNS

$2 and $3 presumably ought to be timestamp with time zone, not text.
In the prepared statement those parameters default to being of the same
type as what they're compared to.  Here, you've forced a textual
comparison to occur, which doesn't match the index on alert_data,
so you end up with a slow seqscan ... and possibly not even the
right answers, if the supplied dates are formatted at all strangely.

(8.3 would have saved you from this mistake, btw, because it won't
do implicit casts to text.)

			regards, tom lane

In response to

pgsql-novice by date

Next:From: Joshua TolleyDate: 2008-09-30 16:58:13
Subject: Re: !!! Compress individual tables with postgres
Previous:From: Tom LaneDate: 2008-09-30 13:37:14
Subject: Re: plpgsql functions vs. embedded queries

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