Major Description
For Index selection query must have predicate on leading index key.
Once index selection is made spans/ranges are generated based on predicates.
At present we only pass leading n continuos index keys predicates only to indexer.
CREATE INDEX ix1 ON default(c0,c1,c2,c3,c4,c5); |
EXPLAIN SELECT * FROM default WHERE c0 BETWEEN 1 AND 100 AND c2 = "XYZ" AND c4 = 200; |
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"high": "100", |
"inclusion": 3, |
"low": "1" |
}
|
]
|
}
|
]
|
c0 BETWEEN 1 AND 100 qualifies 10K documents |
c0 BETWEEN 1 AND 100 AND c2 = "XYZ" qualifies 100 documents |
c0 BETWEEN 1 AND 100 AND c2 = "XYZ" AND c4 = 200 qualifies 10 documents |
Above query has predicates on c0, c2 and c4 but there is no predicate on c1 query only pass the range of c0 to indexer (not c2, c4). This result in 10K documents fetch and applied c2 and c4 predicates post fetch and produces 10 documents.
There are lot of unnecessary processing.
As GSI index maintains MISSING values on NON-LEADING key, during span generation we can generate FULL scan on skipped ranges and push other predicates to indexer.
|
|
"spans": [ |
{
|
"exact": true, |
"range": [ |
{
|
"high": "100", |
"inclusion": 3, |
"low": "1" |
},
|
{
|
"inclusion": 0 |
},
|
{
|
"high": "\"XYZ\"", |
"inclusion": 3, |
"low": "\"XYZ\"" |
},
|
{
|
"inclusion": 0 |
},
|
{
|
"high": "200", |
"inclusion": 3, |
"low": "200" |
}
|
]
|
}
|
]
|
|
In this case indexer will produce only 10 documents and result in only required document fetch.
- Result in more predicates pushed to indexer
- Exact predicate pushdown and less false positives
- More queries qualify ORDER,LIMIT,OFFSET, GROUP, Aggregation pushdown
- Reduce Unnecessary Fetch
- Can result in less number of indexes.
- Can contribute performance and scaling
__CF_USER_TEXT_ENCODING=0x1F7:0x0:0x0 |
INSERT INTO orders (KEY,VALUE)
|
VALUES("is0001", {"type":"indexscan","test_id":"indexscan","c0":1, "c1":10,"c2":100,"c3":1000,"c4":10000, "a1":[11,11,12,12,13,14], "a2":[[11,12],[11,12],[13,14]], "a3":[{"id":11, "type": "n"},{"id":11,"type": "n"},{"id":12,"type": "n"},{"id":13,"type": "n"},{"id":14,"type": "n"},{"id":15,"type": "n"}],"a4":[{"aa":[{"id":11},{"id":11},{"id":12}]},{"aa":[{"id":13},{"id":14},{"id":15}]}]}), |
VALUES("is0002", {"type":"indexscan","test_id":"indexscan","c0":2, "c1":20,"c2":200,"c3":2000,"c4":20000, "a1":[11,11,12,12,13,14], "a2":[[11,12],[11,12],[13,14]], "a3":[{"id":11, "type": "n"},{"id":11,"type": "n"},{"id":12,"type": "n"},{"id":13,"type": "n"},{"id":14,"type": "n"},{"id":15,"type": "n"}],"a4":[{"aa":[{"id":11},{"id":11},{"id":12}]},{"aa":[{"id":13},{"id":14},{"id":15}]}]}), |
VALUES("is0003", {"type":"indexscan","test_id":"indexscan","c0":3, "c1":30,"c2":300,"c3":3000,"c4":30000, "a1":[11,11,12,12,13,14], "a2":[[11,12],[11,12],[13,14]], "a3":[{"id":11, "type": "n"},{"id":11,"type": "n"},{"id":12,"type": "n"},{"id":13,"type": "n"},{"id":14,"type": "n"},{"id":15,"type": "n"}],"a4":[{"aa":[{"id":11},{"id":11},{"id":12}]},{"aa":[{"id":13},{"id":14},{"id":15}]}]}), |
VALUES("is0004", {"type":"indexscan","test_id":"indexscan","c0":4, "c1":40,"c2":400,"c3":4000,"c4":40000, "a1":[11,11,12,12,13,14], "a2":[[11,12],[11,12],[13,14]], "a3":[{"id":11, "type": "n"},{"id":11,"type": "n"},{"id":12,"type": "n"},{"id":13,"type": "n"},{"id":14,"type": "n"},{"id":15,"type": "n"}],"a4":[{"aa":[{"id":11},{"id":11},{"id":12}]},{"aa":[{"id":13},{"id":14},{"id":15}]}]}), |
VALUES("is0005", {"type":"indexscan","test_id":"indexscan","c0":5, "c1":50,"c2":500,"c3":5000,"c4":50000, "a1":[11,11,12,12,13,14], "a2":[[11,12],[11,12],[13,14]], "a3":[{"id":11, "type": "n"},{"id":11,"type": "n"},{"id":12,"type": "n"},{"id":13,"type": "n"},{"id":14,"type": "n"},{"id":15,"type": "n"}],"a4":[{"aa":[{"id":11},{"id":11},{"id":12}]},{"aa":[{"id":13},{"id":14},{"id":15}]}]}), |
VALUES("is0006", {"type":"indexscan","test_id":"indexscan","c0":6, "c2":600,"c4":60000, "a1":[11,11,12,12,13,14], "a2":[[11,12],[11,12],[13,14]], "a3":[{"id":11, "type": "n"},{"id":11,"type": "n"},{"id":12,"type": "n"},{"id":13,"type": "n"},{"id":14,"type": "n"},{"id":15,"type": "n"}],"a4":[{"aa":[{"id":11},{"id":11},{"id":12}]},{"aa":[{"id":13},{"id":14},{"id":15}]}]}); |
|
|
CREATE INDEX is01 ON orders (c0,c1,c2,DISTINCT a1,c3,c4) WHERE type="indexscan" AND test_id = "indexscan"; |
CREATE INDEX is02 ON orders (ALL ARRAY v.id FOR v IN a3 END,c0,c1,c2,c3,c4) WHERE type="indexscan" AND test_id = "indexscan"; |
SELECT META(o).id FROM orders AS o WHERE o.type="indexscan" AND o.test_id = "indexscan" AND o.c0 > 0 AND o.c1 >= 10 AND o.c3 = 1000; |
SELECT META(o).id FROM orders AS o WHERE o.type="indexscan" AND o.test_id = "indexscan" AND o.c0 > 0 AND o.c1 >= 10 AND o.c3 IN [1000, 2000]; |
SELECT META(o).id FROM orders AS o WHERE o.type="indexscan" AND o.test_id = "indexscan" AND o.c0 > 0 AND ANY v IN o.a1 SATISFIES v IN [11,12] END AND o.c3 = 1000; |
SELECT META(o).id FROM orders AS o WHERE o.type="indexscan" AND o.test_id = "indexscan" AND o.c0 > 0 AND o.c1 IS MISSING AND o.c2 = 600 AND o.c3 IS MISSING ; |
SELECT v.id FROM orders AS o UNNEST o.a3 AS v WHERE o.type="indexscan" AND o.test_id = "indexscan" AND v.id > 11 AND o.c0 = 1 AND o.c3 = 1000; |
|
INSERT INTO default (KEY,VALUE) VALUES("is0000", {"c0":0, "c1":10,"c2":10,"c3":1000,"c4":10000}), |
VALUES("is0001", {"c0":1, "c1":10,"c2":20,"c3":1000,"c4":10000}), |
VALUES("is0002", {"c0":2, "c1":10,"c2":20,"c3":1000,"c4":10000}), |
VALUES("is0003", {"c0":3, "c1":10,"c2":40,"c3":1000,"c4":10000}), |
VALUES("is0004", {"c0":4, "c2":100,"c4":10000}), |
VALUES("is0005", {"c0":5, "c1":null,"c2":100,"c4":20000}), |
VALUES("is0006", {"c0":6, "c1":10,"c2":100,"c3":2000,"c4":10000}), |
VALUES("is0007", {"c0":7, "c1":10,"c2":110,"c3":1000,"c4":10000}), |
VALUES("is0008", {"c0":8, "c1":10,"c2":120,"c3":1000,"c4":10000}), |
VALUES("is0009", {"c0":9, "c1":10,"c2":130,"c3":1000,"c4":10000}); |
|
|
CREATE INDEX ix1 ON default(c0,c1,c2,c3,c4); |
|
|
SELECT c0,c1,c2,c3,c4, META(d).id
|
FROM default AS d |
WHERE c0 BETWEEN 1 AND 8 AND c2 = 100; |
|
|
SELECT c0,c1,c2,c3,c4, META(d).id
|
FROM default AS d |
WHERE c0 BETWEEN 1 AND 8 AND c2 = 100 AND c4 = 20000; |
|
|
SELECT c0,c1,c2,c3,c4, META(d).id
|
FROM default AS d |
WHERE c0 BETWEEN 1 AND 8 AND c2 = 100 AND c1 IS MISSING; |
|
|
SELECT c0,c1,c2,c3,c4, META(d).id
|
FROM default AS d |
WHERE c0 BETWEEN 1 AND 8 AND c1 IS NULL AND c3 IS MISSING; |