N1Ql execution is particularly slow when searching for data accurately using array indexes

query

#1

Create an array index, use it in the query and find all the data of a user accurately. The execution efficiency of N1Ql is especially low.
The following is the code to create an array index.

CREATE INDEX items_userId on GTSB(DISTINCT ARRAY [v.userId] FOR v IN items END)
where type=‘Order’

Below is the Plan Text code that executes the results.

{
#operator”: “Sequence”,
#stats”: {
#phaseSwitches”: 1
},
“~children”: [{
#operator”: “Authorize”,
#stats”: {
#phaseSwitches”: 3,
“servTime”: “5.0008ms”
},
“privileges”: {
“List”: [{
“Target”: “default:GTSB”,
“Priv”: 7
}]
},
“~child”: {
#operator”: “Sequence”,
#stats”: {
#phaseSwitches”: 1
},
“~children”: [{
#operator”: “Sequence”,
#stats”: {
#phaseSwitches”: 2,
“state”: “running”
},
“~children”: [{
#operator”: “IndexScan3”,
#stats”: {
#itemsOut”: 99568,
#phaseSwitches”: 398275,
“execTime”: “458.8363ms”,
“kernTime”: “25.8172693s”,
“servTime”: “242.8628ms”
},
“as”: “Extent1”,
“index”: “typekeyindex”,
“index_id”: “20ea82c04ab9a83e”,
“index_projection”: {
“primary_key”: true
},
“keyspace”: “GTSB”,
“namespace”: “default”,
“spans”: [{
“exact”: true,
“range”: [{
“high”: ““Order””,
“inclusion”: 3,
“low”: ““Order””
}]
}],
“using”: “gsi”,
#time_normal”: “00:00.7016”,
#time_absolute”: 0.7016990999999999
}, {
#operator”: “Fetch”,
#stats”: {
#itemsIn”: 99568,
#itemsOut”: 99568,
#phaseSwitches”: 410721,
“execTime”: “462.824ms”,
“kernTime”: “161.976ms”,
“servTime”: “26.0251581s”
},
“as”: “Extent1”,
“keyspace”: “GTSB”,
“namespace”: “default”,
#time_normal”: “00:26.4879”,
#time_absolute”: 26.4879821
}, {
#operator”: “Sequence”,
#stats”: {
#phaseSwitches”: 2,
“execTime”: “999.3µs”,
“state”: “running”
},
“~children”: [{
#operator”: “Filter”,
#stats”: {
#itemsIn”: 99568,
#itemsOut”: 165,
#phaseSwitches”: 199469,
“execTime”: “17.6681167s”,
“kernTime”: “8.982841s”
},
“condition”: “(((Extent1.type) = “Order”) and any v in (Extent1.items) satisfies ((v.userId) = “userId394919424”) end)”,
#time_normal”: “00:17.6681”,
#time_absolute”: 17.6681167
}, {
#operator”: “InitialProject”,
#stats”: {
#itemsIn”: 165,
#itemsOut”: 165,
#phaseSwitches”: 502,
“execTime”: “999.6µs”,
“kernTime”: “26.6489604s”
},
“raw”: true,
“result_terms”: [{
“expr”: “Extent1
}],
#time_normal”: “00:00.0009”,
#time_absolute”: 0.0009996
}, {
#operator”: “FinalProject”,
#stats”: {
#itemsIn”: 165,
#itemsOut”: 165,
#phaseSwitches”: 331
}
}],
#time_normal”: “00:00.0009”,
#time_absolute”: 0.0009993
}]
}, {
#operator”: “Limit”,
#stats”: {
#itemsIn”: 165,
#itemsOut”: 165,
#phaseSwitches”: 496,
“kernTime”: “997.7µs”
},
“expr”: “200”
}]
},
#time_normal”: “00:00.0050”,
#time_absolute”: 0.0050008
}, {
#operator”: “Stream”,
#stats”: {
#itemsIn”: 165,
#itemsOut”: 165,
#phaseSwitches”: 663,
“kernTime”: “26.6559585s”
}
}],
“~versions”: [“2.0.0-N1QL”, “5.5.0-2958-enterprise”]
}


#2

Use the following index. Check EXPLAIN and see if it using right index. If still have issue post the query.

CREATE INDEX items_userId on GTSB(DISTINCT ARRAY v.userId FOR v IN items END)
where type="Order";

#3

Yes.Query efficiency is still very low. The results will be available in half a minute.


#4

Post Query, index definition and EXPLAIN