N1QL Query Performance. What is wrong?


#1

Hi. I test couchbase on my PC and I just not understand what I do wrong…

So I have bucket with Name Devices.
There 250.000 documents.

The structure of document is:
{
“device”: {
“IsSensor”: true,
“HardwareID”: 123456,
“SystemName”: “Lorem In”,
“Type”: 29,
“Family”: 16,
“SyncType”: 1,
“LinkedObjectId”: “00000000-0000-0000-0000-000000000000”,
“LocationX”: 0,
“LocationY”: 0,
“ImplementationClassType”: 7,
“HasLocation”: false,
“IsFieldDevice”: true,
“Id”: “bb09c32d-eeba-4e8c-9162-6216ebaad929”,
“Name”: “ohtdy30u.oxb”,
“ParentObjectId”: “00000000-0000-0000-0000-000000000000”,
“ComponentManagerId”: “f6b307fe-b4a7-458f-ab24-41c36609f201”,
“ImportanceLevel”: 1
}
}

(The device is dynamic object and properties is dynamic)

My code (C#)
is:

string query = String.Format("SELECT device FROM Devices where device.ComponentManagerId == '{0}'", componentId);
var queryRequest = new QueryRequest().Statement(query).Metrics(false);
queryRequest.MaxServerParallelism(10);

var result = await _bucket.QueryAsync<dynamic>(queryRequest);

(Example: SELECT device FROM Devices where device.ComponentManagerId == 'f6b307fe-b4a7-458f-ab24-41c36609f201')

This query take 15-20 SECONDS!!!
(Size about 190MB)
Indexes:
CREATE PRIMARY INDEX DeviceIndex ON Devices
CREATE INDEX componentIdIndx ON Devices(ComponentManagerId)

Explain:
{
“plan”: {
#operator”: “Sequence”,
“~children”: [
{
#operator”: “PrimaryScan3”,
“index”: “DeviceIndex”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “Devices”,
“namespace”: “default”,
“using”: “gsi”
},
{
#operator”: “Fetch”,
“keyspace”: “Devices”,
“namespace”: “default”
},
{
#operator”: “Parallel”,
“~child”: {
#operator”: “Sequence”,
“~children”: [
{
#operator”: “Filter”,
“condition”: “(((Devices.device).ComponentManagerId) = “f6b307fe-b4a7-458f-ab24-41c36609f201”)”
},
{
#operator”: “InitialProject”,
“result_terms”: [
{
“expr”: “(Devices.device)”
}
]
},
{
#operator”: “FinalProject”
}
]
}
}
]
},
“text”: “SELECT device FROM Devices where device.ComponentManagerId == ‘f6b307fe-b4a7-458f-ab24-41c36609f201’”
}


#2

CREATE INDEX componentIdIndx ON Devices ( device.ComponentManagerId )