Re: PQexecParams performance - Mailing list pgsql-bugs

From Tom Lane
Subject Re: PQexecParams performance
Date
Msg-id 8715.1134498368@sss.pgh.pa.us
Whole thread Raw
In response to PQexecParams performance  ("Harry Rossignol" <harrywr2@comcast.net>)
List pgsql-bugs
"Harry Rossignol" <harrywr2@comcast.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:

Previous
From: Andreas Pflug
Date:
Subject: Re: COPY TO STDOUT BINARY
Next
From: "Alexei"
Date:
Subject: BUG #2110: initdb set wrong default encoding