Theo Kramer a écrit :
Hi
I have been having some serious performance issues when using prepared
statements which I can not re-produce when using a direct statement. Let
me try to explain
The query does an order by in descending order on several columns for
which an index exists.
The explain output as follows
rascal=# explain SELECT oid, * FROM calllog
WHERE calllog_mainteng = '124 '
AND calllog_phase = 8
AND calllog_self < 366942
OR calllog_mainteng = '124 '
AND calllog_phase < 8
ORDER BY calllog_mainteng DESC,
calllog_phase DESC,
calllog_self DESC limit 25;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Limit (cost=0.00..111.62 rows=25 width=2164)
-> Index Scan Backward using calllog_rmc_idx on calllog
(cost=0.00..53475.22 rows=11977 width=2164)
Index Cond: (calllog_mainteng = '124 '::bpchar)
Filter: (((calllog_phase = 8) AND (calllog_self < 366942)) OR
(calllog_phase < 8))
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
SELECT oid, * FROM calllog
WHERE calllog_mainteng = '124 '
AND calllog_phase = 8
AND calllog_self < 366942
OR calllog_mainteng = '124 '
AND calllog_phase < 8
ORDER BY calllog_mainteng DESC,
calllog_phase DESC,
calllog_self DESC limit 25
Row[s] = 25, Duration = 435409.474 ms
The index as per the explain is defined as follows
"calllog_rmc_idx" UNIQUE, btree (calllog_mainteng, calllog_phase,
calllog_self)
VACUUM and all those good things done
Version of PostgreSQL 8.1 and 8.2
enable_seqscan = off
enable_sort = off
Any advice/suggestions/thoughts much appreciated
Reading the manual, you can learn that prepared statement can (not)
follow the same plan as direct query:
the plan is make before pg know the value of the variable.
See 'Notes' http://www.postgresql.org/docs/8.2/interactive/sql-prepare.html
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
