Hi ,
Set this parameter in psotgresql.conf set enable_seqscan=off;
And try:
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Scott
Marlowe
Sent: Tuesday, May 13, 2008 11:32 PM
To: idc danny
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Problem with 11 M records table
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...
--
Sent via pgsql-performance mailing list
(pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance