Re: Performance problems with prepared statements

On 10/10/07, Theo Kramer <theo(at)flame(dot)co(dot)za> wrote:
> When running the query directly from psql it returns the required rows
> in less than 100 milli-seconds.
>
> However, when using a prepared statement from my C application on the
> above query and executing it the query duration is as follows
> ...
> Row[s] = 25, Duration = 435409.474 ms
>

How are you timing it? Does it really take 435 seconds to complete?
Try the following in psql:

postgres# PREPARE yourplan (VARCHAR, INT, INT) AS
SELECT oid, * FROM calllog
WHERE calllog_mainteng = $1
AND calllog_phase = $2
AND calllog_self < $3
OR calllog_mainteng = $1
AND calllog_phase < 8
ORDER BY calllog_mainteng DESC,
calllog_phase DESC,
calllog_self DESC limit 25;

postgres# EXECUTE yourplan('124 ', 8, 366942);

--
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation | fax: 732.331.1301
499 Thornall Street, 2nd Floor | jonah(dot)harris(at)enterprisedb(dot)com
Edison, NJ 08837 | http://www.enterprisedb.com/