How to speedup n1ql query - what to scale (index, query, data)?

Couchbase Server
1

Currently using DP 4.5

Using 3 VMs each running 4cores/4gb ram and each running different service(data, index, query) all in a 7200rpm drive. Currently I have 1M test data on it. I want to know if this is an acceptable result from n1ql testing.

Index created:
CREATE INDEX default_id_idx ON default(meta().id)
CREATE INDEX default_tags_idx ON default(ARRAY d.tags FOR d IN default END)

SELECT meta().id as Key FROM default WHERE meta().id LIKE ‘Application_%’ AND ANY tag IN tags SATISFIES tag IN[‘tag1’, ‘tag9’] END

ran this 10x and its a consistent with the data below
Time elapsed: 32865 ms
Message: Success
Result Row(s): 166666

Another question is I want this query to be faster than 32000ms. Should I add another box of index, query, or data? I tried adding more cpus to the query service and it didnt helped speeding it up.

2

Hi Chicri,
as a first step, can you try with the array indexes feature in 4.5 DP. If possible try with 4.5 Beta release, which has more enhancements to array indexing. With this, you shouldn’t need the primary index.

// in 4.5 beta – Creates Covering Array Index on elements of the array 'tags’
CREATE INDEX default_tags_idx
ON default(DISTINCT ARRAY tag FOR tag IN tags END, tags);

// in 4.5 DP – Creates Array Index on elements of the array 'tags’
CREATE INDEX default_tags_idx
ON default(DISTINCT ARRAY tag FOR tag IN tags END);

// check explain output, this SELECT should use the above array-index
SELECT meta().id as Key FROM default
WHERE meta().id LIKE 'Application_%'
AND ANY tag IN tags SATISFIES tag IN [‘tag1’, ‘tag9’] END;

-Prasad