Slow N1QL performance on small dataset using covering index

Couchbase Server
1

I am using Couchbase 6 community edition and my bucket has about 2 million documents of small sizes (< 5000 bytes)
Each document has a fields named country and I have an GSI on this field.
There are four unique values for this field, however, a query to get these unique values takes 8 to 10 seconds.
I am not sure why it is this slow.
my query is:

SELECT DISTINCT(country)
FROM test_bucket
USE INDEX(country_index USING GSI)
WHERE country IS NOT MISSING

The memory quota on this bucket is 50 GB.
and the machine has 40 cores.

I would like to ask what is the bottleneck here or what would cause a bottleneck in this situation

2

You have right index. As you have 2million country documents query engine needs to get all the 2 million entries from indexer and eliminate duplicates. Use request profiling described in page 341 https://blog.couchbase.com/n1ql-practical-guide-second-edition/

Also checkout technique described https://dzone.com/articles/count-amp-group-faster-using-n1ql

If you can use EE version you can use index aggregation as described by here https://blog.couchbase.com/understanding-index-grouping-aggregation-couchbase-n1ql-query/ by changing query to group by like below.

SELECT country
FROM test_bucket
USE INDEX(country_index USING GSI)
WHERE country IS NOT MISSING
GROUP BY country;