Implementing keyset pagination with multiple keys and an index is easy when all the keys have the same ordering (ascending or descending). It is just a matter of using JSON arrays comparison in N1QL selects where clauses. It performs a nice lexicographic comparison, and the index is used. However, Couchbase GSI allow to create indexes with per-key order, each key having its own.
JSON array comparison does not work any longer, and the only solution I see is to revert to a more complex where clause. For instance to implement:
[a, b, c] < [d, e, f]
It is possible to write the following:
(a < d) or ((a == d) and ((b < e) or ((b == e) and ((c < f)))))
It does the same, and it is possible to change the ordering based on each key (replace < with > wherever necessary).
It works, but it seems that the select execution plan does not use the index any longer, even with comparisons matching exactly the index. I guess the expression is becoming too complex for the select interpreter to figure out it is just looking at the index.
Any solution to this? Or we must forget efficient keyset pagination when there is per-key order?