Issue with adaptive index usage

Couchbase Server
1

I have created an adaptive index on a bucket Binaries and 4 attributes as mentioned below in the query:

CREATE INDEX adap_idx1 ON Binaries(DISTINCT PAIRS({_id,_type,territory,type}))
WHERE _type = “ChecklistMetadata”;

But somehow this index is not getting used while querying.

Previously, we had individual indexes for all the above attributes. For instance: CREATE INDEX idx_id ON Binaries(_id) where _type=“ChecklistMetadata”;

Currently, we are trying to club all the index using adaptive index syntax, but we are failing to do so.

Please let is know whats the issue with the adaptive index we are creating. And what changes is required to make that work.

2

Post your queries also.

3

Even when we are querying a simple query set, we are getting error as no index available.

select * from Binaries where _id=“1678990” limit 10;

We are getting the result when we are using the index idx_id but as soon as we drop the index and use index adap_idx1, we are facing the above error.

4

adap_idx1 is partial adaptive index. Your query needs where _id=“1678990” AND _type = “ChecklistMetadata”

5

@vsr1

Even when we are creating index separately for _id where _type=“ChecklistMetadata”:
CREATE INDEX adap_idx_id1 ON Binaries(DISTINCT PAIRS(_id)) WHERE _type = “ChecklistMetadata”;
Then also we are facing the same problem. Its not able to fetch the result.

Please suggest when to use adaptive index and if we cant use in this case then why?

6 
CREATE INDEX  `adap_idx_id1`  ON  `Binaries` (DISTINCT PAIRS({_id,_type})) WHERE _type = "ChecklistMetadata";
select * from Binaries where _id="1678990" AND   _type = "ChecklistMetadata" limit 10;

https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/adaptive-indexing.html

7

@vsr1

This particular query is not getting hit and is taking a lot of time. I have modified the index and added the where clause attribute as well.

8

Post explain. If you concern with performance use regular gsi index https://blog.couchbase.com/create-right-index-get-right-performance/