i have the blow query which works but is extremely slow.
SELECT meta(tr).id as DocId,
tr.tracking_nbr, tr.subject,tr.click_count, tr.msg_count,tr.notify,tr.send_DateTime, tr.send_to, tr.send_cc, tr.send_bcc, tr.send_from
, MAX(t.time_date) AS last_event_DateTime
FROM Contacts AS tr
JOIN Contacts AS t ON t._type="tracker" AND t.tracking_nbr = tr.tracking_nbr
WHERE tr._type = "track_request"
GROUP BY tr
order by tr.send_DateTime DESC
i have an index on on the time_date on the _type = tracker but it takes forever to group these to return result.
here is the current execution plan
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "5.564µs"
},
"~children": [
{
"#operator": "Authorize",
"#stats": {
"#phaseSwitches": 3,
"execTime": "24.729µs",
"servTime": "3.907075ms"
},
"privileges": {
"List": [
{
"Target": "default:Contacts",
"Priv": 7
}
]
},
"~child": {
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "24.311µs"
},
"~children": [
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "6.409µs"
},
"~children": [
{
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 745,
"#phaseSwitches": 2983,
"execTime": "2.609613ms",
"kernTime": "155.187155ms",
"servTime": "3.74406ms"
},
"as": "tr",
"index": "Ottoman__type",
"index_id": "f9d810b05a296595",
"index_projection": {
"primary_key": true
},
"keyspace": "Contacts",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"track_request\"",
"inclusion": 3,
"low": "\"track_request\""
}
]
}
],
"using": "gsi",
"#time_normal": "00:00.0063",
"#time_absolute": 0.006353673
},
{
"#operator": "Fetch",
"#stats": {
"#itemsIn": 745,
"#itemsOut": 745,
"#phaseSwitches": 3077,
"execTime": "5.335188ms",
"kernTime": "247.307428ms",
"servTime": "402.683139ms"
},
"as": "tr",
"keyspace": "Contacts",
"namespace": "default",
"#time_normal": "00:00.4080",
"#time_absolute": 0.40801832699999996
},
{
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1,
"execTime": "3.151µs"
},
"~children": [
{
"#operator": "NestedLoopJoin",
"#stats": {
"#itemsIn": 745,
"#itemsOut": 2511,
"#phaseSwitches": 14517,
"execTime": "86.010295ms",
"kernTime": "1.808681151s"
},
"alias": "t",
"on_clause": "(((`t`.`_type`) = \"tracker\") and ((`t`.`tracking_nbr`) = (`tr`.`tracking_nbr`)))",
"~child": {
"#operator": "Sequence",
"#stats": {
"#phaseSwitches": 1490,
"execTime": "1.811519395s",
"kernTime": "599.67µs",
"state": "running"
},
"~children": [
{
"#operator": "IndexScan3",
"#stats": {
"#itemsOut": 2511,
"#phaseSwitches": 12279,
"execTime": "18.83646ms",
"kernTime": "2.239934ms",
"servTime": "1.2170539s"
},
"as": "t",
"index": "idx_tracking_nbr",
"index_id": "6e990b72952c3d76",
"index_projection": {
"primary_key": true
},
"keyspace": "Contacts",
"namespace": "default",
"nested_loop": true,
"spans": [
{
"exact": true,
"range": [
{
"high": "(`tr`.`tracking_nbr`)",
"inclusion": 3,
"low": "(`tr`.`tracking_nbr`)"
}
]
}
],
"using": "gsi"
},
{
"#operator": "Fetch",
"#stats": {
"#itemsIn": 2511,
"#itemsOut": 2511,
"#phaseSwitches": 13737,
"execTime": "45.346087ms",
"kernTime": "1.248910662s",
"servTime": "506.060629ms"
},
"as": "t",
"keyspace": "Contacts",
"namespace": "default",
"nested_loop": true
}
]
},
"#time_normal": "00:00.0860",
"#time_absolute": 0.086010295
},
{
"#operator": "Filter",
"#stats": {
"#itemsIn": 2511,
"#itemsOut": 2511,
"#phaseSwitches": 10047,
"execTime": "24.564601ms",
"kernTime": "2.007116548s"
},
"condition": "((`tr`.`_type`) = \"track_request\")",
"#time_normal": "00:00.0245",
"#time_absolute": 0.024564601
},
{
"#operator": "InitialGroup",
"#stats": {
"#itemsIn": 2511,
"#itemsOut": 692,
"#phaseSwitches": 6409,
"execTime": "2.595679176s",
"kernTime": "246.70408ms"
},
"aggregates": [
"max((`t`.`time_date`))"
],
"group_keys": [
"`tr`"
],
"#time_normal": "00:02.5956",
"#time_absolute": 2.595679176
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000003151
},
{
"#operator": "IntermediateGroup",
"#stats": {
"#itemsIn": 692,
"#itemsOut": 692,
"#phaseSwitches": 2771,
"execTime": "591.08752ms",
"kernTime": "2.737726612s"
},
"aggregates": [
"max((`t`.`time_date`))"
],
"group_keys": [
"`tr`"
],
"#time_normal": "00:00.5910",
"#time_absolute": 0.5910875200000001
},
{
"#operator": "FinalGroup",
"#stats": {
"#itemsIn": 692,
"#itemsOut": 692,
"#phaseSwitches": 2771,
"execTime": "341.441921ms",
"kernTime": "3.234417578s"
},
"aggregates": [
"max((`t`.`time_date`))"
],
"group_keys": [
"`tr`"
],
"#time_normal": "00:00.3414",
"#time_absolute": 0.341441921
},
{
"#operator": "InitialProject",
"#stats": {
"#itemsIn": 692,
"#itemsOut": 692,
"#phaseSwitches": 2771,
"execTime": "10.199883ms",
"kernTime": "3.573928625s"
},
"result_terms": [
{
"as": "DocId",
"expr": "(meta(`tr`).`id`)"
},
{
"expr": "(`tr`.`tracking_nbr`)"
},
{
"expr": "(`tr`.`subject`)"
},
{
"expr": "(`tr`.`click_count`)"
},
{
"expr": "(`tr`.`msg_count`)"
},
{
"expr": "(`tr`.`notify`)"
},
{
"expr": "(`tr`.`send_DateTime`)"
},
{
"expr": "(`tr`.`send_to`)"
},
{
"expr": "(`tr`.`send_cc`)"
},
{
"expr": "(`tr`.`send_bcc`)"
},
{
"expr": "(`tr`.`send_from`)"
},
{
"as": "last_event_DateTime",
"expr": "max((`t`.`time_date`))"
}
],
"#time_normal": "00:00.0101",
"#time_absolute": 0.010199883
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000006409
},
{
"#operator": "Order",
"#stats": {
"#itemsIn": 692,
"#itemsOut": 692,
"#phaseSwitches": 2081,
"execTime": "3.319223ms",
"kernTime": "3.584057615s"
},
"sort_terms": [
{
"desc": true,
"expr": "(`tr`.`send_DateTime`)"
}
],
"#time_normal": "00:00.0033",
"#time_absolute": 0.003319223
},
{
"#operator": "FinalProject",
"#stats": {
"#itemsIn": 692,
"#itemsOut": 692,
"#phaseSwitches": 2077,
"execTime": "1.202468ms",
"kernTime": "35.842761ms"
},
"#time_normal": "00:00.0012",
"#time_absolute": 0.0012024680000000002
}
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.000024311
},
"#time_normal": "00:00.0039",
"#time_absolute": 0.003931804
},
{
"#operator": "Stream",
"#stats": {
"#itemsIn": 692,
"#itemsOut": 692,
"#phaseSwitches": 2771,
"execTime": "275.9µs",
"kernTime": "3.6286241s"
},
"#time_normal": "00:00.0002",
"#time_absolute": 0.0002759
}
],
"~versions": [
"2.0.0-N1QL",
"6.0.0-1693-enterprise"
],
"#time_normal": "00:00.0000",
"#time_absolute": 0.0000055640000000000004
}
