Обсуждение: BUG #7918: limitation of pagination with LIMIT and OFFSET

The following bug has been logged on the website:

Bug reference:      7918
Logged by:          Adriano Patrick do Nascimento Cunha
Email address:      adrianopatrick@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.

"adrianopatrick@gmail.com" <adrianopatrick@gmail.com> wrote:

> 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