Hello,
I am trying to run the following query on Couchbase Server Community 4.5.1:
SELECT mission.docId,mission.*,
SUM(CASE WHEN submission.status = 'request' OR submission.offer IS NOT MISSING THEN 1 ELSE 0 END) AS requested,
SUM(CASE WHEN submission.status = 'pending' AND (submission.offer IS MISSING OR (submission.offer IS NOT MISSING AND (submission.highValueTarget = true OR submission.offer.status='approved'))) THEN 1 ELSE 0 END) AS pending
FROM brandbassador_dev submission
JOIN brandbassador_dev mission ON KEYS ('Mission::' || submission.missionId)
WHERE submission.docType="Submission"
AND mission.status IN ["published", "ended"]
AND submission.status IN ["approved", "rejected", "started", "ongoing", "pending"]
GROUP BY mission
ORDER BY mission.created_at DESC
LIMIT 10
Running this query takes on average 8 (!) seconds to run. Below is a couple of indices I’ve created:
CREATE INDEX `idx_docType_dev` ON `brandbassador_dev`(`docType`) WHERE (`docType` is not missing)
CREATE INDEX `idx_submission_dev` ON `brandbassador_dev`(`docType`,`missionId`) WHERE (`docType` = "Submission")
and below is the EXPLAIN query:
[
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IntersectScan",
"scans": [
{
"#operator": "IndexScan",
"index": "idx_docType_dev",
"index_id": "bac21a130df04add",
"keyspace": "brandbassador_dev",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"\"Submission\""
],
"Inclusion": 3,
"Low": [
"\"Submission\""
]
}
}
],
"using": "gsi"
},
{
"#operator": "IndexScan",
"index": "idx_submission_dev",
"index_id": "f4d498aed02fb948",
"keyspace": "brandbassador_dev",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"successor(\"Submission\")"
],
"Inclusion": 1,
"Low": [
"\"Submission\""
]
}
}
],
"using": "gsi"
}
]
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Fetch",
"as": "submission",
"keyspace": "brandbassador_dev",
"namespace": "default"
},
{
"#operator": "Join",
"as": "mission",
"keyspace": "brandbassador_dev",
"namespace": "default",
"on_keys": "(\"Mission::\" || (`submission`.`missionId`))"
},
{
"#operator": "Filter",
"condition": "((((`submission`.`docType`) = \"Submission\") and ((`mission`.`status`) in [\"published\", \"ended\"])) and ((`submission`.`status`) in [\"approved\", \"rejected\", \"started\", \"ongoing\", \"pending\"]))"
},
{
"#operator": "InitialGroup",
"aggregates": [
"sum(case when (((`submission`.`status`) = \"pending\") and (((`submission`.`offer`) is missing) or (((`submission`.`offer`) is not missing) and (((`submission`.`highValueTarget`) = true) or (((`submission`.`offer`).`status`) = \"approved\"))))) then 1 else 0 end)",
"sum(case when (((`submission`.`status`) = \"request\") or ((`submission`.`offer`) is not missing)) then 1 else 0 end)"
],
"group_keys": [
"`mission`"
]
}
]
}
},
{
"#operator": "IntermediateGroup",
"aggregates": [
"sum(case when (((`submission`.`status`) = \"pending\") and (((`submission`.`offer`) is missing) or (((`submission`.`offer`) is not missing) and (((`submission`.`highValueTarget`) = true) or (((`submission`.`offer`).`status`) = \"approved\"))))) then 1 else 0 end)",
"sum(case when (((`submission`.`status`) = \"request\") or ((`submission`.`offer`) is not missing)) then 1 else 0 end)"
],
"group_keys": [
"`mission`"
]
},
{
"#operator": "FinalGroup",
"aggregates": [
"sum(case when (((`submission`.`status`) = \"pending\") and (((`submission`.`offer`) is missing) or (((`submission`.`offer`) is not missing) and (((`submission`.`highValueTarget`) = true) or (((`submission`.`offer`).`status`) = \"approved\"))))) then 1 else 0 end)",
"sum(case when (((`submission`.`status`) = \"request\") or ((`submission`.`offer`) is not missing)) then 1 else 0 end)"
],
"group_keys": [
"`mission`"
]
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "(`mission`.`docId`)"
},
{
"expr": "`mission`",
"star": true
},
{
"as": "requested",
"expr": "sum(case when (((`submission`.`status`) = \"request\") or ((`submission`.`offer`) is not missing)) then 1 else 0 end)"
},
{
"as": "pending",
"expr": "sum(case when (((`submission`.`status`) = \"pending\") and (((`submission`.`offer`) is missing) or (((`submission`.`offer`) is not missing) and (((`submission`.`highValueTarget`) = true) or (((`submission`.`offer`).`status`) = \"approved\"))))) then 1 else 0 end)"
}
]
}
]
}
}
]
},
{
"#operator": "Order",
"limit": "10",
"sort_terms": [
{
"desc": true,
"expr": "(`mission`.`created_at`)"
}
]
},
{
"#operator": "Limit",
"expr": "10"
},
{
"#operator": "FinalProject"
}
]
},
"text": "SELECT mission.docId,mission.*,\nSUM(CASE WHEN submission.status = 'request' OR submission.offer IS NOT MISSING THEN 1 ELSE 0 END) AS requested,\nSUM(CASE WHEN submission.status = 'pending' AND (submission.offer IS MISSING OR (submission.offer IS NOT MISSING AND (submission.highValueTarget = true OR submission.offer.status='approved'))) THEN 1 ELSE 0 END) AS pending\nFROM brandbassador_dev submission\nJOIN brandbassador_dev mission ON KEYS 'Mission::' || submission.missionId\nWHERE submission.docType=\"Submission\" \nAND mission.status IN [\"published\", \"ended\"] \nAND submission.status IN [\"approved\", \"rejected\", \"started\", \"ongoing\", \"pending\"]\nGROUP BY mission \nORDER BY mission.created_at DESC \nLIMIT 10"
}
]
The bucket currently has less than 60K documents, and the server has three nodes. I think that it is obvious that the indices I’ve created are not appropriate. Could you help me finding a way to decrease the time this query takes to run? Thanks!!