From: Craig James <craig_james@emolecules.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 10:17:04 -0700
Message-ID: <4829CD10.5070304@emolecules.com> (raw)
In-Reply-To: <684043.20585.qm@web35307.mail.mud.yahoo.com>
References: <684043.20585.qm@web35307.mail.mud.yahoo.com>
idc danny 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.
The OFFSET clause is almost always inefficient for anything but very small tables or small offsets. In order for a relational database (not just Postgres) to figure out which row is the 11000000th row, it has to actually retrieve the first 10999999 rows and and discard them. There is no magical way to go directly to the 11-millionth row. Even on a trivial query such as yours with no WHERE clause, the only way to determine which row is the 11 millionths is to scan the previous 10999999.
There are better (faster) ways to achieve this, but it depends on why you are doing this query. That is, do you just want this one block of data, or are you scanning the whole database in 10,000-row blocks?
Craig
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: craig_james@emolecules.com, idcdanny@yahoo.com
Subject: Re: Problem with 11 M records table
In-Reply-To: <4829CD10.5070304@emolecules.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