Very low performance of an indexed N1QL query

SQL++
1

We are using cb4 community edition and we are experiencing very slow responses. CB is the only thing on the server, we are using 1 node for indexing and storage since we are still in development mode.
Our issue is the following:
We have a bucket with 22000 items
We have a GSI index on it that looks like this
"indexes": {
“datastore_id”: “http://127.0.0.1:8091”,
“id”: “a1a3d6a0fd8add4d”,
“index_key”: [
"type",
"code"
],
“keyspace_id”: “zipt”,
“name”: “rate_code_index3”,
“namespace_id”: “default”,
“state”: “online”,
“using”: “gsi”
}

So basically a GSI index on type and rate. There is 21000 documents that would match this query in the bucket.

The query
SELECT * FROM zipt USE INDEX(rate_code_index3 USING GSI) WHERE (type = “rate” and code =577613) limit 1;

is currently executing in a whooping 3.5s which I think i very slow.

This is the explain of the query

{
“requestID”: “14be6a46-e9e9-48da-8f1f-24fb697ab31e”,
“signature”: “json”,
“results”: [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“index”: “rate_code_index3”,
“keyspace”: “zipt”,
“limit”: 9.223372036854776e+18,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: [
"“rate\u0009"”
],
“Inclusion”: 1,
“Low”: [
"“rate”"
]
},
“Seek”: null
}
],
“using”: “gsi”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Fetch”,
“keyspace”: “zipt”,
“namespace”: “default”
},
{
"#operator": “Filter”,
“condition”: “(((zipt.type) = “rate”) and ((zipt.code) = 577613))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“star”: true
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
{
"#operator": “Limit”,
“expr”: “1”
}
]
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “10.385487ms”,
“executionTime”: “10.164681ms”,
“resultCount”: 1,
“resultSize”: 2712
}
}

Are we doing anything wrong?
The bucket also has a primary index
Is it supposed to be this slow? This is kind of unusable in this form so if you could help in any way it would be much appreciated!

Thank you

2

Instead of running the above, what happens if you just run:

SELECT * FROM zipt WHERE type = 'rate' AND code = 577613 LIMIT 1;

Let me know if that changes the times at all.

Best,

3

Not much, this was just a test iteration that should for sure use this
index. I have tried all combinations, gsi/view indexes, only code only
rate, both code and rate.
The query you mentioned just uses a view based index that i created before.
I tried to leave only one index available and force one like in the example
i posted.

All times are 3 seconds or more, maybe 10% of the time it falls to 2.5 but
this is still too slow for that bucket size in my opinion.

Do you have any idea or maybe have a suggestion as to what I could try next?
Are these timings bad or expected for this bucket/document size?
Does the explain look “heathly”?

4

Perhaps your data, index, or both are not fully fitting in memory. These numbers are atypical and nowhere close to what we see. Are you on Windows, Linux, or Mac?

5

Since this is still in development the node has about 2gb ram reserved. No
memory alerts have occured and everything should fit on first glance. This
is hosted on centos7

How can i check if everything you mention is in memory?
Is there something else i could check or try?

Thanks for the help

6

@tony_mrakovcic any news with this issue? I have the same problem. Everything is indexed but poor perfomance when running queries.

7

Yes. Can you upgrade to 4.1, and then change SELECT * to select specific fields. You should create a covering index that covers all the data needed by your query. We can help with that after you upgrade. Thanks for your patience.