BUG #7918: limitation of pagination with LIMIT and OFFSET

[a***@gmail.com]

The following bug has been logged on the website:

Bug reference: 7918
Logged by: Adriano Patrick do Nascimento Cunha
Email address: ***@gmail.com
PostgreSQL version: 8.4.7
Operating system: Windows/Linux
Description:

Hello guys, today I need to process a query, the query returned as somewhere
around 20 million records, I thought to do with LIMIT and OFFSET where the
limit is fixed for 5000 records and will incrementing the OFFSET, but when
reached OFFSET 400 000 consultation was very slow, taking almost the time of
the query without LIMIT and OFFSET. I noticed that when the OFFSET is less
than 400 000 the query execution plan is, but when this value exceeds the
execution plan for the query changes and the query is too long.

[Kevin Grittner]

I need to process a query, the query returned as somewhere around
20 million records, I thought to do with LIMIT and OFFSET where
the limit is fixed for 5000 records and will incrementing the
OFFSET, but when reached OFFSET 400 000 consultation was very
slow, taking almost the time of the query without LIMIT and
OFFSET.

You should probably use a cursor instead of LIMIT and OFFSET.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company