Couchbase GSI Indexes ( Non covering)

SQL++
1

Hello, In the following N1QL query, I have a GSI Index partially covering it

GSI Index ->

CREATE INDEX index00101 ONdatastore(filter1, filter2) WHERE ( type = "RP")

N1QL ->

select META(t).id as documentUID from `datastore` t 
where 
type = "RP" and filter1 = "JMT"  and (filter2 In ["D0000"]) 
 and (filter3 In ["AA13C"])

I have total 27 Million Documents, after apply predicates on type & filter1, filter2 using GSI, it comes down to 200,000 + documents.

In order to apply the third filter, Couchbase Query Nodes will have to fetch documents, apply additonal filter.

This query Is taking approximately 1 minute to execute. I am just wondering if there is any optimization I can do on the cluster to increase the Query response.

I don’t want to create a covering Index and would like Couchbase Query Nodes retrieve the documents and apply filter as this will be our use case in majority of cases.

Please recommend

Regards
Shiv

2

Requirement is not use covering and fetch the document and apply predicate, the optimization is limited.

  1. Use RAW so that you get array of META ids instead of Array of META ids objects
  2. 4.5.1 on wards set pretty=false so the size of the document will be less
    These few reduces result size improves little bit.

SELECT RAW META(t).id FROM …

3

Hello,

Thank you.

Does max parallelism will have any impact. I see none.
I set it to 4 from one, just wondering this will add any value.

Regards
Shiv

4

Fetch the data may be taking time and that is already parallelized. Also CE version Query service is limited to 4 CPUs. You can adjust few fetch parameter and see if that helps

For each query service(parameters are reset to default if query service is restarted)

Get the current parameters

curl -u <user>:<password> http://<hostname>:8093/admin/settings

Change parameters value to 2048 (higher the value more memory uses)

curl -u <user>:<password> http://<hostname>:8093/admin/settings -XPOST -d '{"pipeline-batch":2048,"pipeline-cap":2048}'

5

Nope. That did not help.

I updated default values
pipeline-batch from 16 to 2048 and pipeline-cap from 512 to 2048

I still have a similar response times

Regards
Shiv