Re: limit /offset

On Sat, 16 Mar 2002, Pirtea Calin wrote:

> When i checkout the plan for this statement
>     select * from detail2 where id>125000 order by info1 limit 10
> is:
>     Limit (cost=0.00..1.85 rows=10 width=52)
>     -> Index Scan using detail2_ind1 on detail2 (cost=0.00..61.50 rows=333
> width=52)
> and it takes less than a second to complete
> but when i use offset in the same statement
>     select * from detail2 where id>125000 order by info1 limit 10 offset
> 10000
> the plan changes
>     Limit (cost=36.47..36.47 rows=1 width=52)
>         -> Sort (cost=36.47..36.47 rows=333 width=52)
>             -> Seq Scan on detail2 (cost=0.00..22.50 rows=333 width=52)
> and it takes almost 50 seconds to complete (42.890 sec)
> Can anyone explain why offset doesn't use the index available?
>

How many rows does detail2 have? What does explain say for the query and
how long does it take if you set enable_seqscan=off before it?