Major Description
Currently INDEX JOIN/INDEX NEST supports covering on right side bucket. But needs improvements work properly.
INDEX JOIN
1) The expressions on right side is not shown as covered
2) The index section shows cover but the results are not right
3) meta(right).id should include automatically.
4) the right side index chosen is partial index the filterCovers needs to be included
5) right side index should not contain array index because of de-dup required.
INDEX NEST
NEST makes right side as array and can't be used as covered index on right side.
In that case they can use INDEX JOIN and group by.
|
|
CREATE PRIMARY INDEX ON default;
|
CREATE INDEX ix1 ON default(docid,name);
|
CREATE INDEX ix2 ON default(docid,name) where type = "wdoc";
|
CREATE INDEX ix3 ON default(altid, name, DISTINCT ARRAY p FOR p IN phones END);
|
|
|
INSERT INTO default VALUES("w001",{"type":"wdoc", "docid":"x001","name":"wdoc","phones":["123-456-7890","123-456-7891"],"altid":"x001"});
|
INSERT INTO default VALUES("p001",{"type":"pdoc", "docid":"x001","name":"pdoc","phones":["123-456-7890","123-456-7891"],"altid":"x001"});
|
INSERT INTO default VALUES("p002",{"type":"pdoc", "docid":"w001","name":"pdoc","phones":["123-456-7890","123-456-7891"],"altid":"w001"});
|
|
|
-- The following queries can use covered index on right side.
|
|
|
SELECT meta(b1).id b1id FROM default b1 JOIN default b2 ON KEY b2.docid FOR b1 WHERE meta(b1).id > "";
|
SELECT meta(b1).id b1id, meta(b2).id b2id FROM default b1 JOIN default b2 ON KEY b2.docid FOR b1 WHERE meta(b1).id > "";
|
SELECT meta(b1).id b1id, b2.docid FROM default b1 JOIN default b2 ON KEY b2.docid FOR b1 WHERE meta(b1).id > "";
|
SELECT meta(b1).id b1id, b2.name FROM default b1 JOIN default b2 ON KEY b2.docid FOR b1 WHERE meta(b1).id > "";
|
SELECT meta(b1).id b1id, b2.name, b3.docid FROM default b1 JOIN default b2 ON KEY b2.docid FOR b1 JOIN default b3 ON KEY b3.docid FOR b1 WHERE meta(b1).id > "";
|
SELECT meta(b1).id b1id, b2.name, b3.docid FROM default b1 JOIN default b2 ON KEY b2.docid FOR b1 JOIN default b3 ON KEY b3.docid FOR b2 WHERE meta(b1).id > "";
|
SELECT meta(b1).id b1id, meta(b2).id, b2.name FROM default b1 JOIN default b2 ON KEY b2.docid FOR b1 WHERE meta(b1).id > "" AND b2.type = "wdoc";
|
SELECT meta(b1).id b1id, b2.name, b3.docid FROM default b1 JOIN default b2 ON KEY b2.docid FOR b1 JOIN default b3 ON KEY b3.docid FOR b1 WHERE meta(b1).id > "" AND b2.type = "wdoc";
|
SELECT meta(b1).id b1id, b2.name, b3.docid FROM default b1 JOIN default b2 ON KEY b2.docid FOR b1 JOIN default b3 ON KEY b3.docid FOR b2 WHERE meta(b1).id > "" AND b2.type = "wdoc";
|
|
|
-- The following will raise no matching index error due to right side as only array index. skipping array indexes.
|
SELECT meta(b1).id b1id FROM default b1 JOIN default b2 ON KEY b2.altid FOR b1 WHERE meta(b1).id > "";
|
-- right side is not covered
|
|
|
SELECT meta(b1).id b1id, b2 from default b1 JOIN default b2 ON KEY b2.docid FOR b1 WHERE meta(b1).id > "";
|
|
|
|
|
-- NEST can't use right side covered index.
|
|
|
SELECT meta(b1).id b1id from default b1 NEST default b2 ON KEY b2.docid FOR b1 WHERE meta(b1).id > "";
|
SELECT meta(b1).id b1id, b2 from default b1 NEST default b2 ON KEY b2.docid FOR b1 WHERE meta(b1).id > "";
|
|