Pagination Push down offset and limit in index

SQL++
1

I have this query and this index who covered it:

SELECT meta().id FROM MYBUCKET
WHERE type = “story”
AND render IN [ “STORY”, “IMAGE”, “LIVE” ]
AND excludeQuery = false
AND publication < 1541695651248
AND endPublicationInstant IS NOT VALUED OR endPublicationInstant > 1541695651248 )
AND category= “network”
order by publicationInstant DESC
OFFSET 172875 LIMIT 25

CREATE INDEX idx_emd_publicationInstant_endPublicationInstant_id ON MYBUCKET((publicationInstant) DESC,(endPublicationInstant)) WHERE (((((type) = “story”) and (category = “network”)) and ((render) in [“STORY”, “IMAGE”, “LIVE”])) and ((excludeQuery) = false))

My first pages are super fast, <20ms each, but whe offset increase, my times too getting times over seconds. I need to push down my offset limit to index, but I don´t understand what I am doing wrong. Please help.

This is also my explain:
{
“plan”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “IndexScan3”,
“covers”: [
“cover ((MYBUCKET.publicationInstant))”,
“cover ((MYBUCKET.endPublicationInstant))”,
“cover ((meta(MYBUCKET).id))”
],
“filter_covers”: {
“cover ((MYBUCKET.excludeQuery))”: false,
“cover (((MYBUCKET.render) in [“STORY”, “IMAGE”,“LIVE”]))”: true,
“cover ((MYBUCKET.type))”: “story”,
“cover ((MYBUCKET.category))”: “network”
},
“index”: “idx_emd_publicationInstant_endPublicationInstant_id”,
“index_id”: “25acb7cd7fbe67dc”,
“index_projection”: {
“entry_keys”: [
0,
1,
2
]
},
“keyspace”: “MYBUCKET”,
“namespace”: “default”,
“spans”: [
{
“exact”: true,
“range”: [
{
“high”: “1541695651248”,
“inclusion”: 0,
“low”: “null”
}
]
},
{
“exact”: true,
“range”: [
{
“high”: “1541695651248”,
“inclusion”: 0,
“low”: “null”
},
{
“inclusion”: 0,
“low”: “1541695651248”
}
]
}
],
“using”: “gsi”
},
{
“#operator”: “Parallel”,
“~child”: {
“#operator”: “Sequence”,
“~children”: [
{
“#operator”: “Filter”,
“condition”: “((((((cover (((MYBUCKET.main).type)) = “story”) and cover (((MYBUCKET.render) in [“STORY”, “IMAGE”, “LIVE”,”]))) and (cover ((MYBUCKET…excludeQuery)) = false)) and (cover ((MYBUCKET.publicationInstant)) < 1541695651248)) and ((cover ((((MYBUCKET.main).metadata).endPublicationInstant)) is not valued) or (1541695651248 < cover ((((MYBUCKET.endPublicationInstant))) and (cover ((MYBUCKET.category)) = “network”))"
},
{
“#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: “cover ((meta(CONTENT).id))”
}
]
},
{
“#operator”: “FinalProject”
}
]
}
}
]
},
{
“#operator”: “Offset”,
“expr”: “172875”
},
{
“#operator”: “Limit”,
“expr”: “25”
}
]
},
“text”: “SELECT meta().id FROM MYBUCKET\r\nWHERE type = “story”\r\nAND render IN [ “STORY”, “IMAGE”, “LIVE”]\r\nAND excludeQuery = false\r\nAND publicationInstant < 1541695651248\r\nAND (endPublicationInstant IS NOT VALUED OR endPublicationInstant > 1541695651248 )\r\nAND category= “network”\r\nOFFSET 172875 LIMIT 25”
}

2

Try the following query and index combinations.

CREATE INDEX ix1 ON MYBUCKET(publicationInstant DESC,IFMISSINGORNULL(endPublicationInstant,""))
WHERE type = "story" AND category = "network" AND render IN ["STORY", "IMAGE", "LIVE"] AND excludeQuery = false;

SELECT RAW meta().id
FROM MYBUCKET
WHERE type = "story"
AND render IN [ "STORY", "IMAGE", "LIVE" ]
AND excludeQuery = false
AND category= "network"
AND publicationInstant < 1541695651248
AND IFMISSINGORNULL(endPublicationInstant,"") > 1541695651248
ORDER BY publicationInstant DESC
OFFSET 172875 LIMIT 25;

Created functional index on IFMISSINGORNULL(endPublicationInstant,"") i.e when first argument is MISSING or NULL it returns “” , I have chosen “” because you have number comparison and string is higher than number so predicates makes easy. If your data has “” for endPublicationInstant chose different value which not present. Use same function in the predicate.

Now query and index combination able to push all predicates to indexer, no false positives possible from indexscan, and query order matches with index keys order. This allows avoid sort which in turn allows push offset and limit to indexer. Checkout EXPLAIN: IndexScan section will have limit, offset, At the end both Order, Offset operator will not be present.

Other option avoid offset and change the publicationInstant < 1541695651248 last seen value as described in the keyset-pagination
Also checkout

https://blog.couchbase.com/create-right-index-get-right-performance/
https://blog.couchbase.com/offset-keyset-pagination-n1ql-query-couchbase/