Can I improve COUNT() performance?

SQL++
1

Index

CREATE INDEX test_idx ON default(login) WHERE ((((classType = “Order”) and (0 < close_time)) and ((cmd = 0) or (cmd = 1))) and (server = “3”))

Query takes 15s, indexed items 841K, this query result is 81247.
Any possibility to shorten the time under 100ms like views?

select COUNT(meta().id) from default use index(test_idx)
where classType = “Order”
and close_time > 0
and server = “3”
and login between 70000000 and 79999999
and (cmd = 0 or cmd = 1)

explain

[
  {
    "plan": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "IndexScan",
          "index": "test_idx",
          "index_id": "32550db8ae45bc76",
          "keyspace": "default",
          "namespace": "default",
          "spans": [
            {
              "Range": {
                "High": [
                  "79999999"
                ],
                "Inclusion": 3,
                "Low": [
                  "70000000"
                ]
              }
            }
          ],
          "using": "gsi"
        },
        {
          "#operator": "Fetch",
          "keyspace": "default",
          "namespace": "default"
        },
        {
          "#operator": "Parallel",
          "~child": {
            "#operator": "Sequence",
            "~children": [
              {
                "#operator": "Filter",
                "condition": "((((((`default`.`classType`) = \"Order\") and (0 < (`default`.`close_time`))) and ((`default`.`server`) = \"3\")) and ((`default`.`login`) between 70000000 and 79999999)) and (((`default`.`cmd`) = 0) or ((`default`.`cmd`) = 1)))"
              },
              {
                "#operator": "InitialGroup",
                "aggregates": [
                  "count((meta(`default`).`id`))"
                ],
                "group_keys": []
              }
            ]
          }
        },
        {
          "#operator": "IntermediateGroup",
          "aggregates": [
            "count((meta(`default`).`id`))"
          ],
          "group_keys": []
        },
        {
          "#operator": "FinalGroup",
          "aggregates": [
            "count((meta(`default`).`id`))"
          ],
          "group_keys": []
        },
        {
          "#operator": "Parallel",
          "~child": {
            "#operator": "Sequence",
            "~children": [
              {
                "#operator": "InitialProject",
                "result_terms": [
                  {
                    "expr": "count((meta(`default`).`id`))"
                  }
                ]
              },
              {
                "#operator": "FinalProject"
              }
            ]
          }
        }
      ]
    },
    "text": "select COUNT(meta().id) from default use index(test_idx)\nwhere classType = \"Order\"\nand close_time > 0\nand server = \"3\"\nand login between 70000000 and 79999999\nand (cmd = 0 or cmd = 1)"
  }
]
2

Try COUNT(*) or COUNT(1)

instead of COUNT(META().id).

4

COUNT(*) and COUNT(1) and COUNT(login) are 13sec.

5

Can you post EXPLAIN for each of those. We want to see IndexCountScan.

6

COUNT(1), COUNT( * ) and COUNT(login) are almost the same… the only different is ‘1’,’*’ and ‘login’.

[
  {
    "plan": {
      "#operator": "Sequence",
      "~children": [
        {
          "#operator": "IndexScan",
          "index": "test_idx",
          "index_id": "32550db8ae45bc76",
          "keyspace": "default",
          "namespace": "default",
          "spans": [
            {
              "Range": {
                "High": [
                  "79999999"
                ],
                "Inclusion": 3,
                "Low": [
                  "70000000"
                ]
              }
            }
          ],
          "using": "gsi"
        },
        {
          "#operator": "Fetch",
          "keyspace": "default",
          "namespace": "default"
        },
        {
          "#operator": "Parallel",
          "~child": {
            "#operator": "Sequence",
            "~children": [
              {
                "#operator": "Filter",
                "condition": "((((((`default`.`classType`) = \"Order\") and (0 < (`default`.`close_time`))) and ((`default`.`server`) = \"3\")) and ((`default`.`login`) between 70000000 and 79999999)) and (((`default`.`cmd`) = 0) or ((`default`.`cmd`) = 1)))"
              },
              {
                "#operator": "InitialGroup",
                "aggregates": [
                  "count(1)"
                ],
                "group_keys": []
              }
            ]
          }
        },
        {
          "#operator": "IntermediateGroup",
          "aggregates": [
            "count(1)"
          ],
          "group_keys": []
        },
        {
          "#operator": "FinalGroup",
          "aggregates": [
            "count(1)"
          ],
          "group_keys": []
        },
        {
          "#operator": "Parallel",
          "~child": {
            "#operator": "Sequence",
            "~children": [
              {
                "#operator": "InitialProject",
                "result_terms": [
                  {
                    "expr": "count(1)"
                  }
                ]
              },
              {
                "#operator": "FinalProject"
              }
            ]
          }
        }
      ]
    },
    "text": "select COUNT(1) from default use index(test_idx)\nwhere classType = \"Order\"\nand close_time > 0\nand server = \"3\"\nand login between 70000000 and 79999999\nand (cmd = 0 or cmd = 1)"
  }
]
7

You need to be running on Couchbase 4.5.1. Can you verify.

8

Yes, on our Dev testing VMs.

9

I will take another look. But can you please double check that it is 4.5.1, and not a different version. This is important. Please confirm.

10

This is our dev VMs cb version - 4.5.1-2844

11

Hi @hanswong, can you try

cmd IN [ 0, 1 ]

in both the index WHERE clause and the query WHERE clause.

Hi @vsr1, why is this not using IndexCountScan?

12

In this case, Index condition has OR clause even exact predicate contains in query it makes non covered query. To use IndexCountScan the query needs to be covered.

13

This should cover.

CREATE INDEX test_idx ON default(login, cmd) WHERE ((((classType= "Order") and (0 <close_time)) and ((cmd= 0) or (cmd= 1))) and (server = "3"));

select COUNT(*) from default use index(test_idx)
where classType = "Order"
and close_time > 0
and server = "3"
and login between 70000000 and 79999999
and (cmd = 0 or cmd = 1);
14

This is better. @vsr1, what do you think?

@hanswong, please post the EXPLAIN and timings for this.

CREATE INDEX test_idx_cmd0 ON default( login ) WHERE classType= "Order" AND 0 < close_time AND cmd = 0 AND server = "3";

CREATE INDEX test_idx_cmd1 ON default( login ) WHERE classType= "Order" AND 0 < close_time AND cmd = 1 AND server = "3";

SELECT SUM(c) AS count
FROM (
SELECT COUNT(*) AS c
FROM default
WHERE classType = "Order"
AND close_time > 0
AND server = "3"
AND login between 70000000 AND 79999999
AND cmd = 0
UNION ALL
SELECT COUNT(*) AS c
FROM default
WHERE classType = "Order"
AND close_time > 0
AND server = "3"
AND login between 70000000 AND 79999999
AND cmd = 1
) AS s;
15

Yes @geraldss. New one will be better.

1 Like
16

Sorry for the late reply, was busy on other works.
I will try it ASAP and post the result.

17

The new one did the job, the explain has IndexCountScan now and the query takes 361ms now.
Thanks for the help.
So should I avoid any “or” in the index?

1 Like
18

You should use the one that is working now.

If you have a new use case requiring OR, you can post a separate topic, and we will repeat the process :slight_smile: