On Thu, Dec 3, 2009 at 7:07 AM, aftab <akha...@hotmail.co.uk> wrote: > > 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)
This is the same thing you posted yesterday.... ...Robert -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
