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

Re: PQexecParams performance

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Harry Rossignol" <harrywr2(at)comcast(dot)net>
Cc: pgsql-bugs(at)postgreSQL(dot)org
Subject: Re: PQexecParams performance
Date: 2005-12-13 18:26:08
Message-ID: 8715.1134498368@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"Harry Rossignol" <harrywr2(at)comcast(dot)net> writes:
> This statement via SQLexec executes in the blink of an eye

> DECLARE XNKPE BINARY CURSOR for SELECT wtsnzblob FROM "NZRECS" WHERE
> wtsevent = '05002' AND wtspaymeth = '$' AND wtspayno = '' AND ( wtstrxtm
>> '19691231 160000' OR (wtstrxtm = '19691231 160000' AND wtscpu > '0')
> OR (wtstrxtm = '19691231 160000' AND wtscpu = '0' AND wtsuser > '0'))
> ORDER BY wtstrxtm,wtscpu,wtsuser LIMIT 64

> This statement via SQLExecParams can be measured in 10's of seconds, and
> takes an amount of time that is linear to the size of the database, even
> though the number of records that equal parameter 1, wtsevent is static.

> DECLARE XNKPE BINARY CURSOR for SELECT wtsnzblob FROM "NZRECS" WHERE
> wtsevent = $1 AND wtspaymeth = $2 AND wtspayno = $3 AND ( wtstrxtm > $4
> OR (wtstrxtm = $4 AND wtscpu > $5) OR (wtstrxtm = $4 AND wtscpu = $5 AND
> wtsuser > $6)) ORDER BY wtstrxtm,wtscpu,wtsuser LIMIT 64

Could we see the EXPLAIN ANALYZE for both cases?  Also the table schema
(particularly the available indexes).

(Note: easiest way to get EXPLAIN ANALYZE by hand for a parameterized
query is to PREPARE it and then issue EXPLAIN ANALYZE EXECUTE.)

			regards, tom lane

pgsql-bugs by date

Next:From: Neil ParkerDate: 2005-12-13 20:22:21
Subject: BUG #2111: Error parsing 'infinity' under some versions of glibc
Previous:From: Andreas PflugDate: 2005-12-13 16:47:51
Subject: Re: COPY TO STDOUT BINARY

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