Slow query on indexed field

Couchbase Server
1

Hello,

We are running Couchbase 6.0 CE on 5 nodes with 26GB RAM & 4 Cores per node. We have approximately 31m documents in the cluster (of which approximately 67% of them are resident). We have the following index (as well as approximately 20 other indexes and Sync Gateway views)

CREATE INDEX `type` ON `sync_gateway`(`type`)

We are running the following query

SELECT type, COUNT(type)
FROM `sync_gateway`
WHERE type IS NOT MISSING
GROUP BY type;

Which never seems to complete, the query monitor suggests it completes after around 55minutes with 16 results. I have increased the index scan timeout to a huge number but I do not believe this should be necessary with a query such as the above. We have 5GB RAM set for the index service and 13GB set for the data service. We have no value in the max parallelism setting for the query preferences. The above queries explain is as follows:

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "IndexScan3",
        "covers": [
          "cover ((`sync_gateway`.`type`))",
          "cover ((meta(`sync_gateway`).`id`))"
        ],
        "index": "type",
        "index_id": "ff6f8fddfbb2d9ae",
        "index_projection": {
          "entry_keys": [
            0
          ]
        },
        "keyspace": "sync_gateway",
        "namespace": "default",
        "spans": [
          {
            "exact": true,
            "range": [
              {
                "inclusion": 1,
                "low": "null"
              }
            ]
          }
        ],
        "using": "gsi"
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "Filter",
              "condition": "(cover ((`sync_gateway`.`type`)) is not missing)"
            },
            {
              "#operator": "InitialGroup",
              "aggregates": [
                "count(cover ((`sync_gateway`.`type`)))"
              ],
              "group_keys": [
                "cover ((`sync_gateway`.`type`))"
              ]
            }
          ]
        }
      },
      {
        "#operator": "IntermediateGroup",
        "aggregates": [
          "count(cover ((`sync_gateway`.`type`)))"
        ],
        "group_keys": [
          "cover ((`sync_gateway`.`type`))"
        ]
      },
      {
        "#operator": "FinalGroup",
        "aggregates": [
          "count(cover ((`sync_gateway`.`type`)))"
        ],
        "group_keys": [
          "cover ((`sync_gateway`.`type`))"
        ]
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "InitialProject",
              "result_terms": [
                {
                  "expr": "cover ((`sync_gateway`.`type`))"
                },
                {
                  "expr": "count(cover ((`sync_gateway`.`type`)))"
                }
              ]
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      }
    ]
  },
  "text": "SELECT type, COUNT(type)\nFROM `sync_gateway`\nWHERE type IS NOT MISSING\nGROUP BY type;"
}

Does anyone have any thoughts on what might be going wrong? Something is clearly not right.

Update:

After getting the count of documents by type, I can see that 17m of the 30m (only 25m have a type property) documents have the same type. If the CB index is a B-Tree, this could potentially lead to a huge imbalance and degrade performance. Could this be the culprit? Or is the implementation such that when an index is rebuilt it reads the documents in the order in which they were written, meaning that the tree will be balanced as long as the insertion order was random?

2

The Plan looks right and query engine needs to get every item in the index (35m), group and aggregate it.
As you are using CE query engine on each node limits resources to 4 cores.

EE has few additional functionality which can make it faster https://blog.couchbase.com/understanding-index-grouping-aggregation-couchbase-n1ql-query/
https://blog.couchbase.com/couchbase-gsi-index-partitioning/

3

@vsr1 does this mean you are saying the 55minutes for this query to complete is expected?

4

The plan is right, You can enable profiling and check where the time spent. Also you are working on CE which limits resources.

If you need to count each type on the bucket may be map/reduce views might be faster