OFFSET is simple to implement, but the database still needs to read and discard rows before reaching the requested page. Keyset pagination improves this by beginning after the last row from the previous page. Since different databases have various query planner optimizations and index access methods, the only way to ensure correctness is to examine the execution plan.
The usual example, which I take from Vlad Mihalcea's blog post, orders posts by date and uses the identifier as a unique tie-breaker:
ORDER BY created_on DESC, id DESC
The next page should begin after the last row, and the most effective way to specify this in SQL is through row comparison:
WHERE (created_on, id) < (:last_created_on, :last_id)
ORDER BY created_on DESC, id DESC
FETCH FIRST 50 ROWS ONLY
There are two details that make the difference between a direct index seek and an index scan with a filter:
- Use a row comparison when the database can turn it into a composite index boundary.
- Pass values with exactly the right data types.
The second point is easy to miss. A query might look flawless and produce correct results, yet still omit the second index column because it compares a bigint with a numeric. Whether you're a developer or an agent, always verify this by checking the execution plan.
Reproducing it on PostgreSQL
This is the table and index used in the original example:
CREATE TABLE post (
created_on timestamp(6),
id bigint NOT NULL,
title varchar(255),
PRIMARY KEY (id)
);
CREATE INDEX idx_post_created_on_id
ON post (created_on DESC, id DESC);
The complete reproductions are available for PostgreSQL 18 and PostgreSQL 15 which I used in a Linkedin discussion.
The best predicate: one correctly typed row comparison
SQL can compare rows. The SQL standard defines the comparison predicate, including greater than, with row value constructors:
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT id, created_on
FROM post
WHERE ( created_on , id )
< ( timestamp '2019-10-02 21:00:00' , 4951::bigint )
ORDER BY created_on DESC, id DESC
LIMIT 50;
PostgreSQL puts the complete row comparison in the Index Cond:
Limit (actual rows=50.00 loops=1)
Buffers: shared hit=39
-> Index Only Scan using idx_post_created_on_id on post
(actual rows=50.00 loops=1)
Index Cond: (ROW(created_on, id) <
ROW('2019-10-02 21:00:00'::timestamp without time zone,
'4951'::bigint))
Heap Fetches: 50
Index Searches: 1
Buffers: shared hit=39
Planning:
Buffers: shared hit=60 read=4
Planning Time: 1.925 ms
Execution Time: 0.308 ms
This is what I want for pagination. The B-tree navigates to the composite value and reads the next 50 entries in index order. No sort or filter is needed. It seeks directly to the index entry of the first row to fetch, and it reads only what is necessary for the result.
The id is not optional here. Many posts can have the same timestamp. Pagination needs a total and stable order, so the last ordering column must make the key unique.
The exact OR expression is correct, but not as good on PostgreSQL
A row comparison is equivalent, for non-null values, to:
created_on < :last_created_on
OR (
created_on = :last_created_on
AND id < :last_id
)
This query returns the same rows:
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT id, created_on
FROM post
WHERE created_on < timestamp '2019-10-02 21:00:00'
OR (
created_on = timestamp '2019-10-02 21:00:00'
AND id < 4951::bigint
)
ORDER BY created_on DESC, id DESC
LIMIT 50;
But PostgreSQL keeps the expression as a filter, after scanning all rows:
Limit (actual rows=50.00 loops=1)
Buffers: shared hit=39
-> Index Only Scan using idx_post_created_on_id on post
(actual rows=50.00 loops=1)
Filter: ((created_on <
'2019-10-02 21:00:00'::timestamp without time zone)
OR ((created_on =
'2019-10-02 21:00:00'::timestamp without time zone)
AND (id < '4951'::bigint)))
Heap Fetches: 50
Index Searches: 1
Buffers: shared hit=39
Planning:
Buffers: shared hit=3
Planning Time: 0.133 ms
Execution Time: 0.049 ms
This small test does not show a performance problem because the first entries happen to qualify. The important difference is the plan operation: Filter is not Index Cond.
With many rows sharing the cursor timestamp, or with a cursor deep in the index, this form may scan and reject many entries before returning 50 rows. The row comparator gives PostgreSQL the composite boundary directly.
Do not replace it with one of these:
-- Too restrictive
created_on < :created_on AND id < :id
-- Too broad
created_on < :created_on OR id < :id
-- Also wrong: it loses older rows having a larger id
created_on <= :created_on AND id < :id
Lexicographic order needs the equality prefix:
created_on < :created_on
OR (created_on = :created_on AND id < :id)
This is logically equivalent to the row comparison, but database query planners rarely recognize x < y OR x = y as a single bound.
The datatype can silently remove part of the seek
Now I change only one value:
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT id, created_on
FROM post
WHERE ( created_on , id )
< ( timestamp '2019-10-02 21:00:00' , 4951::numeric )
ORDER BY created_on DESC, id DESC -------------
LIMIT 50;
The column is bigint, but the value is numeric. Here is the PostgreSQL plan:
Limit (actual rows=50.00 loops=1)
Buffers: shared hit=39
-> Index Only Scan using idx_post_created_on_id on post
(actual rows=50.00 loops=1)
Index Cond: (created_on <=
'2019-10-02 21:00:00'::timestamp without time zone)
Filter: (ROW(created_on, (id)::numeric) <
ROW('2019-10-02 21:00:00'::timestamp without time zone,
'4951'::numeric))
Heap Fetches: 50
Index Searches: 1
Buffers: shared hit=39
Planning:
Buffers: shared hit=9
Planning Time: 0.288 ms
Execution Time: 0.107 ms
The significant lines are:
Index Cond: (created_on <= ...)
Filter: (ROW(created_on, (id)::numeric) < ...)
The index stores id as bigint, but the comparison casts the indexed column to numeric. The second column is no longer usable as the bigint B-tree boundary.
PostgreSQL still extracts a safe condition from the leading column:
created_on <= :last_created_on
It must be <=, not <, because rows at the same timestamp can qualify when their identifier is lower. PostgreSQL then evaluates the original row expression as a filter to preserve the correct result.
The optimizer source calls this a lossy version of the row comparison. It means that the index condition identifies a superset of the required rows and the complete condition must be rechecked.
The fix is only a cast, but it must be on the value:
WHERE (created_on, id)
< (:last_created_on::timestamp, :last_id::bigint)
Do not cast the indexed column (except if it is indexed with an expression-based index):
-- Avoid this
WHERE (created_on, id::numeric)
< (:last_created_on, :last_id::numeric)
For a column defined as bigint, the application should bind the cursor as int8/bigint, not as numeric or BigDecimal when that changes the PostgreSQL parameter type.
A prepared statement makes the contract explicit:
PREPARE next_page(timestamp, bigint) AS
SELECT id, created_on
FROM post
WHERE (created_on, id) < ($1, $2)
ORDER BY created_on DESC, id DESC
LIMIT 50;
A prepared statement or function is the best way to ensure the execution plan uses the right data type, regardless of what is passed.
A test that exposes wasted index work
The previous fiddle helps you inspect the plan, but its data doesn't magnify the difference. This creates one million rows, with 500,000 rows sharing each timestamp:
DROP TABLE IF EXISTS post;
CREATE UNLOGGED TABLE post (
id bigint PRIMARY KEY,
created_on timestamp NOT NULL,
title text
);
INSERT INTO post
SELECT g,
timestamp '2024-01-01'
- ((g - 1) / 500000) * interval '1 day',
'post ' || g
FROM generate_series(1, 1000000) AS g;
CREATE INDEX post_seek_idx
ON post (created_on DESC, id DESC);
VACUUM (ANALYZE) post;
Run the three alternatives separately:
-- Exact composite boundary
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT id, created_on
FROM post
WHERE (created_on, id)
< (timestamp '2024-01-01', 100::bigint)
ORDER BY created_on DESC, id DESC
LIMIT 50;
-- Only a leading-column boundary because bigint is compared with numeric
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT id, created_on
FROM post
WHERE (created_on, id)
< (timestamp '2024-01-01', 100::numeric)
ORDER BY created_on DESC, id DESC
LIMIT 50;
-- Exact logical expansion, but commonly retained as a filter
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT id, created_on
FROM post
WHERE created_on < timestamp '2024-01-01'
OR (
created_on = timestamp '2024-01-01'
AND id < 100::bigint
)
ORDER BY created_on DESC, id DESC
LIMIT 50;
The first query can position near (2024-01-01,99). The other two may start near the beginning of the 500,000 entries for 2024-01-01 and filter almost all of them.
Always run this with your distribution and parameters. A query is not efficient because it contains Index Scan. Inspect Index Cond, Filter, rows removed, buffers, and the number of entries actually read.
Which databases support an index for row comparison?
The short answer depends on whether “support” means syntax or a direct composite index boundary.
| Database |
Ordered row syntax |
Composite-index behavior for pagination |
| PostgreSQL |
✅ |
Yes , with compatible B-tree operators and matching datatypes |
| YugabyteDB |
✅ |
The original PostgreSQL-style pattern is still not an exact DocDB seek. A new hash-aware form exists |
| MySQL |
✅ |
Yes when the row is the leftmost index prefix. Important limitation when it follows separate key predicates |
| MongoDB |
❌ |
The exact $or can use two index scans combined with SORT_MERGE
|
| Oracle |
❌ |
Use scalar predicates. Typically a leading range plus filter, or bounded UNION ALL branches |
| SQL Server |
❌ |
The exact OR can become multiple ranges in one ordered Index Seek
|
Accepting the syntax is not a sufficient test because the goal of this pagination filter is to read only what is necessary. The execution plan is the evidence. Here are some detailed tests
YugabyteDB: improved, but the old case still needs the workaround
I wrote about this in Efficient pagination in YugabyteDB & PostgreSQL.
The index was:
CREATE UNIQUE INDEX demo1_key_ts_id
ON demo1(key, ts DESC, id DESC);
In YugabyteDB, the first unspecified index column is hash-sharded, so this is effectively:
(key HASH, ts DESC, id DESC)
The elegant PostgreSQL query was:
WHERE key = $1
AND (ts, id) < ($2, $3)
ORDER BY ts DESC, id DESC
LIMIT $4
On YugabyteDB 2.8, the complete historical plan showed why Index Cond was not sufficient evidence:
Limit (actual time=26446.024..26748.760 rows=1000 loops=1)
-> Nested Loop
-> Index Scan using demo1_key_ts_id on demo1
Index Cond:
((key = 1)
AND
(ROW(ts, id) <
ROW('2022-01-01 00:00:01+00'::timestamp with time zone,
'000fffff-ffff-ffff-ffff-ffffffffffff'::uuid)))
Rows Removed by Index Recheck: 998000
Planning Time: 1.159 ms
Execution Time: 26751.767 ms
The storage layer had not started at the complete row value. PostgreSQL rechecked and rejected 998,000 entries.
The workaround was to provide a scalar bound for the first range column:
WHERE key = $1
AND ts <= $2
AND (
ts < $2
OR (ts = $2 AND id < $3)
)
ORDER BY ts DESC, id DESC
LIMIT $4
The historical plan became:
Limit (actual time=18.769..305.666 rows=1000 loops=1)
-> Nested Loop
-> Index Scan using demo1_key_ts_id on demo1
Index Cond:
((key = 1)
AND
(ts <=
'2022-01-01 00:00:01+00'::timestamp with time zone))
Filter:
((ts <
'2022-01-01 00:00:01+00'::timestamp with time zone)
OR
((ts =
'2022-01-01 00:00:01+00'::timestamp with time zone)
AND
(id <
'000fffff-ffff-ffff-ffff-ffffffffffff'::uuid)))
Planning Time: 1.039 ms
Execution Time: 309.985 ms
What about now?
YugabyteDB 2026.1 added a useful row boundary for hash indexes, but it is a different key shape. It starts with the hash code and includes a contiguous key prefix:
CREATE TABLE hc_rc_t1 (
h int,
r1 int,
r2 int,
v text,
PRIMARY KEY (h HASH, r1 ASC, r2 ASC)
);
INSERT INTO hc_rc_t1
SELECT i % 5, i, i * 10, 'val' || i
FROM generate_series(1, 50) AS i;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT *
FROM hc_rc_t1
WHERE (yb_hash_code(h), h, r1, r2)
> (yb_hash_code(1), 1, 10, 100)
LIMIT 5;
The released 2026.1.2 regression plan is:
Limit (actual rows=5 loops=1)
-> Index Scan using hc_rc_t1_pkey on hc_rc_t1
(actual rows=5 loops=1)
Index Cond:
(ROW(yb_hash_code(h), h, r1, r2)
> ROW(4624, 1, 10, 100))
Without the leading hash code:
SELECT *
FROM hc_rc_t1
WHERE (h, r1, r2) > (1, 10, 100)
LIMIT 5;
the released test expects:
Limit (actual rows=5 loops=1)
-> Seq Scan on hc_rc_t1
Filter: (ROW(h, r1, r2) > ROW(1, 10, 100))
Rows Removed by Filter: 2
This does not fix the original portable predicate:
key = :key AND (ts, id) < (:ts, :id)
Issue #11794 remains open, and a July 2026 issue still describes those row-comparison bounds as loose bounds requiring recheck. For the original index and ordering, I would still use the decomposed predicate and verify it with:
EXPLAIN (ANALYZE, DIST, COSTS OFF)
In YugabyteDB, check Storage Index Rows Scanned and Rows Removed by Index Recheck, not only Index Cond.
MySQL: native row comparison, with a prefix caveat
MySQL 8.4 supports the same lexicographic syntax:
CREATE TABLE post (
id bigint NOT NULL PRIMARY KEY,
created_on datetime(6) NOT NULL,
payload varchar(100),
INDEX post_seek_i (created_on DESC, id DESC)
) ENGINE = InnoDB;
EXPLAIN ANALYZE
SELECT id, created_on
FROM post
WHERE (created_on, id)
< ('2026-01-01 12:00:00', 50000)
ORDER BY created_on DESC, id DESC
LIMIT 50;
With the row constructor covering the leftmost index prefix, the desired plan shape is:
Limit: 50 row(s)
-> Index range scan on post using post_seek_i
But MySQL documents an important exception. With:
CREATE INDEX post_tenant_seek_i
ON post (tenant_id, created_on DESC, id DESC);
WHERE tenant_id = ?
AND (created_on, id) < (?, ?)
the row starts at the second index part. MySQL may use only tenant_id. Its documented example reports:
type: ref
key: PRIMARY
key_len: 4
Extra: Using where
Expanding the row comparison:
WHERE tenant_id = ?
AND (
created_on < ?
OR (created_on = ? AND id < ?)
)
allows the documented example to use all three key parts:
type: range
key: PRIMARY
key_len: 12
Extra: Using where
So MySQL supports index range access for row comparison, but do not generalize that to every position in a composite index. Check access_type, used_key_parts, actual rows, and using_filesort.
Reference: MySQL row-constructor optimization.
MongoDB: no tuple syntax, but two index seeks
MongoDB has no equivalent of:
(created_on, id) < (:created_on, :id)
For this index:
db.events.createIndex(
{created_on: -1, _id: -1},
{name: "created_on_-1__id_-1"}
);
use the exact lexicographic expansion:
const T = ISODate("2026-01-01T12:01:00Z");
const I = ObjectId("000000000000000000000004");
const after = {
$or: [
{created_on: {$lt: T}},
{create... (truncated)
by Franck Pachot
Percona Database Performance Blog
Thread Pool in Percona Server and MySQL (Part 1) A new version of Community MySQL Server July release contains two versions 26.7.0 and 9.7.2. It is an important milestone because it finally brings a Thread Pooling feature to the community. The official MySQL Server Thread Pool plugin existed for a long time, but was available … Continued
The post Thread Pool in Percona Server and MySQL (Part 1) appeared first on Percona.
by Bogdan Degtyariov
