How Can Keyset Pagination Optimize PostgreSQL Queries with Large Offsets?

Boosting PostgreSQL Pagination Performance on Massive Tables

Retrieving paginated data from large PostgreSQL tables (millions of rows) using OFFSET can severely impact query speed. This article presents a superior optimization strategy: keyset pagination.

Keyset Pagination: A More Efficient Approach

Keyset pagination avoids the performance pitfalls of OFFSET by leveraging the last row's values from the previous page. Here's the query:

<code class="language-sql">SELECT *
FROM   big_table
WHERE  (vote, id) > (vote_x, id_x)
ORDER  BY vote, id
LIMIT  n;</code>

vote_x and id_x represent the last row's vote and id from the preceding page. This allows the database to efficiently skip rows, significantly improving performance compared to OFFSET.

Index Enhancement

For optimal performance, create an index on the columns used in the WHERE clause:

<code class="language-sql">CREATE INDEX vote_order_asc ON big_table (vote, id);</code>

Important Notes:

• The (vote, id) combination must be unique to ensure consistent results.
• The ORDER BY clause's direction must match the index's direction.
• This method isn't suitable for queries with mixed or conflicting ORDER BY directions.

Further Reading:

• SQL Syntax for 'WHERE (col1, col2) ...' (replace https://www.php.cn/link/4643dd49216b67d9c617ceb260e45684 with actual link)
• Pagination Done the PostgreSQL Way by Markus Winand

The above is the detailed content of How Can Keyset Pagination Optimize PostgreSQL Queries with Large Offsets?. For more information, please follow other related articles on the PHP Chinese website!