Medium DGM query on a 200K dataset takes about 1.3 mins to return results

1. Load default bucket with data ~ 200k
2. Data is in 40% DGM.
3. Issue single select from single client ( rest of the system is steady, ie no load, no other server ops.)
the query returns after 1.3 minutes.

select * from default where SequenceNumber < 1000 order by SequenceNumber limit 10
...
"status": "success",
"metrics":

{ "elapsedTime": "1m18.478329649s", "executionTime": "1m18.478111752s", "resultCount": 10, "resultSize": 132744 }

}

explain shows it is using the indexscan- SequenceNumber.

cbq> explain select * from default where SequenceNumber < 1000 order by SequenceNumber limit 10;
{
"requestID": "3b18abcb-269a-4011-b365-5230fab0b078",
"signature": "json",
"results": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan",
"index": "i_seq",
"keyspace": "default",
"limit": 9.223372036854776e+18,
"namespace": "default",
"spans": [
{
"Range":

{ "High": [ "1000" ], "Inclusion": 0, "Low": null }

,
"Seek": null
}
],
"using": "gsi"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [

{ "#operator": "Fetch", "keyspace": "default", "namespace": "default" }

,

{ "#operator": "Filter", "condition": "((`default`.`SequenceNumber`) \u003c 1000)" }

,
{
"#operator": "InitialProject",
"result_terms": [

{ "star": true }

]
}
]
}
}
]
},
{
"#operator": "Order",
"sort_terms": [

{ "expr": "(`default`.`SequenceNumber`)" }

]
},

{ "#operator": "Limit", "expr": "10" }

,
{
"#operator": "Parallel",
"~child":

{ "#operator": "FinalProject" }

}
]
}
],
"status": "success",
"metrics":

{ "elapsedTime": "298.423177ms", "executionTime": "247.471618ms", "resultCount": 1, "resultSize": 2883 }

}

Successive issues of the exact same select stmt, returns in exact same amount of time.

Is the query result for frequently used dataset cached with current strucutre?

The resident ratio rises to 90% ( screenshot spikes) and then drops back to 40% , since the existing dataset is almost equal to the high water mark of the bucket.

• How can the user tune up the system for query latency if the bucket is in this state?