Re: limit /offset

On Sat, Mar 16, 2002 at 12:13:41PM +0200, 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

[snip]

> Can anyone explain why offset doesn't use the index available?

What makes you think that an index makes it faster to lookup the 10,000th row
in a table? A index indexes on *values* and has nothing to do with the row
numbers. So your query has to go through and check the 10,000 rows to ensure
they actually match your query. That's what takes the time.

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Ignorance continues to thrive when intelligent people choose to do
> nothing. Speaking out against censorship and ignorance is the imperative
> of all intelligent people.