Here are the results of my experiments. An uneducated cursory look at the execution plans tell me that we’re fine, but we would be grateful to have confirmation.
We have created two simple indexes (without JSON array):
CREATE INDEX abc ON test(a, b, c)
CREATE INDEX abdescc ON test(a, b desc, c)
Here are two execution plans for the first one:
abc.par.lex.txt.zip (1.3 KB)
abc.par.pre.txt.zip (1.1 KB)
And one for the second one:
abdescc.par.lex.txt.zip (1.4 KB)
Instead of using JSON array comparisons we’ve used the versatile lexicographic boolean expression. All selects take a slice of the respective index.
The execution plans show that all parameters are used in the ranges, so we are under the assumption that even if there are lots of documents with the same ‘a’ value, the selects will be very fast. Do you confirm?
As usual, thanks a lot!