Re: plpgsql functions vs. embedded queries

Thank you! That's a great reason to upgrade.


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

"Wright, George" <George.Wright@infimatic.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