DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=fwpHuWwFD1CXldUCAiZQFDFXRTr+2DUtGCP3xjbt8IOb9LAldBfQZPeGQy2m1QOmI0vFV1pYW6kx+HipQwfx/ub0wGckr4wYCFClmwiQ5/OZcweUV7HfLcqLOXvXbrZsFAA4KMBiZst3/DiMlwZ2H/JYo8BSsVa8/c+6fr6Ah38=
Message-ID: <dcc563d10805131102y6a840088hd96c32a507b9bfb3@mail.gmail.com>
Date: Tue, 13 May 2008 12:02:20 -0600
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: "idc danny" <idcdanny@yahoo.com>
Subject: Re: Problem with 11 M records table
Cc: pgsql-performance@postgresql.org
In-Reply-To: <684043.20585.qm@web35307.mail.mud.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <684043.20585.qm@web35307.mail.mud.yahoo.com>

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...