Pagination optimization queries

At present Query can expose index order when query order matches index order. Otherwise

it must produce all the possible values i.e. (do indexScan, send it to query, if required fetch and do sort and produce results).

CREATE INDEX ix1 ON default(size,brand,color,price);

SELECT *

FROM default AS d

WHERE size > 7

ORDER BY size 

LIMIT 10;

Above query can use ix1, use index order and push limit.

Assume above query qualifies 1Million documents.

Now if you add DESC or ODER BY any combinations of index key. Queries can't use index order and taken take lot of time and TCO increases drastically.

ORDER BY possibility is 4! *2 = 20 combinations, one creating 20 indexes imposible.

Dynamic order is very common in dashboard queries.

If we consider couchbase as whole (not individual services) these can be easily solvable .

When All predicates are pushed indexer and no false positives possible LIMIT can be pushed if no ORDER. Same logic extend further when ORDER keys are part of the index with out consider index order By telling index the order it needs.

Indexer will implement top-n MAX or MIN heap and keep only the rows that needs and at the end of the scan they send those rows.  If further optimization they can consider is partial sort if already sorted partial keys.

One can consider this Only when OFFSET/LIMIT less than certain agreeable LIMIT (say 8192). 

All 20 combinations can be serviced by single index efficiently

Note: Early order implementation pass it to indexer, so that applied at the edge