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

Re: plpgsql functions vs. embedded queries

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Wright, George" <George(dot)Wright(at)infimatic(dot)com>
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:37:14
Message-ID: 11123.1222781834@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
"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

Responses

pgsql-novice by date

Next:From: Wright, GeorgeDate: 2008-09-30 13:42:23
Subject: Re: plpgsql functions vs. embedded queries
Previous:From: Wright, GeorgeDate: 2008-09-30 13:25:33
Subject: Re: plpgsql functions vs. embedded queries

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