Thread: BUG #5230: Limit operator slows down
The following bug has been logged online: Bug reference: 5230 Logged by: aftab Email address: akhangd@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)
On Thu, Dec 3, 2009 at 7:07 AM, aftab <akhangd@hotmail.co.uk> wrote: > > The following bug has been logged online: > > Bug reference: =A0 =A0 =A05230 > Logged by: =A0 =A0 =A0 =A0 =A0aftab > Email address: =A0 =A0 =A0akhangd@hotmail.co.uk > PostgreSQL version: 8.3.8 > Operating system: =A0 Centos 5 > Description: =A0 =A0 =A0 =A0Limit operator slows down > Details: > > S1=3D"SELECT * > FROM position WHERE > position.POSITION_STATE_ID=3D2 AND > position.TARGET_ID=3D18 > ORDER BY position.ID DESC > =A0"; > > =A0S2=3D"SELECT * > =A0FROM position WHERE > =A0position.POSITION_STATE_ID=3D2 AND > =A0position.TARGET_ID=3D18 > =A0ORDER BY position.ID DESC > =A0LIMIT 1 > =A0"; > > =A0S1 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: > > =A0QUERY PLAN > -------------------------------------------------------------------------= --- > ------------------------------------------------------------- > =A0Sort =A0(cost=3D209341.40..209767.55 rows=3D170457 width=3D297) (actual > time=3D0.200..0.200 rows=3D1 loops=3D1) > =A0 Sort Key: id > =A0 Sort Method: =A0quicksort =A0Memory: 25kB > =A0 -> =A0Bitmap Heap Scan on "position" =A0(cost=3D5050.22..146754.50 ro= ws=3D170457 > width=3D297) (actual time=3D0.188..0.189 rows=3D1 loops=3D1) > =A0 =A0 =A0 =A0 Recheck Cond: (target_id =3D 18) > =A0 =A0 =A0 =A0 Filter: (position_state_id =3D 2) > =A0 =A0 =A0 =A0 -> =A0Bitmap Index Scan on position_target_fk =A0(cost=3D= 0.00..5007.61 > rows=3D170912 width=3D0) (actual time=3D0.134..0.134 rows=3D2 loops=3D1) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 Index Cond: (target_id =3D 18) > =A0Total runtime: 0.275 ms > (9 rows) > > Query Plan for S2: > > =A0QUERY PLAN > -------------------------------------------------------------------------= --- > -------------------------------------------------------------------------= --- > --- > =A0Limit =A0(cost=3D0.00..2.43 rows=3D1 width=3D297) (actual time=3D3053.= 220..3053.221 > rows=3D1 loops=3D1) > =A0 -> =A0Index Scan Backward using position_pkey on "position" > (cost=3D0.00..413713.28 rows=3D170459 width=3D297) (actual time=3D3053.21= 6..3053.216 > rows=3D1 loops=3D1) > =A0 =A0 =A0 =A0 Filter: ((position_state_id =3D 2) AND (target_id =3D 18)) > =A0Total runtime: 3053.297 ms > (4 rows) This is the same thing you posted yesterday.... ...Robert
On Thu, Dec 3, 2009 at 10:45 AM, aftab khan <akhangd@hotmail.co.uk> wrote: > This is not exactly the same what I posted yesterday, What I have posted > today also include EXPLAIN ANALYZE OUTPUT . [ adding the list back to the CC line ] It looks like you have a problem very similar to one Laurence Laborde was complaining about recently on pgsql-performance. My discussion of the problem is here: http://archives.postgresql.org/pgsql-performance/2009-12/msg00022.php In your case, the reason why the estimates are off is because of the lack of multi-column statistics, rather than because of the choice of operator, but the issues are similar. ...Robert