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!