Why My N1QL Query With Index Is So Slowly?

SQL++
1

My English is not good, please forgive me!

I create a new Buckets and insert abount 700,000 records.
Then I create 2 indexes in the buckets as follow:

CREATE PRIMARY INDEX ON did_devices USING GSI;
CREATE INDEX idx_system on did_devices (SYSTEM);

then, I get a query as follow:
cbq> select * from did_devices where SYSTEM = IOS1 limit 3;
{
“requestID”: “b061fcc2-c13d-421a-987e-7f2355830f61”,
“signature”: {
"": ""
},
“results”: [
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “1m18.017465565s”,
“executionTime”: “1m18.01730557s”,
“resultCount”: 0,
“resultSize”: 0
}
}

show explain:
cbq> explain select * from did_devices where SYSTEM = IOS1 limit 3;
{
“requestID”: “64e6ecfe-3ba0-4991-8569-f04708cc5fdd”,
“signature”: “json”,
“results”: [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “Sequence”,
"~children": [
{
"#operator": “IndexScan”,
“index”: “idx_system”,
“keyspace”: “did_devices”,
“limit”: 9.223372036854776e+18,
“namespace”: “default”,
“spans”: [
{
“Range”: {
“High”: null,
“Inclusion”: 1,
“Low”: [
“null”
]
},
“Seek”: null
}
],
“using”: “gsi”
},
{
"#operator": “Parallel”,
"~child": {
"#operator": “Sequence”,
"~children": [
{
"#operator": “Fetch”,
“keyspace”: “did_devices”,
“namespace”: “default”
},
{
"#operator": “Filter”,
“condition”: “((did_devices.SYSTEM) = (did_devices.IOS1))”
},
{
"#operator": “InitialProject”,
“result_terms”: [
{
“star”: true
}
]
},
{
"#operator": “FinalProject”
}
]
}
}
]
},
{
"#operator": “Limit”,
“expr”: “3”
}
]
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “3.90946ms”,
“executionTime”: “3.783982ms”,
“resultCount”: 1,
“resultSize”: 2605
}
}

I want to know why my query cost more then 1 minute while using index?
It is looks like the index is work!

thank you very much!

2

What version of Couchbase Server do you use?
How about this N1QL cost:

select * from did_devices where SYSTEM is not missing and SYSTEM = IOS1 limit 3;
4

Hi, atom_yang
thank you very much!

server version: 4.0.0-4051 Community Edition

it is very fast by add “SYSTEM is not missing”, but I want to know why It is work?
I did not find it in api document.

cbq> select count(1) from did_devices where SYSTEM is not missing and SYSTEM = ‘IOS123’;
{
“requestID”: “72f7c2e0-c817-43f4-9b2a-8839084940ed”,
“signature”: {
"$1": “number”
},
“results”: [
{
"$1": 0
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “9.499669ms”,
“executionTime”: “9.417955ms”,
“resultCount”: 1,
“resultSize”: 31
}
}

5

In your earlier queries, you were missing the quotes around ‘IOS123’. The quotes are needed.

6

See also.

7

Hi，
geraldss，thank you very much!

quotes just missing when I copy queries to here!

I found a very strange phenomenon, after performing @atom_yang‘s queries, I tried again my previous inquiries and found become very fast, why there is this phenomenon it?

cbq> select * from did_devices where SYSTEM = ‘IOS1’ limit 3;
{
“requestID”: “167606b5-2a15-422d-b311-29b0d74f7d33”,
“signature”: {
"": ""
},
“results”: [
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “4.98071ms”,
“executionTime”: “4.8848ms”,
“resultCount”: 0,
“resultSize”: 0
}
}

8

Hi @king, hard to know. If you see the slowdown again, we can troubleshoot.

10

Hi @geraldss,
I delete the whole Buckets and rebuild it. insert same data and create index.
But still can not reproduce, This is really strange

cbq> select count(*) from did_devices where SYSTEM = ‘IOS111’;
{
“requestID”: “50a539eb-e18e-47a8-8279-e212447f6e35”,
“signature”: {
"$1": “number”
},
“results”: [
{
"$1": 0
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “3.975754ms”,
“executionTime”: “3.881018ms”,
“resultCount”: 1,
“resultSize”: 31
}
}

11

Don’t worry about it. Let us know if it happens again.