|
Home > Archive > PostgreSQL Bugs > December 2005 > PQexecParams performance
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
PQexecParams performance
|
|
| Harry Rossignol 2005-12-12, 1:24 pm |
| My experience using PQexecParams is that it has a performance
degradation of a factor of 100's if not thousands.
I have reworked my queries to DECLARE a binary cursor for the return
results and using a fixed text statement for the PQexec select.
It doesn't help on Inserts and Updates but it sure helps on straight
select statement queries.
| |
| Tom Lane 2005-12-12, 8:25 pm |
| "Harry Rossignol" <harrywr2@comcast.net> writes:
> My experience using PQexecParams is that it has a performance
> degradation of a factor of 100's if not thousands.
This is demonstrably not so in general. You may be hitting a case where
the planner chooses a poor plan for lack of information about the value
of a parameter ... but with no details about the query or even mention
of which PG version you're talking about, this bug report isn't worth
the electrons it's written on.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Tom Lane 2005-12-13, 1:24 pm |
| "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
> OR (wtstrxtm = '19691231 160000' AND wtscpu = '0' AND wtsuser > '0'))
> ORDER BY wtstrxtm,wtscpu,wtsu
ser 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,wtsu
ser 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
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
|
|
|
|
|