Re: BUG #5230: Limit operator slows down

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