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
relationaldatabase (not just Postgres) to figure out which row is the 11000000th row, it has to actually retrieve the
first10999999 rows and and discard them. There is no magical way to go directly to the 11-millionth row. Even on a
trivialquery such as yours with no WHERE clause, the only way to determine which row is the 11 millionths is to scan
theprevious 10999999.
There are better (faster) ways to achieve this, but it depends on why you are doing this query. That is, do you just
wantthis one block of data, or are you scanning the whole database in 10,000-row blocks?
Craig