Document's inner array update query optimization

SQL++
, ,
1

Hey guys, I am new to Couchbase and (N1QL) but I have a query that I need to be optimized.

Here’s the UPDATE query:

UPDATE items AS i
SET i.innerArray = ARRAY a FOR a IN i.innerArray WHEN a.innerArrayItemId NOT IN $innerArrayItemIds END,
    i.modifiedOn = CLOCK_UTC()
WHERE ANY a IN i.innerArray SATISFIES a.innerArrayItemId IN $innerArrayItemIds END
  AND i.ownerId = $ownerId
  AND i.type = 'item'
RETURNING i.id as id, ARRAY_LENGTH(i.innerArray) as remaining, i.innerDoc.innerDocId as innerDocId

And here’s the execution plan:

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "DistinctScan",
        "scan": {
          "#operator": "IndexScan3",
          "as": "i",
          "index": "item-ownerId-innerArrayItemId",
          "index_id": "7eb15c2ae0c60ae",
          "index_projection": {
            "primary_key": true
          },
          "keyspace": "items",
          "namespace": "default",
          "spans": [
            {
              "range": [
                {
                  "high": "$ownerId",
                  "inclusion": 3,
                  "low": "$ownerId"
                },
                {
                  "high": "array_max($innerArrayItemIds)",
                  "inclusion": 3,
                  "low": "array_min($innerArrayItemIds)"
                }
              ]
            }
          ],
          "using": "gsi"
        }
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "Fetch",
              "as": "i",
              "keyspace": "items",
              "namespace": "default"
            },
            {
              "#operator": "Filter",
              "condition": "((any `a` in (`i`.`innerArray`) satisfies ((`a`.`innerArrayItemId`) in $innerArrayItemIds) end and ((`i`.`ownerId`) = $ownerId)) and ((`i`.`type`) = \"item\"))"
            },
            {
              "#operator": "Clone"
            },
            {
              "#operator": "Set",
              "set_terms": [
                {
                  "path": "(`i`.`innerArray`)",
                  "value": "array `a` for `a` in (`i`.`innerArray`) when (not ((`a`.`innerArrayItemId`) in $innerArrayItemIds)) end"
                },
                {
                  "path": "(`i`.`modifiedOn`)",
                  "value": "clock_utc()"
                }
              ]
            },
            {
              "#operator": "SendUpdate",
              "alias": "i",
              "keyspace": "items",
              "namespace": "default"
            },
            {
              "#operator": "InitialProject",
              "result_terms": [
                {
                  "as": "id",
                  "expr": "(`i`.`id`)"
                },
                {
                  "as": "remaining",
                  "expr": "array_length((`i`.`innerArray`))"
                },
                {
                  "as": "innerDocId",
                  "expr": "((`i`.`innerDoc`).`innerArrayItemId`)"
                }
              ]
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      }
    ]
  }
}

Any suggestion of how this can be improved would be just fine. Thank you!

2

Tis query uses USE KEYS, no index is required. No improvement is needed.

4

@vsr1 I’m sorry, but the query was wrong. So I’ve made some changes to it