N1QL DISTINCT _type query Performance

SQL++
1

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

2

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