Count queries are two slow N1QL

SQL++
, ,
1

Hi

Need help

We have 2 count queries that are taking more time for execution,
We have two clusters one on Enterprise Edition 5.1.0 build 5552 and another on Enterprise Edition 6.0.2 build 2413 and both the clusters are with 7 nodes(3 data, 2 index and 2 query nodes)
There are around 20 million documents in the bucket that is being queried.
However, queries are taking more than 1 min to execute and the results are same in both the clusters.

PFB queries

Query:
SELECT count(*) FROM promotions commandStatus UNNEST commandStatus.migrations as migrations
WHERE commandStatus._class = “com.tesco.api.promotion.domain.commandStatus.LegacyCommandStatus”
AND migrations.status = “ERROR”
AND commandStatus.messageId LIKE “%PR%”
AND commandStatus.createdDateTime BETWEEN 1554536717000 AND 1586159117000

Index:
CREATE INDEX legacy_ALL_migrations_createdDateTime_messageId
ON promotions(ALL migrations,createdDateTime,messageId)
WHERE _class = ‘com.tesco.api.promotion.domain.commandStatus.LegacyCommandStatus’
using GSI WITH {“num_replica”: 1};

Explain:

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "IndexCountScan2",
        "covers": [
          "cover ((`migrations`.`status`))",
          "cover ((`commandStatus`.`createdDateTime`))",
          "cover ((`commandStatus`.`messageId`))",
          "cover ((meta(`commandStatus`).`id`))"
        ],
        "filter_covers": {
          "cover (((`commandStatus`.`migrations`) < {}))": true,
          "cover (([] <= (`commandStatus`.`migrations`)))": true,
          "cover ((`commandStatus`.`_class`))": "com.tesco.api.promotion.domain.commandStatus.LegacyCommandStatus",
          "cover (is_array((`commandStatus`.`migrations`)))": true
        },
        "index": "legacy_ALL_migrations_status_createdDateTime_messageId",
        "index_id": "1db88738656eaea3",
        "keyspace": "promotions",
        "namespace": "default",
        "spans": [
          {
            "exact": true,
            "range": [
              {
                "high": "\"ERROR\"",
                "inclusion": 3,
                "low": "\"ERROR\""
              }
            ]
          }
        ],
        "using": "gsi"
      },
      {
        "#operator": "IndexCountProject",
        "result_terms": [
          {
            "expr": "count(*)"
          }
        ]
      }
    ]
  },
  "text": "SELECT count(*)   FROM promotions commandStatus UNNEST commandStatus.migrations as migrations  \nWHERE commandStatus.`_class` = \"com.tesco.api.promotion.domain.commandStatus.LegacyCommandStatus\" \nAND migrations.status = \"ERROR\" \nAND commandStatus.messageId LIKE \"%PR%\"\nAND commandStatus.createdDateTime BETWEEN 1554536717000 AND 1586159117000"
}

Query:
SELECT count(distinct(commandStatus.extId)) FROM promotions commandStatus
WHERE _class = “com.tesco.api.promotion.domain.commandStatus.LegacyCommandStatus”
AND commandStatus.createdDateTime BETWEEN 1554536717000 AND 1586159117000

Index:
CREATE INDEX legacy_createdDateTime_extId ON promotions(createdDateTime,extId)
WHERE _class = ‘com.tesco.api.promotion.domain.commandStatus.LegacyCommandStatus’
using GSI WITH {“num_replica”: 1};

Explain:

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "IndexScan2",
        "covers": [
          "cover ((`commandStatus`.`createdDateTime`))",
          "cover ((`commandStatus`.`extId`))",
          "cover ((meta(`commandStatus`).`id`))"
        ],
        "filter_covers": {
          "cover ((`commandStatus`.`_class`))": "com.tesco.api.promotion.domain.commandStatus.LegacyCommandStatus"
        },
        "index": "legacy_createdDateTime_extId",
        "index_id": "42dd1218a78b94fb",
        "index_projection": {
          "entry_keys": [
            0,
            1
          ]
        },
        "keyspace": "promotions",
        "namespace": "default",
        "spans": [
          {
            "exact": true,
            "range": [
              {
                "high": "1586159117000",
                "inclusion": 3,
                "low": "1554536717000"
              },
              {
                "inclusion": 0,
                "low": "null"
              }
            ]
          }
        ],
        "using": "gsi"
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "Filter",
              "condition": "((cover ((`commandStatus`.`_class`)) = \"com.tesco.api.promotion.domain.commandStatus.LegacyCommandStatus\") and (cover ((`commandStatus`.`createdDateTime`)) between 1554536717000 and 1586159117000))"
            },
            {
              "#operator": "InitialGroup",
              "aggregates": [
                "count(distinct cover ((`commandStatus`.`extId`)))"
              ],
              "group_keys": []
            }
          ]
        }
      },
      {
        "#operator": "IntermediateGroup",
        "aggregates": [
          "count(distinct cover ((`commandStatus`.`extId`)))"
        ],
        "group_keys": []
      },
      {
        "#operator": "FinalGroup",
        "aggregates": [
          "count(distinct cover ((`commandStatus`.`extId`)))"
        ],
        "group_keys": []
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "InitialProject",
              "result_terms": [
                {
                  "expr": "count(distinct cover ((`commandStatus`.`extId`)))"
                }
              ]
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      }
    ]
  },
  "text": "SELECT count(distinct(commandStatus.extId)) FROM promotions commandStatus \nWHERE `_class` = \"com.tesco.api.promotion.domain.commandStatus.LegacyCommandStatus\"  \nAND commandStatus.createdDateTime BETWEEN 1554536717000 AND 1586159117000"
}

Regards,
Venkat

2

You should try the Couchbase Index Advisor in the 6.5 (beta).
This is the suggestion from it:
CREATE INDEX adv_ALL_migrations_status_class_createdDateTime_messageId ON CUSTOMER(ALL ARRAY migrations.statusFOR migrations INmigrations END,_class,createdDateTime,messageId)

Having the LIKE predicate with a “%PR%” pattern is going to make the query run slow unless other predicates have filtered out most of the documents.

For the second query: you should try the same. Couchbase 5.5 has aggregate pushdown to the indexer. Look at the documentation and articles on it.

3

For Q1 are you sure you want use UNNEST. If the array has multiple values and more than one array element has status = “ERROR”. It counts again due to UNNEST semantics is self join.

I assume you want count one for document. In that case use following. Make sure both queries covered.
If you need leading wildcard you can also explore https://dzone.com/articles/a-couchbase-index-technique-for-like-predicates-wi

SELECT count(1)
FROM promotions AS p
WHERE p._class = "com.tesco.api.promotion.domain.commandStatus.LegacyCommandStatus"
      AND ANY m IN p.migrations SATISFIES m.status = "ERROR" END
      AND p.messageId LIKE "%PR%"
      AND p.createdDateTime BETWEEN 1554536717000 AND 1586159117000;


CREATE INDEX ix10 ON promotions(createdDateTime, messageId)
PARTITION BY HASH (META().id)
WHERE _class = "com.tesco.api.promotion.domain.commandStatus.LegacyCommandStatus"
      AND ANY m IN migrations SATISFIES m.status = "ERROR" END
using GSI WITH {"num_replica": 1, "num_partition":8};

Also you can use ARRAY_CONTAINS(migrations[*].status, "ERROR") instead of ANY m IN p.migrations SATISFIES m.status = "ERROR" END in both query and index

SELECT COUNT(DISTINCT p.extId)
FROM promotions AS p
WHERE p._class = "com.tesco.api.promotion.domain.commandStatus.LegacyCommandStatus"
AND p.createdDateTime BETWEEN 1554536717000 AND 1586159117000;

CREATE INDEX legacy_createdDateTime_extId ON promotions(createdDateTime, extId)
PARTITION BY HASH (META().id)
WHERE _class = "com.tesco.api.promotion.domain.commandStatus.LegacyCommandStatus"
using GSI WITH {"num_replica": 1, "num_partition":8};
4

Hi @keshav_m
Thanks for your input. Will try creating that type of index too. However, when I was using advise of 6.5(beta), here is how it showed me to create an index which is quite different from what you have provided.

image
image2288×984 279 KB

Regards,
Venkat

5

@vsr1
Many thanks for your response. The document looks as below, so if I have only the below document in the bucket I need the count to be 2. So using the approach suggested by I could see the counts are mismatching. Let me know what I am doing incorrectly here.

{
  "requestType": "MigratePromotions",
  "migrations": [
    {
      "identifier": "2020-10-05T07:45:17Z_2020-10-14T07:45:17Z",
      "commandStatusError": [
        {
          "parameters": {
            "shortDescription": ""
          },
          "code": "PROMOTION.SHORT_DESCRIPTION_CANNOT_BE_NULL_OR_EMPTY"
        },
        {
          "parameters": {
            "longDescription": ""
          },
          "code": "PROMOTION.LONG_DESCRIPTION_CANNOT_BE_NULL_OR_EMPTY"
        }
      ],
      "status": "ERROR"
    },
    {
      "identifier": "2020-10-01T07:45:17Z_2020-10-04T07:45:17Z",
      "commandStatusError": [
        {
          "parameters": {
            "longDescription": ""
          },
          "code": "PROMOTION.LONG_DESCRIPTION_CANNOT_BE_NULL_OR_EMPTY"
        },
        {
          "parameters": {
            "ids": [
              "4"
            ]
          },
          "code": "PROMOTION.PROMOTION_LEGACY_CHANGE_DETAIL.CONDITION_ID_NOT_FOUND"
        }
      ],
      "status": "ERROR"
    }
  ],
  "requestId": "5105a354-02cd-4dcb-bf69-579af3465263",
  "messageId": "HH_H33NX_PR_291019235540",
  "createdDateTime": 1573799813117,
  "_class": "com.tesco.api.promotion.domain.commandStatus.LegacyCommandStatus",
  "extId": 504478,
  "clusterId": "ef8d39df-dba5-488f-8af1-b59177bc70c7",
  "errors": [],
  "status": "ERROR"
}

Regards,
Venkat

6

If you are always looking for status = “ERROR” use below query and index.

SELECT SUM(ARRAY_COUNT(ARRAY 1 FOR m IN migrations WHEN m.status = "ERROR"  END))
FROM promotions AS p
WHERE p._class = "com.tesco.api.promotion.domain.commandStatus.LegacyCommandStatus"
      AND p.messageId LIKE "%PR%"
      AND p.createdDateTime BETWEEN 1554536717000 AND 1586159117000;


CREATE INDEX ix10 ON promotions(createdDateTime, messageId, ARRAY_COUNT(ARRAY 1 FOR m IN migrations WHEN m.status = "ERROR"  END))
PARTITION BY HASH (META().id)
WHERE _class = "com.tesco.api.promotion.domain.commandStatus.LegacyCommandStatus"
using GSI WITH {"num_replica": 1, "num_partition":8};

If it varies from query by query you must use ARRAY indexing

SELECT COUNT(1)
FROM promotions AS p
UNNEST p.migrations AS m
WHERE p._class = "com.tesco.api.promotion.domain.commandStatus.LegacyCommandStatus"
      AND p.messageId LIKE "%PR%"
      AND m.status = "ERROR"
      AND p.createdDateTime BETWEEN 1554536717000 AND 1586159117000;


CREATE INDEX ix10 ON promotions(ALL ARRAY m.status FOR m IN migrations END, createdDateTime, messageId)
PARTITION BY HASH (META().id)
WHERE _class = "com.tesco.api.promotion.domain.commandStatus.LegacyCommandStatus"
using GSI WITH {"num_replica": 1, "num_partition":8};
7

If there are documents in the bucket Index advisor does sampling and recommends Partial indexes . That is reason recommendation is different.

8

@vsr1
Have tried it the query execution time has been reduced by 20 seconds and not its under 40s from 1 minute earlier.
Any other thoughts please.

Regards,
Venkat