[BUGS] BUG #5226: Limit operator slows down

2009-12-02 Thread aftab 

The following bug has been logged online:

Bug reference:  5226
Logged by:  aftab
Email address:  akha...@hotmail.co.uk
PostgreSQL version: 8.3.8
Operating system:   Centos 5
Description:Limit operator slows down
Details: 

S1="SELECT *
FROM position WHERE 
position.POSITION_STATE_ID=2 AND 
position.TARGET_ID=18
ORDER BY position.ID DESC 
";
S2="SELECT *
FROM position WHERE 
position.POSITION_STATE_ID=2 AND 
position.TARGET_ID=18
ORDER BY position.ID DESC 
LIMIT 1
";

S1 takes 0.16ms compared to S2 which takes 5 secs. Both S1 and S2 are same
except "LIMIT 1 " is added to S2.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] BUG #5228: Execution of prepared query is slow when timestamp parameter is used

2009-12-03 Thread aftab 

The following bug has been logged online:

Bug reference:  5228
Logged by:  aftab
Email address:  akha...@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)

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] BUG #5230: Limit operator slows down

2009-12-03 Thread aftab 

The following bug has been logged online:

Bug reference:  5230
Logged by:  aftab
Email address:  akha...@hotmail.co.uk
PostgreSQL version: 8.3.8
Operating system:   Centos 5
Description:Limit operator slows down
Details: 

S1="SELECT *
FROM position WHERE
position.POSITION_STATE_ID=2 AND
position.TARGET_ID=18
ORDER BY position.ID DESC
 ";

 S2="SELECT *
 FROM position WHERE
 position.POSITION_STATE_ID=2 AND
 position.TARGET_ID=18
 ORDER BY position.ID DESC
 LIMIT 1
 ";

 S1 takes 0.16ms compared to S2 which takes 5 secs. Both S1 and S2 are same 
except "LIMIT 1 " is added to S2.

Query Plan for S1:

 QUERY PLAN

-
 Sort  (cost=209341.40..209767.55 rows=170457 width=297) (actual
time=0.200..0.200 rows=1 loops=1)
   Sort Key: id
   Sort Method:  quicksort  Memory: 25kB
   ->  Bitmap Heap Scan on "position"  (cost=5050.22..146754.50 rows=170457
width=297) (actual time=0.188..0.189 rows=1 loops=1)
 Recheck Cond: (target_id = 18)
 Filter: (position_state_id = 2)
 ->  Bitmap Index Scan on position_target_fk  (cost=0.00..5007.61
rows=170912 width=0) (actual time=0.134..0.134 rows=2 loops=1)
   Index Cond: (target_id = 18)
 Total runtime: 0.275 ms
(9 rows)

Query Plan for S2:

 QUERY PLAN


---
 Limit  (cost=0.00..2.43 rows=1 width=297) (actual time=3053.220..3053.221
rows=1 loops=1)
   ->  Index Scan Backward using position_pkey on "position" 
(cost=0.00..413713.28 rows=170459 width=297) (actual time=3053.216..3053.216
rows=1 loops=1)
 Filter: ((position_state_id = 2) AND (target_id = 18))
 Total runtime: 3053.297 ms
(4 rows)

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs