Using the following documents with four attributes each:
test:1
{"type":"test", "u":"4b0f86fc", "a1":"e2d27cc7", "a2":"7e16f001", "s":"graceful"}
test:2
{"type":"test", "u":"4b0f86fc", "a1":"cad907a9", "a2":"7e16f001", "s":"credit"}
test:3
{"type":"test", "u":"4b0f86fc", "a1":"e2d27cc7", "a2":"89ef6a8f", "s":"overflow"}
test:4
{"type":"test", "u":"6718d226", "a1":"cad907a9", "a2": "89ef6a8f", "s": "desert"}
The goal was to have a covering index for u and s, and then a separate adaptive index for a1, a2, … an (there are two above, but more in the real case). The u attribute would be a standard filter on every query, the ax attributes extra filters built into the query string conditionally, and the s attribute used for sorting final results. The intent is that there would be an intersect scan with some covered attributes for sorting. Essentially we have paged queries powering a UI that supports filters and sorted columns, where the user gets to pick the filters.
I have seen this post:
Covering Indexes with IntersectScan or UnionScan
Which indicates that covered drops out on intersect scans as late as 4.5. However, the documentation here:
https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/covering-indexes.html
implies that INTERSECT is one of the query types that “. . . can benefit from covering indexes.” We’re running 6.0.2 (Enterprise), so I was hoping maybe the situation had changed since 4.5 and covered would work with intersect.
Unfortunately, I’m finding that:
- I cannot get the intersect scan to happen with an adaptive index, only individual.
- Without adaptive, I can get the intersect scan to work, but not get covered attributes for sorting (always a fetch).
For #1, here is the test:
CREATE INDEX `us_idx` ON `test`(`u`,`s`) WHERE `type`="test"
CREATE INDEX `adp_idx` ON `test`(DISTINCT PAIRS({a1, a2}))
SELECT meta().id FROM `test` WHERE `u`="4b0f86fc" AND `a1`="cad907a9" AND `type`="test"
It correctly returns test:2, but the explain indicates us_idx is used along with a fetch for a1 - no intersect scan. Hitting:
SELECT meta().id FROM test
WHERE a1
=“cad907a9”
Happily uses the adaptive index and correctly returns test:2 and test:4, so it looks like I just can’t get the adaptive to come in on an intersect scan (even if I add the WHERE clause for the type attribute in the adaptive index).
For #2, I’m going to change it up a little (no adaptive index - I dropped adp_idx and built a targeted index):
CREATE INDEX `us_idx` ON `test`(`u`,`s`) WHERE `type`="test"
CREATE INDEX `attr1_idx` ON `test`(`a1`) WHERE `type`="test"
With these:
SELECT meta().id FROM
testWHERE
u="4b0f86fc" AND
a1="cad907a9" AND
type="test"
Now does the intersect scan between us_idx and attr1_idx, but I’ve lost the coverage for s. Sorting ends up taking a fetch (it’s that coverage that I was hoping made it in post 4.5 based on the 6.0 docs).
Ultimately, I’m really after something like this:
SELECT RAW meta().id
FROM `test`
WHERE `u`="4b0f86fc" AND `a1` LIKE "cad907a9" AND `type`="test"
ORDER BY `s`
yielding an intersect scan between some kind of indexes with s
covered for fast sort (and not having to to do a really broad scan across a 10’s of thousands of potential items).
Am I basically out of luck on this? We’re playing with FTS to solve this, but there are separate challenges there and would like the N1QL option.
Looking forward to a response, and thanks in advance for any help or thoughts!
PS-One last thing… The reason I am not trying to build an adaptive index that includes u (seems obvious) is the clear indication that adaptive doesn’t support covered. Additionally, I have quite a few columns for sorting (over 10), so the nothing that I could add those to the us_idx (s1, s2, s3… sn) was attractive if I could then just put the other filters in separate “light” indexes.