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”]
}