Hi I am using version 4.6.1 and 5.0-beta. The ORDER BY is really slow
The query is (entire bucket have around 120000 items)
SELECT default.* FROM default WHERE AInt IS NOT MISSING ORDER BY AInt DESC LIMIT 50
Aint
have index:
CREATE INDEX neg_int ON default(-AInt, AInt)
The query explain is: (on ver 5.0-beta)
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 2,
"execTime": "1.461µs",
"kernTime": "4.021624433s"
},
"~children": [
{
"#operator": "Authorize",
"#stats": {
"#phaseSwitches": 4,
"execTime": "5.286µs",
"kernTime": "4.020407932s",
"servTime": "1.041799ms"
},
"privileges": {
"List": [
{
"Target": "default:default",
"Priv": 7
}
]
},
"~child": {
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 3,
"execTime": "2.406µs",
"kernTime": "4.020397465s"
},
"~children": [
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 2,
"execTime": "2.136µs",
"kernTime": "4.019555904s"
},
"~children": [
{
"#operator": "PrimaryScan",
"#stats": {
"#itemsOut": 129658,
"#phaseSwitches": 518635,
"execTime": "255.441156ms",
"kernTime": "3.692260758s",
"servTime": "52.710069ms"
},
"index": "primary_index",
"keyspace": "default",
"namespace": "default",
"using": "gsi",
"#time_normal": "00:00.3081",
"#time_absolute": 0.30815122500000003
},
{
"#operator": "Fetch",
"#stats": {
"#itemsIn": 129658,
"#itemsOut": 129658,
"#phaseSwitches": 519147,
"execTime": "257.211659ms",
"kernTime": "115.658301ms",
"servTime": "3.644560297s"
},
"keyspace": "default",
"namespace": "default",
"#time_normal": "00:03.9017",
"#time_absolute": 3.901771956
},
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 5,
"execTime": "1.43µs",
"kernTime": "4.019546983s"
},
"~children": [
{
"#operator": "Filter",
"#stats": {
"#itemsIn": 129658,
"#itemsOut": 129658,
"#phaseSwitches": 518637,
"execTime": "722.703564ms",
"kernTime": "3.295918863s"
},
"condition": "((`default`.`AInt`) is not missing)",
"#time_normal": "00:00.7227",
"#time_absolute": 0.722703564
},
{
"#operator": "InitialProject",
"#stats": {
"#itemsIn": 129658,
"#itemsOut": 129658,
"#phaseSwitches": 518639,
"execTime": "1.953823211s",
"kernTime": "2.065715655s"
},
"result_terms": [
{
"expr": "`default`",
"star": true
}
],
"#time_normal": "00:01.9538",
"#time_absolute": 1.953823211
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.0000014299999999999999
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000002136
},
{
"#operator": "Order",
"#stats": {
"#itemsIn": 129658,
"#itemsOut": 50,
"#phaseSwitches": 259421,
"execTime": "251.268752ms",
"kernTime": "3.768424789s"
},
"limit": "50",
"sort_terms": [
{
"desc": true,
"expr": "(`default`.`AInt`)"
}
],
"#time_normal": "00:00.2512",
"#time_absolute": 0.251268752
},
{
"#operator": "Limit",
"#stats": {
"#itemsIn": 50,
"#itemsOut": 50,
"#phaseSwitches": 205,
"execTime": "7.979µs",
"kernTime": "4.019723827s"
},
"expr": "50",
"#time_normal": "00:00.0000",
"#time_absolute": 0.000007979
},
{
"#operator": "FinalProject",
"#stats": {
"#itemsIn": 50,
"#itemsOut": 50,
"#phaseSwitches": 207,
"execTime": "30.294µs",
"kernTime": "4.020352787s"
},
"#time_normal": "00:00.0000",
"#time_absolute": 0.000030294
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.0000024060000000000003
},
"#time_normal": "00:00.0010",
"#time_absolute": 0.001047085
},
{
"#operator": "Stream",
"#stats": {
"#itemsIn": 50,
"#itemsOut": 50,
"#phaseSwitches": 107,
"execTime": "5.888µs",
"kernTime": "4.021612898s"
},
"#time_normal": "00:00.0000",
"#time_absolute": 0.000005888
}
],
"~versions": [
"1.7.0-N1QL",
"5.0.0-2873-enterprise"
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000001461
}
Since ORDER BY is one of very basic operation and will be used highly active in production. How can I boost performance on this?