The following bug has been logged online:
Bug reference: 5228
Logged by: aftab
Email address: akhangd@hotmail.co.uk
PostgreSQL version: 8.3.8
Operating system: Centos 5
Description: Execution of prepared query is slow when timestamp
parameter is used
Details:
e.g.
prepare testplan (int, int) as
SELECT *
FROM position WHERE
position.POSITION_STATE_ID=$1 AND
position.TARGET_ID=$2
AND position.TIME>='2009-10-30 13:43:32'
ORDER BY position.ID DESC ;
EXPLAIN ANALYZE EXECUTE testplan(2,63)
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------
Sort (cost=166238.58..166370.97 rows=52956 width=297) (actual
time=28.618..28.619 rows=1 loops=1)
Sort Key: id
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on "position" (cost=6182.89..147236.51 rows=52956
width=297) (actual time=28.518..28.521 rows=1 loops=1)
Recheck Cond: (target_id = $2)
Filter: (("time" >= '2009-10-30 13:43:32'::timestamp without time
zone) AND (position_state_id = $1))
-> Bitmap Index Scan on position_target_fk (cost=0.00..6169.65
rows=210652 width=0) (actual time=0.624..0.624 rows=1006 loops=1)
Index Cond: (target_id = $2)
Total runtime: 28.763 ms
(9 rows)
When I replace "time" filter with a parameter then the same query takes
longer
prepare testplan (int, int, timestamp) as
SELECT *
FROM position WHERE
position.POSITION_STATE_ID=$1 AND
position.TARGET_ID=$2
AND position.TIME>=$3
ORDER BY position.ID DESC ;
EXPLAIN ANALYZE EXECUTE testplan(2,63,'2009-10-30 13:43:32');
QUERY
PLAN
----------------------------------------------------------------------------
------------------------------------------------------------------
Sort (cost=154260.75..154348.53 rows=35111 width=297) (actual
time=2852.357..2852.358 rows=1 loops=1)
Sort Key: id
Sort Method: quicksort Memory: 25kB
-> Index Scan using position_time on "position" (cost=0.00..146688.94
rows=35111 width=297) (actual time=0.113..2852.338 rows=1 loops=1)
Index Cond: ("time" >= $3)
Filter: ((position_state_id = $1) AND (target_id = $2))
Total runtime: 2852.439 ms
(7 rows)
