N1QL DISTINCT _type query Performance

I am trying to reverse engineer my Couch Base DB via Hackolade and i am running into some interesting issues.

One of the query the system tries to use is
SELECT DISTINCT _type FROM rets LIMIT 1000

if I run this on my Server it takes almost 14 minutes to complete. and i have the folowing Index
CREATE INDEX adv_type ON rets(_type) WITH { “defer_build”:true }

What am i missing here ok its 2 million docs but it shouldn’t take 14 minutes,

One thing I found is if I use the following below Query i get the 14 minutes down to 8 sec which is better but still quite slow

SELECT DISTINCT _type FROM rets where _type IS NOT MISSING LIMIT 1000

This is what causes my query to time out on Hackolade

Without WHERE clause it will use primary index and fetch the all the documents. Primary Uses for Couchbase Primary Index. | The Couchbase Blog

CREATE INDEX `adv_type` ON `rets` ( `_type` );
SELECT DISTINCT _type FROM ` rets ` where _type IS NOT MISSING LIMIT 1000

Above index still uses covering index and avoids Fetch. If you have many documents of same type still indexer .

If you have EE you can also try and see how it performs.

CREATE INDEX `adv_type` ON `rets` ( `_type` );
SELECT _type FROM ` rets ` where _type IS NOT MISSING  GROUP BY _type LIMIT 1000