Re: BUG #5230: Limit operator slows down

On Thu, Dec 3, 2009 at 7:07 AM, aftab <akhangd(at)hotmail(dot)co(dot)uk> wrote:
>
> The following bug has been logged online:
>
> Bug reference:      5230
> Logged by:          aftab
> Email address:      akhangd(at)hotmail(dot)co(dot)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