Group Query is taking way to long

SQL++
1

I have a bucket which stores Notifications i get from Microsoft Graph API are they are either of type contact or message.
So i created a field called _type which is either contact or message as well as an index on it.

if i i run the following query

select count(*), _type from mail_store 
group by _type

it takes like 20 sec, but if i run the below is the execution plan

image
image982×127 13.1 KB

if i go and put a filter on the _type of IS NOT NULL

select count(*), _type from mail_store 
where _type  IS NOT NULL
group by _type

it takes only 87ms, as a side note all documents have the _type field so there is no condition where _type IS NULL below is the execution plan.

image
image997×167 15.4 KB

is there a reason for that kind of behavior and if so what is the rule of thumb, also it seems if there is a group clause it fails to pick the best avail index. On group without where

Index Currently Used

CREATE PRIMARY INDEX undefined ON mail_store

on group by withe where clause

Index Currently Used

CREATE INDEX adv_type ON mail_store(_type)

in both cases the advise says Existing indexes are sufficient.
So it seems without where clause the system is not able to pick the best index

2

The behavior is right.
Couchbase Index will not index the document if the leading index key is MISSING in the document.
Query without where clause must produce all the documents in the bucket. So it uses primary index, which in turns fetches the document.
With where _type IS NOT NULL it will pick adv_type index and does covered query.

GROUP BY _type
MISSING will be one group , NULL one group, Each different value as different group.
If you are data has _type all the time or not interested MISSING , Add WHERE _type IS NOT MISSING