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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
