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}},
{created_on: T, _id: {$lt: I}}
]
};
db.events.find(after)
.sort({created_on: -1, _id: -1})
.limit(50);
Here is a complete small reproduction:
db.events.drop();
db.events.insertMany([
{_id:ObjectId("000000000000000000000006"), created_on:ISODate("2026-01-01T12:02:00Z"), n:"A"},
{_id:ObjectId("000000000000000000000005"), created_on:ISODate("2026-01-01T12:01:00Z"), n:"B"},
{_id:ObjectId("000000000000000000000004"), created_on:ISODate("2026-01-01T12:01:00Z"), n:"C"},
{_id:ObjectId("000000000000000000000003"), created_on:ISODate("2026-01-01T12:01:00Z"), n:"D"},
{_id:ObjectId("000000000000000000000002"), created_on:ISODate("2026-01-01T12:00:00Z"), n:"E"},
{_id:ObjectId("000000000000000000000001"), created_on:ISODate("2026-01-01T11:59:00Z"), n:"F"}
]);
db.events.createIndex(
{created_on:-1, _id:-1},
{name:"created_on_-1__id_-1"}
);
const T = ISODate("2026-01-01T12:01:00Z");
const I = ObjectId("000000000000000000000004");
const q = {
$or: [
{created_on: {$lt:T}},
{created_on:T, _id:{$lt:I}}
]
};
db.events.find(q, {n:1, created_on:1})
.sort({created_on:-1, _id:-1})
.limit(3)
.hint("created_on_-1__id_-1")
.explain("executionStats");
MongoDB can plan this as:
LIMIT
└─ FETCH
└─ SORT_MERGE
├─ IXSCAN created_on_-1__id_-1
└─ IXSCAN created_on_-1__id_-1
SORT_MERGE merges two already ordered index streams. It is not a blocking in-memory SORT. MongoDB 9.0 regression tests require this shape for sortable $or queries.
Explain output varies between the classic and slot-based engines. Inspect the complete result for:
{
executionStats: {
nReturned: 3,
totalKeysExamined: 3,
totalDocsExamined: 3,
executionStages: {
stage: "LIMIT",
inputStage: {
stage: "FETCH",
inputStage: {
stage: "SORT_MERGE",
inputStages: [
{
stage: "IXSCAN",
indexName: "created_on_-1__id_-1"
},
{
stage: "IXSCAN",
indexName: "created_on_-1__id_-1"
}
]
}
}
}
}
}
This is the plan structure and the decisive counters, not a captured complete JSON plan from a specific server. Run the reproduction on the target MongoDB version because the optimizer can also choose one broader index scan with a residual $or.
An array expression is not a replacement:
{$expr: {$lt: [["$created_on", "$_id"], [T, I]]}}
It compares a computed array and does not provide compound index bounds. Materializing an array field is also different because it becomes a multikey index.
Oracle: no ordered row comparison
Oracle didn't implement the full SQL standard. it supports expression lists for equality and IN, but not:
(created_on, id) < (:created_on, :id)
Use:
CREATE TABLE post (
id number NOT NULL,
created_on timestamp NOT NULL,
payload varchar2(100),
CONSTRAINT post_pk PRIMARY KEY (id)
);
CREATE INDEX post_seek_i ON post (created_on, id);
SELECT id, created_on, payload
FROM post
WHERE created_on <= :last_created_on
AND (
created_on < :last_created_on
OR (
created_on = :last_created_on
AND id < :last_id
)
)
ORDER BY created_on DESC, id DESC
FETCH FIRST 50 ROWS ONLY;
The redundant created_on <= :last_created_on exposes a simple leading range to the optimizer. The desired shape is:
WINDOW NOSORT STOPKEY
TABLE ACCESS BY INDEX ROWID
INDEX RANGE SCAN DESCENDING POST_SEEK_I
Predicate Information:
access("CREATED_ON" <= :LAST_CREATED_ON)
filter("CREATED_ON" < :LAST_CREATED_ON
OR ("CREATED_ON" = :LAST_CREATED_ON
AND "ID" < :LAST_ID))
That is a leading-column range followed by the exact filter, not one native two-column seek.
If the filter reads too many entries, use two disjoint bounded branches:
WITH same_time AS (
SELECT id, created_on, payload
FROM post
WHERE created_on = :last_created_on
AND id < :last_id
ORDER BY created_on DESC, id DESC
FETCH FIRST 50 ROWS ONLY
),
older AS (
SELECT id, created_on, payload
FROM post
WHERE created_on < :last_created_on
ORDER BY created_on DESC, id DESC
FETCH FIRST 50 ROWS ONLY
)
SELECT *
FROM (
SELECT * FROM same_time
UNION ALL
SELECT * FROM older
)
ORDER BY created_on DESC, id DESC
FETCH FIRST 50 ROWS ONLY;
Each branch can have its own index range. Fetching at most one page from each disjoint branch is sufficient to produce one global page.
Capture actual operations and predicates with:
SELECT /*+ GATHER_PLAN_STATISTICS */
...
SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
NULL, NULL, 'ALLSTATS LAST +PREDICATE +OUTLINE'
));
The displayed Oracle plan above is the target shape, not a plan captured for this draft.
SQL Server: no tuple syntax, but multiple ranges in one seek
T-SQL does not support an ordered row constructor. Use the exact disjunction:
CREATE INDEX IX_post_seek
ON dbo.Post (created_on DESC, id DESC)
INCLUDE (payload);
SELECT TOP (@page_size)
id, created_on, payload
FROM dbo.Post
WHERE created_on < @last_created_on
OR (
created_on = @last_created_on
AND id < @last_id
)
ORDER BY created_on DESC, id DESC;
SQL Server can compile the two disjoint ranges into one ordered Index Seek:
Top
Index Seek
Object: IX_post_seek
Ordered: True
Scan Direction: FORWARD
Seek Predicates:
1. created_on < @last_created_on
2. created_on = @last_created_on
AND id < @last_id
This is not native tuple syntax, but it has the required physical behavior. In the XML or graphical plan, confirm that both branches are under Seek Predicates, not only under a residual Predicate.
For more columns, continue the equality prefixes:
WHERE (a = @a AND b = @b AND c = @c AND d > @d)
OR (a = @a AND b = @b AND c > @c)
OR (a = @a AND b > @b)
OR a > @a
ORDER BY a, b, c, d;
The plan shape above is documented from reproducible SQL Server investigations, but it was not captured on a SQL Server instance for this draft.
Direction, NULLs, and concurrent changes
For a homogeneous order, the comparator reverses with the direction:
ORDER BY a ASC, b ASC -> (a,b) > (:a,:b)
ORDER BY a DESC, b DESC -> (a,b) < (:a,:b)
For mixed directions, one tuple operator does not express the order:
-- ORDER BY created_on DESC, id ASC
WHERE created_on < :created_on
OR (
created_on = :created_on
AND id > :id
)
The index must use the same mixed directions when the database supports them:
(created_on DESC, id ASC)
Pagination columns should preferably be NOT NULL. A comparison with NULL is not true, and databases may order nulls differently. If nulls are part of the application order, encode that order explicitly and index the same expression.
Finally, keyset pagination does not freeze the dataset. Separate requests under READ COMMITTED can observe inserts, deletes, or updates to ordering columns between pages. Decide whether this is acceptable for a live feed or whether all pages must use one consistent snapshot.
The practical rule
For PostgreSQL, start with:
WHERE (created_on, id)
< (:last_created_on::timestamp, :last_id::bigint)
ORDER BY created_on DESC, id DESC
LIMIT :page_size
Then read the plan:
- the complete row value should be in
Index Cond - there should be no residual cursor
Filter - the index should provide the order
- the number of entries read should be close to the pagination size
For other databases, preserve the same lexicographic semantics but use the form their optimizer can seek:
- PostgreSQL: correctly typed row comparison
- YugabyteDB: verify storage pushdown, the original hash-plus-range case still needs the scalar range workaround
- MySQL: row comparison when it covers the leftmost index prefix, otherwise expand and inspect all used key parts
- MongoDB: two
$orbranches andSORT_MERGE - Oracle: leading range plus filter, or two bounded branches
- SQL Server: expanded predicate compiled into multiple seek ranges.
The SQL text is only the request. The execution plan tells you where pagination really starts.
Does Hibernate generate the best for each database?
Hibernate ORM does not use the dialect capability flags to choose the predicate shape for keyset pagination. For ORDER BY created_on DESC, id DESC it generates created_on < ? OR (created_on = ? AND id < ?), i.e. exactly the "exact OR expression" shown above, on every database, including PostgreSQL where the row comparison (created_on, id) < (?, ?) would yield an Index Cond composite boundary.
Hibernate does contain dialect-aware machinery for row comparisons, true by default and overridden to false by Oracle, SQL Server, DB2, Sybase, HSQL and others, but it only applies to tuple/subquery comparisons written explicitly in HQL, not to keyset pagination.
When a dialect must emulate a tuple comparison, the generation depends on an indexOptimized flag:
- the "Normal" form produces the top-level OR (a > 1 or a = 1 and b > 2), which defeats index seeks as we have seen above
- the "Optimized" form, selected by the dialects that lack row-value syntax (Oracle, SQL Server, DB2, Sybase, etc.), emits a >= 1 and not (a = 1 and b <= 2) — a top-level AND with a broadened leading bound that the optimizer can turn into an index range and then recheck, the same "leading range plus filter" that we have seen above.
However, the key set pagination doesn't use this and then doenst have this optimization
So on PostgreSQL and MySQL 8.0.30+, Hibernate's output is correct but uses the weaker filterable form instead of a composite index boundary. On SQL Server the expansion can still be compiled into multiple seek ranges, and on Oracle it may produce the scan-plus-filter plan unless you write the bound predicate yourself.
In short, Hibernate guarantees correct results everywhere, but guarantees the optimal plan nowhere — the same 'verify with EXPLAIN' rule applies to ORM-generated SQL as to hand-written SQL.
References
- Vlad Mihalcea: SQL Seek Method or Keyset Pagination
- PostgreSQL row-constructor comparison
- PostgreSQL indexes and
ORDER BY - PostgreSQL optimizer implementation for row comparisons
- YugabyteDB issue #11794
- YugabyteDB 2026.1.2 release notes
- MySQL row-constructor optimization
- MongoDB
$orand indexes - Oracle comparison conditions
- SQL Server comparison operators
Top comments (0)