Expecting IndexCountScan but getting IndexScan

SQL++
,
1

Hello,

After creating what I believe is a covering index, I run count queries and expect to see IndexCountScan being used for the operator when I look at the explain plan, but instead I see IndexScan. Here’s the index:

CREATE INDEX `group-fact-idx`
ON `mybucket`(`groupId`,`schemaVersion`)
WHERE ((`type` = "fact")
AND (`deleted` is missing))

Here’s the count query:

SELECT COUNT(mybucket) AS count
FROM `mybucket`
WHERE type = 'fact'
AND groupId = "my-group"
AND schemaVersion='1.0.0'
AND deleted IS MISSING

And here’s the explain plan for the query:

{
    "plan": {
        "#operator": "Sequence",
        "~children": [
            {
                "#operator": "IndexScan",
                "index": "group-fact-idx",
                "index_id": "5cac04fd6e5da369",
                "keyspace": "mybucket",
                "namespace": "default",
                "spans": [
                    {
                        "Range": {
                            "High": [
                                "\"my-group\"",
                                "\"1.0.0\""
                            ],
                            "Inclusion": 3,
                            "Low": [
                                "\"my-group\"",
                                "\"1.0.0\""
                            ]
                        }
                    }
                ],
                "using": "gsi"
            },
            {
                "#operator": "Fetch",
                "keyspace": "mybucket",
                "namespace": "default"
            },
            {
                "#operator": "Parallel",
                "~child": {
                    "#operator": "Sequence",
                    "~children": [
                        {
                            "#operator": "Filter",
                            "condition": "(((((`mybucket`.`type`) = \"fact\") and ((`mybucket`.`groupId`) = \"my-group\")) and ((`mybucket`.`schemaVersion`) = \"1.0.0\")) and ((`mybucket`.`deleted`) is missing))"
                        },
                        {
                            "#operator": "InitialGroup",
                            "aggregates": [
                                "count(`mybucket`)"
                            ],
                            "group_keys": []
                        }
                    ]
                }
            },
            {
                "#operator": "IntermediateGroup",
                "aggregates": [
                    "count(`mybucket`)"
                ],
                "group_keys": []
            },
            {
                "#operator": "FinalGroup",
                "aggregates": [
                    "count(`mybucket`)"
                ],
                "group_keys": []
            },
            {
                "#operator": "Parallel",
                "~child": {
                    "#operator": "Sequence",
                    "~children": [
                        {
                            "#operator": "InitialProject",
                            "result_terms": [
                                {
                                    "as": "count",
                                    "expr": "count(`mybucket`)"
                                }
                            ]
                        },
                        {
                            "#operator": "FinalProject"
                        }
                    ]
                }
            }
        ]
    },
    "text": "SELECT COUNT(mybucket) AS count\nFROM `mybucket`\nWHERE type = 'fact'\nAND groupId = \"my-group\"\nAND schemaVersion='1.0.0'\nAND deleted IS MISSING"
}

Our Couchbase version is 4.6.1-3652 Enterprise Edition (build-3652).

Is there something I’m not doing or doing wrong to try to get IndexCountScan?

Thanks!

2

How about this N1QL:

SELECT COUNT(groupId) AS count
FROM `mybucket `
WHERE type = 'fact'
AND groupId = "my-group"
AND schemaVersion='1.0.0'
AND deleted IS MISSING

FYI

and

3

To use IndexCountScan the COUNT expression needs to be constant or STAR or leading key of the index.
As you have bucket as count expression as the document will not be MISSING or NULL. You can replace that with constant as follows or you can use query suggested by @atom_yang.

SELECT COUNT(1) AS count
FROM `mybucket`
WHERE type = 'fact'
AND groupId = "my-group"
AND schemaVersion='1.0.0'
AND deleted IS MISSING;