Index multiple arrays

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
}
2 Likes