Hi,
I have 63k records in my bucket and I am trying to fetch counts based on type. Below is my query
SELECT COUNT(type) AS count, type FROM events USE INDEX(
my-contribution
USING GSI) WHERE user_id IS NOT MISSING AND type IN[‘added’, ‘edited’] GROUP BY type;
Running this query takes more than 500ms.
Explain for the same is
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan3",
"covers": [
"cover ((`events`.`user_id`))",
"cover ((`events`.`type`))",
"cover ((meta(`events`).`id`))"
],
"index": "my-contribution",
"index_id": "7e05677ff098f217",
"index_projection": {
"entry_keys": [
0,
1
]
},
"keyspace": "events",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"inclusion": 1,
"low": "null"
},
{
"high": "\"added\"",
"inclusion": 3,
"low": "\"added\""
}
]
},
{
"exact": true,
"range": [
{
"inclusion": 1,
"low": "null"
},
{
"high": "\"edited\"",
"inclusion": 3,
"low": "\"edited\""
}
]
}
],
"using": "gsi"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "((cover ((`events`.`user_id`)) is not missing) and (cover ((`events`.`type`)) in [\"added\", \"edited\"]))"
},
{
"#operator": "InitialGroup",
"aggregates": [
"count(cover ((`events`.`type`)))"
],
"group_keys": [
"cover ((`events`.`type`))"
]
}
]
}
},
{
"#operator": "IntermediateGroup",
"aggregates": [
"count(cover ((`events`.`type`)))"
],
"group_keys": [
"cover ((`events`.`type`))"
]
},
{
"#operator": "FinalGroup",
"aggregates": [
"count(cover ((`events`.`type`)))"
],
"group_keys": [
"cover ((`events`.`type`))"
]
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialProject",
"result_terms": [
{
"as": "count",
"expr": "count(cover ((`events`.`type`)))"
},
{
"expr": "cover ((`events`.`type`))"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
},
"text": "SELECT COUNT(type) AS count, type FROM events USE INDEX(`my-contribution` USING GSI) WHERE user_id IS NOT MISSING AND \ntype IN['added', 'edited'] GROUP BY type;"
}
What should I do improve the performance of this query