Re: Problem with 11 M records table

On Tue, May 13, 2008 at 10:57 AM, idc danny <idcdanny(at)yahoo(dot)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...