It’s still around 8 seconds… Here’s the EXPLAIN
:
[
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan",
"covers": [
"cover ((`submission`.`status`))",
"cover ((\"Mission::\" || (`submission`.`missionId`)))",
"cover (ifmissingornull((`submission`.`offer`), 999))",
"cover ((`submission`.`highValueTarget`))",
"cover ((meta(`submission`).`id`))"
],
"filter_covers": {
"cover ((`submission`.`docType`))": "Submission"
},
"index": "idx_missionId_status_offer_highValueTarget_4_submission_dev",
"index_id": "e92c9f62597c48ca",
"keyspace": "brandbassador_dev",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"successor(\"approved\")"
],
"Inclusion": 1,
"Low": [
"\"approved\""
]
}
},
{
"Range": {
"High": [
"successor(\"ongoing\")"
],
"Inclusion": 1,
"Low": [
"\"ongoing\""
]
}
},
{
"Range": {
"High": [
"successor(\"pending\")"
],
"Inclusion": 1,
"Low": [
"\"pending\""
]
}
},
{
"Range": {
"High": [
"successor(\"rejected\")"
],
"Inclusion": 1,
"Low": [
"\"rejected\""
]
}
},
{
"Range": {
"High": [
"successor(\"started\")"
],
"Inclusion": 1,
"Low": [
"\"started\""
]
}
}
],
"using": "gsi"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Join",
"as": "mission",
"keyspace": "brandbassador_dev",
"namespace": "default",
"on_keys": "cover ((\"Mission::\" || (`submission`.`missionId`)))"
},
{
"#operator": "Filter",
"condition": "(((cover ((`submission`.`docType`)) = \"Submission\") and (cover ((`submission`.`status`)) in [\"approved\", \"rejected\", \"started\", \"ongoing\", \"pending\"])) and ((`mission`.`status`) in [\"published\", \"ended\"]))"
},
{
"#operator": "InitialGroup",
"aggregates": [
"sum(case when ((cover ((`submission`.`status`)) = \"pending\") and ((cover (ifmissingornull((`submission`.`offer`), 999)) = 999) or ((not (cover (ifmissingornull((`submission`.`offer`), 999)) = 999)) and ((cover ((`submission`.`highValueTarget`)) = true) or ((cover (ifmissingornull((`submission`.`offer`), 999)).`status`) = \"approved\"))))) then 1 else 0 end)",
"sum(case when ((cover ((`submission`.`status`)) = \"request\") or (not (cover (ifmissingornull((`submission`.`offer`), 999)) = 999))) then 1 else 0 end)"
],
"group_keys": [
"`mission`"
]
}
]
}
},
{
"#operator": "IntermediateGroup",
"aggregates": [
"sum(case when ((cover ((`submission`.`status`)) = \"pending\") and ((cover (ifmissingornull((`submission`.`offer`), 999)) = 999) or ((not (cover (ifmissingornull((`submission`.`offer`), 999)) = 999)) and ((cover ((`submission`.`highValueTarget`)) = true) or ((cover (ifmissingornull((`submission`.`offer`), 999)).`status`) = \"approved\"))))) then 1 else 0 end)",
"sum(case when ((cover ((`submission`.`status`)) = \"request\") or (not (cover (ifmissingornull((`submission`.`offer`), 999)) = 999))) then 1 else 0 end)"
],
"group_keys": [
"`mission`"
]
},
{
"#operator": "FinalGroup",
"aggregates": [
"sum(case when ((cover ((`submission`.`status`)) = \"pending\") and ((cover (ifmissingornull((`submission`.`offer`), 999)) = 999) or ((not (cover (ifmissingornull((`submission`.`offer`), 999)) = 999)) and ((cover ((`submission`.`highValueTarget`)) = true) or ((cover (ifmissingornull((`submission`.`offer`), 999)).`status`) = \"approved\"))))) then 1 else 0 end)",
"sum(case when ((cover ((`submission`.`status`)) = \"request\") or (not (cover (ifmissingornull((`submission`.`offer`), 999)) = 999))) then 1 else 0 end)"
],
"group_keys": [
"`mission`"
]
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialProject",
"result_terms": [
{
"as": "requested",
"expr": "sum(case when ((cover ((`submission`.`status`)) = \"request\") or (not (cover (ifmissingornull((`submission`.`offer`), 999)) = 999))) then 1 else 0 end)"
},
{
"as": "pending",
"expr": "sum(case when ((cover ((`submission`.`status`)) = \"pending\") and ((cover (ifmissingornull((`submission`.`offer`), 999)) = 999) or ((not (cover (ifmissingornull((`submission`.`offer`), 999)) = 999)) and ((cover ((`submission`.`highValueTarget`)) = true) or ((cover (ifmissingornull((`submission`.`offer`), 999)).`status`) = \"approved\"))))) then 1 else 0 end)"
},
{
"expr": "(`mission`.`docId`)"
},
{
"expr": "`mission`",
"star": true
}
]
}
]
}
}
]
},
{
"#operator": "Order",
"limit": "10",
"sort_terms": [
{
"desc": true,
"expr": "(`mission`.`created_at`)"
}
]
},
{
"#operator": "Limit",
"expr": "10"
},
{
"#operator": "FinalProject"
}
]
},
"text": "SELECT SUM(CASE WHEN submission.status = 'request' OR IFMISSINGORNULL(submission.offer,999) != 999 THEN 1 ELSE 0 END) AS requested,\n SUM(CASE WHEN submission.status = 'pending' AND (IFMISSINGORNULL(submission.offer,999) == 999 OR (IFMISSINGORNULL(submission.offer,999) != 999 AND (submission.highValueTarget = true OR IFMISSINGORNULL(submission.offer,999).status='approved'))) THEN 1 ELSE 0 END) AS pending,\n mission.docId,mission.*\n FROM brandbassador_dev submission\n JOIN brandbassador_dev mission ON KEYS ('Mission::' || submission.missionId)\n WHERE submission.docType=\"Submission\" \n AND submission.status IN [\"approved\", \"rejected\", \"started\", \"ongoing\", \"pending\"]\n AND mission.status IN [\"published\", \"ended\"] \n GROUP BY mission \n ORDER BY mission.created_at DESC \n LIMIT 10"
}
]