It is possible to create an index which covers multiple arrays?
For example:
select meta().id from bucketTest where
any x in arrayA satisfies x='abc' end
and any x in arrayB satisfies x='def' end
or
select meta().id from bucketTest where
array_contains(arrayA, 'abc')
and array_contains(arrayB, 'def')
or
select meta().id from bucketTest where
'abc' in arrayA
and 'def' in arrayB
you can not create index with more than one array expression in the index, that means if you add the index by
CREATE INDEX `idx_arrayA_arrayB` ON `bucketTest`(DISTINCT ARRAY a FOR a IN arrayA END,DISTINCT ARRAY b FOR b IN arrayB END) USING GSI;
you will get the error
[
{
"code": 5000,
"msg": "GSI CreateIndex() - cause: Multiple expressions with ALL are found. Only one array expression is supported per index.",
"query_from_user": "CREATE INDEX `idx_arrayA_arrayB` ON `bucketTest`(DISTINCT ARRAY a FOR a IN arrayA END,DISTINCT ARRAY b FOR b IN arrayB END) USING GSI;"
}
]
but you can create 2 index by
CREATE INDEX `idx_arrayA` ON `bucketTest`(DISTINCT ARRAY a FOR a IN arrayA END) USING GSI;
CREATE INDEX `idx_arrayB` ON `bucketTest`(DISTINCT ARRAY b FOR b IN arrayB END) USING GSI;
then it will be use IntersectScan with this 2 index ,explain looks like
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 2,
"execTime": "1.188µs",
"kernTime": "3.837468ms"
},
"~children": [
{
"#operator": "Authorize",
"#stats": {
"#phaseSwitches": 4,
"execTime": "4.751µs",
"kernTime": "3.151838ms",
"servTime": "671.929µs"
},
"privileges": {
"default:default": 1
},
"~child": {
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 3,
"execTime": "3.768µs",
"kernTime": "3.144591ms"
},
"~children": [
{
"#operator": "IntersectScan",
"#stats": {
"#phaseSwitches": 2,
"execTime": "473.858µs",
"kernTime": "2.401177ms"
},
"scans": [
{
"#operator": "DistinctScan",
"#stats": {
"#phaseSwitches": 4,
"execTime": "21.047µs",
"kernTime": "209.8µs",
"servTime": "2.160548ms"
},
"scan": {
"#operator": "IndexScan",
"#stats": {
"#phaseSwitches": 3,
"execTime": "20.538µs",
"kernTime": "2.332477ms"
},
"index": "idx_arrayA",
"index_id": "5451c3aa51094241",
"keyspace": "default",
"namespace": "default",
"spans": [
{
"Exact": true,
"Range": {
"High": [
"\"abc\""
],
"Inclusion": 3,
"Low": [
"\"abc\""
]
}
}
],
"using": "gsi",
"~children": [
{
"#operator": "IndexScan",
"#stats": {
"#phaseSwitches": 5,
"execTime": "8.797µs",
"kernTime": "255ns",
"servTime": "2.124976ms"
},
"index": "idx_arrayA",
"index_id": "5451c3aa51094241",
"keyspace": "default",
"namespace": "default",
"spans": [
{
"Exact": true,
"Range": {
"High": [
"\"abc\""
],
"Inclusion": 3,
"Low": [
"\"abc\""
]
}
}
],
"using": "gsi"
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000020538
},
"#time_normal": "00:00.0021",
"#time_absolute": 0.002181595
},
{
"#operator": "DistinctScan",
"#stats": {
"#phaseSwitches": 5,
"execTime": "20.734µs",
"kernTime": "291ns",
"servTime": "2.134139ms"
},
"scan": {
"#operator": "IndexScan",
"#stats": {
"#phaseSwitches": 3,
"execTime": "11.014µs",
"kernTime": "2.117874ms"
},
"index": "idx_arrayB",
"index_id": "ba9d3879967366c6",
"keyspace": "default",
"namespace": "default",
"spans": [
{
"Exact": true,
"Range": {
"High": [
"\"def\""
],
"Inclusion": 3,
"Low": [
"\"def\""
]
}
}
],
"using": "gsi",
"~children": [
{
"#operator": "IndexScan",
"#stats": {
"#phaseSwitches": 5,
"execTime": "9.388µs",
"kernTime": "1.086µs",
"servTime": "2.102354ms"
},
"index": "idx_arrayB",
"index_id": "ba9d3879967366c6",
"keyspace": "default",
"namespace": "default",
"spans": [
{
"Exact": true,
"Range": {
"High": [
"\"def\""
],
"Inclusion": 3,
"Low": [
"\"def\""
]
}
}
],
"using": "gsi"
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000011014
},
"#time_normal": "00:00.0021",
"#time_absolute": 0.002154873
}
],
"#time_normal": "00:00.0004",
"#time_absolute": 0.000473858
},
{
"#operator": "Fetch",
"#stats": {
"#phaseSwitches": 5,
"execTime": "2.878µs",
"kernTime": "3.112499ms"
},
"keyspace": "default",
"namespace": "default",
"#time_normal": "00:00.0000",
"#time_absolute": 0.0000028780000000000002
},
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 5,
"execTime": "2.076µs",
"kernTime": "3.138215ms"
},
"~children": [
{
"#operator": "Filter",
"#stats": {
"#phaseSwitches": 5,
"execTime": "2.432µs",
"kernTime": "3.120183ms"
},
"condition": "(any `a` in (`default`.`arrayA`) satisfies (`a` = \"abc\") end and any `b` in (`default`.`arrayB`) satisfies (`b` = \"def\") end)",
"#time_normal": "00:00.0000",
"#time_absolute": 0.0000024319999999999998
},
{
"#operator": "InitialProject",
"#stats": {
"#phaseSwitches": 5,
"execTime": "1.671µs",
"kernTime": "3.125404ms"
},
"result_terms": [
{
"expr": "(meta(`default`).`id`)"
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000001671
},
{
"#operator": "FinalProject",
"#stats": {
"#phaseSwitches": 7,
"execTime": "2.635µs",
"kernTime": "3.131838ms"
},
"#time_normal": "00:00.0000",
"#time_absolute": 0.0000026349999999999998
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000002076
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.0000037679999999999998
},
"#time_normal": "00:00.0006",
"#time_absolute": 0.0006766799999999999
},
{
"#operator": "Stream",
"#stats": {
"#phaseSwitches": 7,
"execTime": "2.704µs",
"kernTime": "3.83178ms"
},
"#time_normal": "00:00.0000",
"#time_absolute": 0.0000027040000000000003
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.0000011879999999999999
}