Re: Problem with 11 M records table

[Craig James <craig_james(at)emolecules(dot)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