Hi Team,
I am executing below query which is taking near about 11 sec to execute.
SELECT
*
FROM `mybucket`
WHERE type="history" AND ( event IN ["modified","rejected","approved","created"] )
ORDER BY timestamp desc
LIMIT 20
OFFSET 0;
I have added index CREATE INDEX adv_event_type_test ON `mybucket`(`event`,`timestamp` DESC) WHERE (`type` = 'history');
Refer the below output of Plan text tab:
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "5.885µs"
},
"~children": [
{
"#operator": "Authorize",
"#stats": {
"#phaseSwitches": 3,
"execTime": "9.775µs",
"servTime": "16.872617ms"
},
"privileges": {
"List": [
{
"Target": "default:mybucket",
"Priv": 7
}
]
},
"~child": {
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "5.09µs"
},
"~children": [
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "3.37µs"
},
"~children": [
{
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 93784,
"#phaseSwitches": 375139,
"execTime": "211.108256ms",
"kernTime": "10.421695486s",
"servTime": "37.78984ms"
},
"index": "adv_event_type_test",
"index_id": "989a75359b496459",
"index_projection": {
"primary_key": true
},
"keyspace": "mybucket",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"approved\"",
"inclusion": 3,
"low": "\"approved\""
}
]
},
{
"exact": true,
"range": [
{
"high": "\"created\"",
"inclusion": 3,
"low": "\"created\""
}
]
},
{
"exact": true,
"range": [
{
"high": "\"modified\"",
"inclusion": 3,
"low": "\"modified\""
}
]
},
{
"exact": true,
"range": [
{
"high": "\"rejected\"",
"inclusion": 3,
"low": "\"rejected\""
}
]
}
],
"using": "gsi",
"#time_normal": "00:00.248",
"#time_absolute": 0.248898096
},
{
"#operator": "Fetch",
"#stats": {
"#itemsIn": 93784,
"#itemsOut": 93784,
"#phaseSwitches": 386863,
"execTime": "265.658084ms",
"kernTime": "1.535227126s",
"servTime": "8.907075461s"
},
"keyspace": "mybucket",
"namespace": "default",
"#time_normal": "00:09.172",
"#time_absolute": 9.172733545
},
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "2.175µs"
},
"~children": [
{
"#operator": "Filter",
"#stats": {
"#itemsIn": 93784,
"#itemsOut": 93784,
"#phaseSwitches": 375139,
"execTime": "6.603747692s",
"kernTime": "4.104305653s"
},
"condition": "(((`mybucket`.`type`) = \"history\") and ((`mybucket`.`event`) in [\"modified\", \"rejected\", \"approved\", \"created\"]))",
"#time_normal": "00:06.603",
"#time_absolute": 6.603747692
},
{
"#operator": "InitialProject",
"#stats": {
"#itemsIn": 93784,
"#itemsOut": 93784,
"#phaseSwitches": 375139,
"execTime": "76.940801ms",
"kernTime": "10.631129494s"
},
"result_terms": [
{
"expr": "self",
"star": true
}
],
"#time_normal": "00:00.076",
"#time_absolute": 0.07694080099999999
}
],
"#time_normal": "00:00.000",
"#time_absolute": 0.000002175
}
],
"#time_normal": "00:00.000",
"#time_absolute": 0.00000337
},
{
"#operator": "Order",
"#stats": {
"#itemsIn": 93784,
"#itemsOut": 20,
"#phaseSwitches": 187593,
"execTime": "659.574576ms",
"kernTime": "10.048613624s"
},
"limit": "20",
"sort_terms": [
{
"desc": true,
"expr": "(`mybucket`.`timestamp`)"
}
],
"#time_normal": "00:00.659",
"#time_absolute": 0.659574576
},
{
"#operator": "Limit",
"#stats": {
"#itemsIn": 20,
"#itemsOut": 20,
"#phaseSwitches": 41,
"execTime": "12.957µs"
},
"expr": "20",
"#time_normal": "00:00.000",
"#time_absolute": 0.000012957000000000001
},
{
"#operator": "FinalProject",
"#stats": {
"#itemsIn": 20,
"#itemsOut": 20,
"#phaseSwitches": 61,
"execTime": "21.482µs",
"kernTime": "2.997041ms"
},
"#time_normal": "00:00.000",
"#time_absolute": 0.000021482
}
],
"#time_normal": "00:00.000",
"#time_absolute": 0.0000050899999999999995
},
"#time_normal": "00:00.016",
"#time_absolute": 0.016882392000000003
},
{
"#operator": "Stream",
"#stats": {
"#itemsIn": 20,
"#itemsOut": 20,
"#phaseSwitches": 43,
"execTime": "2.96426ms",
"kernTime": "10.725225582s"
},
"#time_normal": "00:00.002",
"#time_absolute": 0.00296426
}
],
"~versions": [
"6.5.0-N1QL",
"6.5.1-6299-enterprise"
],
"#time_normal": "00:00.000",
"#time_absolute": 0.0000058849999999999996
}
When I am removing ORDER BY clause it is executing within 300ms.
Is there any way to optimised this query so that this will execute within 300ms without removing order by clause., any help is appreciated.
Thanks in Advance!
Query has to produce all qualified 90K documents to produce 20 because of sort. Last item appear in first in the sorted list.
The following might be faster but depends on selectivity of the filter event
CREATE INDEX adv_event_type_test ON `mybucket`(`timestamp` DESC, event) WHERE (`type` = 'history');
SELECT *
FROM `mybucket`
WHERE type="history" AND ( event IN ["modified","rejected","approved","created"] ) AND timestamp IS NOT NULL
ORDER BY timestamp desc
LIMIT 20
OFFSET 0;
Another option (if the above isn’t fast enough) is with the same index as @vsr1 suggests:
SELECT *
FROM mybucket
USE KEYS (SELECT RAW meta().id
FROM mybucket
WHERE type="history" AND ( event IN ["modified","rejected","approved","created"] )
AND timestamp IS NOT NULL
ORDER BY timestamp DESC
LIMIT 20
OFFSET 0
)
ORDER BY timestamp DESC
;
Since then at most 20 documents ought to be fetched.
Thanks for your reply…This worked exactly what I have expected, executed within a 11 ms. It will be great if you can explain more about why changing the field order in index affects the performance so much.
Thanks for the reply. This solution also working but taking 400 ms for the execution. Can you please provide me some example if I want to use this with JOIN query on some other bucket say ‘mybucket2’ which has ‘instanceId’ as key referenced with id of ‘mybucket’
You could join the the outer SELECT just as you normally would. However @vsr1’s approach is typically simpler and (as you see) performs better, and would be the recommended route; mine was really just to highlight why your original was slow (number of documents to be fetched).
In @vsr1’s approach, as the index can provide the order, the limit is pushed down and only 20 keys are produced from the index scan. The index can provide the order as the leading key is the order so all results from an index scan are in that order; if it is a trailing key (i.e. not sorting by some leading keys) then the overall index results are not in that order so all results have to be returned and sorting has to be performed on them before the limit can be applied.
As the index scan only produced 20 keys, only 20 documents are fetched, which is significantly faster than fetching 93k as was necessary in your original.
My example was really to highlight the 20-keys only being returned by breaking down the key and document fetches into separate SELECTs. It was probably unnecessary of me to propose it, but I hoped it might highlight this for you (and I hope it hasn’t confused things). It is naturally going to be the slower approach since it is adding overhead compared to @vsr1’s approach.
In @vsr1’s approach, if your join was solely to augment the results with additional fields from another bucket then it should be possible to just join normally with an outer join. As soon as you impose filtering (or inner joins) the matching result-set differs. In this case the limit could not be pushed down to the index, all results would have to be returned in order to be joined to the other bucket (which may cause some to be discarded), sorted (because of eventual consistency between index and data) and the limit applied (i.e. it wouldn’t be able to be all handled within the index scan).
(In my approach, if you wanted these 20 documents from this bucket only/always and have a result set that could be smaller, you could apply the join in the outer SELECT; effectively the inner SELECT makes this bucket be just the 20 selected keys.)
HTH.