From: Scott Marlowe <scott.marlowe@gmail.com>
To: idc danny <idcdanny@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Problem with 11 M records table
Date: Tue, 13 May 2008 12:02:20 -0600
Message-ID: <dcc563d10805131102y6a840088hd96c32a507b9bfb3@mail.gmail.com> (raw)
In-Reply-To: <684043.20585.qm@web35307.mail.mud.yahoo.com>
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...
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scott.marlowe@gmail.com, idcdanny@yahoo.com
Subject: Re: Problem with 11 M records table
In-Reply-To: <dcc563d10805131102y6a840088hd96c32a507b9bfb3@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox