On Tue, May 13, 2008 at 10:57 AM, idc danny <idcdanny@yahoo.com> wrote:
> Hi everybody,
>
> I'm fairly new to PostgreSQL and I have a problem with
> a query:
>
> SELECT * FROM "LockerEvents" LIMIT 10000 OFFSET
> 10990000
>
> The table LockerEvents has 11 Mlillions records on it
> and this query takes about 60 seconds to complete.
> Moreover, even after making for each column in the
> table a index the EXPLAIN still uses sequential scan
> instead of indexes.
Yep. The way offset limit works is it first materializes the data
needed for OFFSET+LIMIT rows, then throws away OFFSET worth's of data.
So, it has to do a lot of retrieving.
Better off to use something like:
select * from table order by indexfield where indexfield between
10000000 and 10001000;
which can use an index on indexfield, as long as the amount of data is
small enough, etc...
