DELETE query works very slow

SQL++
1

Hi all,

I would like to run delete query but it is working very slow. I am using 6.0.0 community edition. I have tried this.

DELETE FROM appconnect WHERE _class = “engage.data.couchbase.entity.AudienceMemberEntity”
AND appId = “APCMQH” AND audienceId = “0f2de811-c482-11e9-a180-45ac70e4a33”

and my index is

CREATE INDEX audience_members_idx ON appconnect(_class,appId,audienceId) WHERE (_class = “engage.data.couchbase.entity.AudienceMemberEntity”)

If I use select query and limit by 2500 it works very fast, but my document count is around 300.000 ~. How can I fasten this query because I am facing with query timeout and it exceeds 75 second. My explain query:

  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "IndexScan3",
        "index": "audience_members_idx",
        "index_id": "d1bd089c690c2ca8",
        "index_projection": {
          "primary_key": true
        },
        "keyspace": "appconnect",
        "namespace": "default",
        "spans": [
          {
            "range": [
              {
                "high": "\"engage.data.couchbase.entity.AudienceMemberEntity\"",
                "inclusion": 3,
                "low": "\"engage.data.couchbase.entity.AudienceMemberEntity\""
              },
              {
                "inclusion": 0,
                "low": "null"
              },
              {
                "inclusion": 0,
                "low": "null"
              }
            ]
          }
        ],
        "using": "gsi"
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "Fetch",
              "keyspace": "appconnect",
              "namespace": "default"
            },
            {
              "#operator": "Filter",
              "condition": "((((`appconnect`.`appId`) = (`appconnect`.`whitelabel`)) and ((`appconnect`.`audienceId`) = (`appconnect`.`1b7192e6-c44b-11e9-8cff-11eb4ce107c`))) and ((`appconnect`.`_class`) = \"engage.data.couchbase.entity.AudienceMemberEntity\"))"
            },
            {
              "#operator": "SendDelete",
              "alias": "appconnect",
              "keyspace": "appconnect",
              "namespace": "default"
            },
            {
              "#operator": "InitialProject",
              "result_terms": [
                {
                  "expr": "`appconnect`",
                  "star": true
                },
                {
                  "as": "_ID",
                  "expr": "(meta(`appconnect`).`id`)"
                },
                {
                  "as": "_CAS",
                  "expr": "(meta(`appconnect`).`cas`)"
                }
              ]
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      }
    ]
  },
  "text": "DELETE FROM `appconnect` \nWHERE (`appId` = `whitelabel` AND `audienceId` = `1b7192e6-c44b-11e9-8cff-11eb4ce107`) \nAND `_class` = \"engage.data.couchbase.entity.AudienceMemberEntity\" \nRETURNING `appconnect`.*, META(`appconnect`).id AS _ID, META(`appconnect`).cas AS _CAS"
} ```
2

It looks like the index scan is slow because it is only using one of the three fields of the query, i.e. _class. Try removing the _class from the fields of the CREATE INDEX statements. That might let the optimizer clue in that the remaining fields are usable.

CREATE INDEX  audience_members_idx  ON  appconnect( appId , audienceId ) 
WHERE ( `_class`  = “engage.data.couchbase.entity.AudienceMemberEntity”)
1 Like
3 
DELETE FROM `appconnect` 
WHERE (`appId` = `whitelabel` AND `audienceId` = `1b7192e6-c44b-11e9-8cff-11eb4ce107`) \nAND `_class` = \"engage.data.couchbase.entity.AudienceMemberEntity\" \nRETURNING `appconnect`.*, META(`appconnect`).id AS _ID, META(`appconnect`).cas AS _CAS

The problem is with your delete query.
If you see whitelabel , 1b7192e6-c44b-11e9-8cff-11eb4ce107 has back-ticks ? are you sure about it. Values needs double quotes not back ticks.
Why do u need RETURNING whole document and cas, Which reuqired Fetch otherwise it can use covering.
You can do chunks with LIMIT clause and repeat DELETE

4

I tried with this index and deleted returning from the query. Still slow. In 5 seconds, it deletes 8000 ~records

5

I tried with

DELETE FROM app WHERE _class = "engage.data.couchbase.entity.AudienceMemberEntity" and appId = "APCMQJH"
AND audienceId = "0f2de811-c482-11e9-a180-45ac70e4a33"

I made a mistake copying here

6 
DELETE FROM app 
WHERE _class = "engage.data.couchbase.entity.AudienceMemberEntity" and appId = "APCMQJH"
AND audienceId = "0f2de811-c482-11e9-a180-45ac70e4a33";

Above query should have used covering index and must have fast. If you are talking 300K documents, It takes time due to mutations. If timeout , Use LIMIT option and repeat statement.

1 Like