![\"\"](\"https:\/\/www.couchbase.com\/wp-content\/uploads\/sites\/5\/2026\/05\/arrows2-300x128-1.png\")
<\/p>\n\n\n\n<\/p>\n\n\n\n
Read the pagination background in my previous article: <\/span>https:\/\/www.couchbase.com\/blog\/optimizing-database-pagination-using-couchbase-n1ql\/<\/span><\/a><\/p>\n\n\n\n Pagination is the task of dividing the potential result into pages and retrieving the required pages, one by one on demand. \u00a0Using OFFSET and LIMIT is the easy way to write pagination into database queries. Together, OFFSET and LIMIT, make the pagination clause of the SELECT statement. Pagination is a common application job and its implementation has a major impact on the customer experience. Let\u2019s look at the issues and solutions with Couchbase N1QL in detail.<\/span><\/p>\n\n\n\n Markus Winand from <\/span>https:\/\/use-the-index-luke.com\/<\/span><\/a> argues it may not be the most efficient. He also suggests, when possible, using keyset pagination approach instead of OFFSET pagination. For this article, I\u2019m going to use the example queries modeled on his article, <\/span>https:\/\/use-the-index-luke.com\/no-offset<\/span><\/a> to show what OFFSET optimizations we\u2019ve done, when and how to exploit the keyset pagination in N1QL. <\/span><\/p>\n\n\n\n Since Couchbase ships with a travel-sample dataset, we\u2019ll use it to write our pagination queries.<\/span><\/p>\n\n\n\n [crayon theme=”github” scroll=”true” whitespace-before=”1″ whitespace-after=”1″ lang=”plsql” decode=”true”]SELECT * CREATE INDEX ixtopic ON `travel-sample`(type); For this query, using the index ixtopic, the query engine executes in a simple way. The query engine gets all the qualified keys from the index, then gets all the documents, sorts based on the ORDER BY clause and then drops the OFFSET number of documents (in this case zero) and projects LIMIT (in this case 10) number of documents.<\/span><\/p>\n\n\n\n Here\u2019s the plan showing the index and the spans.<\/span><\/p>\n\n\n\n \u00a0<\/p>\n\n\n [crayon theme=”github” whitespace-before=”1″ whitespace-after=”1″ lang=”js” decode=”true”] { [\/crayon]<\/p>\n\n\n\n N1QL chooses the index ixtype and pushes the filter (type = \u201chotel\u201d) to the index scan. To implement the ORDER BY clause, it fetches all the documents. In the sort phase, we recognize LIMIT 10 clause and make the sort efficient by keeping only the top 10 items.<\/span><\/p>\n\n\n [crayon theme=”github” whitespace-before=”1″ whitespace-after=”1″ lang=”js” decode=”true”] { Let\u2019s look at the index scan operator efficiency with this index:<\/span><\/p>\n\n\n [crayon theme=”github” whitespace-before=”1″ whitespace-after=”1″ lang=”js” decode=”true”] “#operator”: “IndexScan2”, There were 917 documentkeys returned by the indexer. The query engine fetched 917 documents. Then the sort (order) operator sorted them and returned the 10 items.<\/span><\/p>\n\n\n\n 2. GET THE SECOND PAGE<\/span><\/p>\n\n\n [crayon whitespace-before=”1″ whitespace-after=”1″ lang=”plsql” decode=”true”]SELECT * In this case, everything is same as the query1 except:<\/span><\/p>\n\n\n\n [crayon theme=”github” whitespace-before=”1″ whitespace-after=”1″ lang=”js” decode=”true”] “#operator”: “Order”, As the OFFSET increases, the number of documents scanned by the sort will increase as well, consuming more memory and CPU.<\/span><\/p>\n\n\n\n [crayon theme=”github” whitespace-before=”1″ whitespace-after=”1″ lang=”plsql” decode=”true”]CREATE INDEX ixtypectcy ON `travel-sample`(type, country, city); It uses the right index and creates the right filters (spans) for the index scan.Explain includes the following.<\/span><\/p>\n\n\n [crayon theme=”github” whitespace-before=”1″ whitespace-after=”1″ lang=”js” decode=”true”] { Let\u2019s look at the index scan operator efficiency with this index:<\/span><\/p>\n\n\n [crayon theme=”github” whitespace-before=”1″ whitespace-after=”1″ lang=”js” decode=”true”] “#operator”: “IndexScan2”, \u00a0<\/p>\n\n\n\n Only the required 10 document keys are returned from the index scan! \u00a0The N1QL optimizer evaluates both the WHERE clause and the pagination (OFFSET, LIMIT) clause. The query optimizer decides to pushdown the pagination clause to the indexer based on the order by clause of the query and the index key order. \u00a0<\/span><\/p>\n\n\n\n In this case, query predicate is: type = \u2018hotel\u2019<\/span><\/p>\n\n\n\n Order by clause is: ORDER BY country, city<\/span><\/p>\n\n\n\n Index key order is: (type, country, city)<\/span><\/p>\n\n\n\n Just by simple comparison, order by clause is not exactly the same as index key order. However, the leading key of the index (type) has an equality predicate (type = \u2018hotel\u2019). Therefore, the optimizer knows the projected document keys will be in the sorted order of (country and city).<\/span><\/p>\n\n\n\n **and the ORDER BY clause to see if it can pushdown the pagination parameters to the index scan. \u00a0In this case, there is a perfect match.<\/span><\/p>\n\n\n\n The optimizer pushes both OFFSET and LIMIT in pagination to index scan and the index scan returns only the 10 document keys required no matter the OFFSET! Therefore, fetch only needs to fetch the 10 documents. Indexer does have to go through the index scan to evaluate the keys.<\/span><\/p>\n\n\n\n This query executed in about 6.81 milliseconds in my environment. Let\u2019s paginate to subsequent pages to see the performance:<\/span><\/p>\n\n\n\n If you want to paginate in the descending order, change the query and index to the following. The collation of the key in the index should match the collation in the query. In this case, both country and city are in DESCending order.<\/span><\/p>\n\n\n [crayon theme=”github” whitespace-before=”1″ whitespace-after=”1″ lang=”plsql” decode=”true”]DROP INDEX `travel-sample`.ixtypectcy;<\/p>\n CREATE INDEX ixtypectcy ON `travel-sample`(type, country DESC, city DESC);<\/p>\n SELECT country, city [crayon theme=”github” whitespace-before=”1″ whitespace-after=”1″ lang=”js” decode=”true”] { [\/crayon]<\/p>\n0<\/p>\n\n\n\n \u00a0<\/p>\n\n\n\n Let\u2019s now look at a query that\u2019s not covered by the index. The query we saw earlier, we decide to retrieve the name of the hotel along with country and city. The name isn\u2019t in the index.<\/span><\/p>\n\n\n\n \u00a0<\/p>\n\n\n\n [crayon theme=”github” whitespace-before=”1″ whitespace-after=”1″ lang=”js” decode=”true”] { [\/crayon]<\/p>\n1<\/p>\n\n\n\n In this case, again, the index has the data to cover the WHERE clause and the ORDER BY clause. The index scan will apply the predicate. The index order matches query order (note: there is only equality predicate on leading key. So, country and city are in order automatically). Index skips of the OFFSET number of qualified keys and returns the number of keys specified by the LIMIT clause. The engine retrieves the documents in order to maintain the order specified by ORDER BY clause.<\/span><\/p>\n\n\n\n All these little optimizations together ensure the query performance is consistent whether OFFSET is 10, 100 or 1000. The only overhead between higher OFFSET is the index scan skipping over the additional items. Index key evaluation and skipping is significantly faster than getting all the documents and sorting.<\/span><\/p>\n\n\n\n [crayon theme=”github” whitespace-before=”1″ whitespace-after=”1″ lang=”js” decode=”true”] { [\/crayon]<\/p>\n2<\/p>\n\n\n\n Let\u2019s consider a longer running query with a potential of returning up to 24K documents.<\/span><\/p>\n\n\n\n create index idxtypeaiaid ON `travel-sample`(type, airline, airlineid) <\/span><\/p>\n\n\n\n Even when the pagination query predicates and order by is completely covered by the index, there is a limited number of cases where the pagination LIMIT and OFFSET can be pushed to the index scan in order to make this very efficient.<\/span><\/p>\n\n\n\n Here are general rules and requirements for pushing down OFFSET and LIMIT to the indexer. \u00a0Note these rules are not exhaustive and complete but give you a sense of when this optimization is done.<\/span><\/p>\n\n\n\n Case (a) qualifies, and (b) does not.<\/span><\/p>\n\n\n\n The case (2) is a perfect match of the order.<\/span><\/p>\n\n\n\n The case (3) doesn\u2019t match perfectly, but the leading key has an equality filter. So, we know the results from the scan will be in the order of (b, c). \u00a0Hence the pagination pushdown to the index is possible.<\/span><\/p>\n\n\n\n For the case (4), the leading key has a range predicate (a > 3) and therefore, t pagination pushdown is not possible.<\/span><\/p>\n\n\n\n For the case (5), there is a perfect match of the ORDER BY clause to the index keys. Therefore, we can push down the pagination to the indexer, even if there is range predicate on the leading key.<\/span><\/p>\n\n\n\n For the case (f), we can exploit the index order, but cannot push down the OFFSET and LIMIT because the complete predicate cannot be evaluated by the index scan. Since we get the results in the required order, after the OFFSET and LIMIT number of documents are projected, we terminate the index scan.<\/span><\/p>\n\n\n\n For simplicity, I\u2019ve used one leading key. The statements are generalized and applicable to multiple leading keys.<\/span><\/p>\n\n\n\n Obviously, this optimizer logic is complex! See additional information in the appendix on the requirements.<\/span><\/p>\n\n\n\n OFFSET overhead:<\/b><\/p>\n\n\n\n The initial travel-sample has about 900 documents with (type = \u201chotel\u201d). For experimentation, I simply increase the amount of data by inserting the same data again few times using the following query:<\/span><\/p>\n\n\n\n [crayon theme=”github” whitespace-before=”1″ whitespace-after=”1″ lang=”js” decode=”true”] { [\/crayon]<\/p>\n3<\/p>\n\n\n\n Now, let\u2019s try this query:<\/span><\/p>\n\n\n\n [crayon theme=”github” whitespace-before=”1″ whitespace-after=”1″ lang=”js” decode=”true”] { [\/crayon]<\/p>\n4<\/p>\n\n\n\n\n
\nFROM `travel-sample`
\nWHERE type = \u2018hotel\u2019
\nORDER BY country, city
\nOFFSET 0
\nLIMIT 10;<\/p>\n
\n[\/crayon]<\/p>\n\n\n\n![\"\"](\"https:\/\/www.couchbase.com\/wp-content\/uploads\/sites\/5\/2026\/05\/Screen-Shot-2018-01-09-at-10.34.54-AM-300x169-1.png\")
<\/p>\n\n\n\n
\n “index”: “ixtype”,
\n “index_id”: “8630f5f7e05ee113”,
\n “index_projection”: {
\n “primary_key”: true
\n },
\n “keyspace”: “travel-sample”,
\n “namespace”: “default”,
\n “spans”: [
\n {
\n “exact”: true,
\n “range”: [
\n {
\n “high”: “”hotel””,
\n “inclusion”: 3,
\n “low”: “”hotel””
\n }<\/p>\n
\n “#operator”: “Order”,
\n “limit”: “10”,
\n “sort_terms”: [
\n {
\n “expr”: “(`travel-sample`.`country`)”
\n },
\n {
\n “expr”: “(`travel-sample`.`city`)”
\n }
\n ]
\n },
\n[\/crayon]<\/p>\n\n\n\n
\n “#stats”: {
\n “#itemsOut”: 917,
\n “#phaseSwitches”: 3671,
\n “execTime”: “2.646892ms”,
\n “kernTime”: “31.095431ms”,
\n “servTime”: “19.781593ms”
\n },
\n\u2026
\n “#operator”: “Fetch”,
\n “#stats”: {
\n “#itemsIn”: 917,
\n “#itemsOut”: 917,
\n “#phaseSwitches”: 3787,
\n “execTime”: “3.43324ms”,
\n “kernTime”: “20.847541ms”,
\n “servTime”: “69.875698ms”
\n },
\n\u2026
\n “#operator”: “Order”,
\n “#stats”: {
\n “#itemsIn”: 917,
\n “#itemsOut”: 10,
\n “#phaseSwitches”: 1849,
\n “execTime”: “6.519061ms”,
\n “kernTime”: “88.307572ms”
\n },[\/crayon]<\/p>\n\n\n\n
\nFROM `travel-sample`
\nWHERE type = \u2018hotel\u2019
\nORDER BY country, city
\nOFFSET 10
\nLIMIT 10;
\n[\/crayon]<\/p>\n\n\n\n\n
\n “#stats”: {
\n “#itemsIn”: 917,
\n “#itemsOut”: 20,
\n “#phaseSwitches”: 1859,
\n “execTime”: “6.485904ms”,
\n “kernTime”: “65.92484ms”
\n },
\n {
\n “#operator”: “Offset”,
\n “#stats”: {
\n “#itemsOut”: 10,
\n “#phaseSwitches”: 32,
\n “execTime”: “5.071503ms”,
\n “kernTime”: “701ns”,
\n “state”: “running”
\n },
\n “expr”: “10”,
\n “#time_normal”: “00:00.0050”,
\n “#time_absolute”: 0.005071503
\n },
\n[\/crayon]<\/p>\n\n\n\n\n
\nLet\u2019s execute our recent query again:
\nSELECT *
\nFROM `travel-sample`
\nWHERE type = \u2018hotel\u2019
\nORDER BY country, city
\nOFFSET 10
\nLIMIT 10;
\n[\/crayon]<\/p>\n\n\n\n![\"\"](\"https:\/\/www.couchbase.com\/wp-content\/uploads\/sites\/5\/2026\/05\/Screen-Shot-2018-01-09-at-2.38.36-PM-300x179-1.png\")
<\/p>\n\n\n\n
\n “#operator”: “IndexScan2”,
\n “index”: “ixtypectcy”,
\n “index_id”: “2a2ed6573354e21”,
\n “index_projection”: {
\n “primary_key”: true
\n },
\n “keyspace”: “travel-sample”,
\n “limit”: “10”,
\n “namespace”: “default”,
\n “offset”: “10”,
\n “spans”: [
\n {
\n “exact”: true,
\n “range”: [
\n {
\n “high”: “”hotel””,
\n “inclusion”: 3,
\n “low”: “”hotel””
\n }
\n ]
\n }
\n ],
\n[\/crayon]<\/p>\n\n\n\n
\n “#stats”: {
\n “#itemsOut”: 10,
\n “#phaseSwitches”: 43,
\n “execTime”: “41.786\u00b5s”,
\n “kernTime”: “11.15\u00b5s”,
\n “servTime”: “855.759\u00b5s”
\n },
\n[\/crayon]<\/p>\n\n\n\n\n\n
\n OFFSET<\/span><\/td>\n LIMIT<\/span><\/td>\n Response Time<\/span><\/td>\n<\/tr>\n \n 10<\/span><\/td>\n 10<\/span><\/td>\n 6.81 ms<\/span><\/td>\n<\/tr>\n \n 20<\/span><\/td>\n 10<\/span><\/td>\n 7.17 ms<\/span><\/td>\n<\/tr>\n \n 100<\/span><\/td>\n 10<\/span><\/td>\n 7.02 ms<\/span><\/td>\n<\/tr>\n \n 400<\/span><\/td>\n 10<\/span><\/td>\n 9.54 ms<\/span><\/td>\n<\/tr>\n \n 800<\/span><\/td>\n 10<\/span><\/td>\n 9.08 ms<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table><\/figure>\n\n\n\n
\nFROM `travel-sample`
\nWHERE type = “hotel”
\nORDER BY country DESC, city DESC
\nOFFSET 10
\nLIMIT 10;[\/crayon]<\/p>\n\n\n\n
\n “index”: “ixtype”,
\n “index_id”: “8630f5f7e05ee113”,
\n “index_projection”: {
\n “primary_key”: true
\n },
\n “keyspace”: “travel-sample”,
\n “namespace”: “default”,
\n “spans”: [
\n {
\n “exact”: true,
\n “range”: [
\n {
\n “high”: “”hotel””,
\n “inclusion”: 3,
\n “low”: “”hotel””
\n }<\/p>\n
\n “index”: “ixtype”,
\n “index_id”: “8630f5f7e05ee113”,
\n “index_projection”: {
\n “primary_key”: true
\n },
\n “keyspace”: “travel-sample”,
\n “namespace”: “default”,
\n “spans”: [
\n {
\n “exact”: true,
\n “range”: [
\n {
\n “high”: “”hotel””,
\n “inclusion”: 3,
\n “low”: “”hotel””
\n }<\/p>\n
\n “index”: “ixtype”,
\n “index_id”: “8630f5f7e05ee113”,
\n “index_projection”: {
\n “primary_key”: true
\n },
\n “keyspace”: “travel-sample”,
\n “namespace”: “default”,
\n “spans”: [
\n {
\n “exact”: true,
\n “range”: [
\n {
\n “high”: “”hotel””,
\n “inclusion”: 3,
\n “low”: “”hotel””
\n }<\/p>\n\n
\n
\n
\n
\n
\n
\n
\n “index”: “ixtype”,
\n “index_id”: “8630f5f7e05ee113”,
\n “index_projection”: {
\n “primary_key”: true
\n },
\n “keyspace”: “travel-sample”,
\n “namespace”: “default”,
\n “spans”: [
\n {
\n “exact”: true,
\n “range”: [
\n {
\n “high”: “”hotel””,
\n “inclusion”: 3,
\n “low”: “”hotel””
\n }<\/p>\n
\n “index”: “ixtype”,
\n “index_id”: “8630f5f7e05ee113”,
\n “index_projection”: {
\n “primary_key”: true
\n },
\n “keyspace”: “travel-sample”,
\n “namespace”: “default”,
\n “spans”: [
\n {
\n “exact”: true,
\n “range”: [
\n {
\n “high”: “”hotel””,
\n “inclusion”: 3,
\n “low”: “”hotel””
\n }<\/p>\n