[issue2551364] Improve performance of pagination using key param rather than page/size

New submission from John Rouillard:

https://dev.to/scion01/optimizing-pagination-in-postgresql-offsetlimit-vs-keyset-21dp

Discusses changing pagination to use keyset vs offset/limit (aka page number/page size).

The idea is to replace:

  SELECT * FROM table ORDER BY id ASC LIMIT 100 OFFSET 100;

with:

  SELECT * from table where id > x ORDER BY id ASC LIMIT 100

where X was the max id in the prior set of 100 items.

For larger offsets, the database has to retrieve and order all rows up to OFFSET
and then discard them. In the second case, the index prevents retrieving
rows that would be in the OFFSET region. Only rows with an id larger than X
would be processed. This may come in play only for large offsets (high page number)
but is worth investigating as it could improve response times for X.index.html pages.

----------
components: Database
messages: 8141
nosy: rouilj
severity: normal
status: new
title: Improve performance of pagination using key param rather than page/size
type: behavior

_________________________________________________
Roundup tracker <[email protected]>
<https://issues.roundup-tracker.org/issue2551364>
_________________________________________________