Hello @vsr1 @daschl @ingenthr,
I need to change the query to do a group by. But now it is much slower than the query you helped me with above. Can you provide some optimization tips?
select m.token, ARRAY_AGG(meta(m).id) as ids
FROM mobile m
WHERE m.appId='foo'
AND array_count(m.subscriptions) > 0
AND m.token > "0"
GROUP BY m.token
ORDER BY m.token
LIMIT 10000
I have this index:
CREATE INDEX `appId_token_subscriptions_idx` ON `mobile`(`appId`,`token`) WHERE (0 < array_count(`subscriptions`))
Here is the explain plan:
[
{
"plan": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan",
"covers": [
"cover ((`m`.`appId`))",
"cover ((`m`.`token`))",
"cover ((meta(`m`).`id`))"
],
"filter_covers": {
"cover ((0 < array_count((`m`.`subscriptions`))))": true
},
"index": "appId_token_subscriptions_idx",
"index_id": "21c334ac0d2804dc",
"keyspace": "mobile",
"namespace": "default",
"spans": [
{
"Range": {
"High": [
"successor(\"foo\")"
],
"Inclusion": 0,
"Low": [
"\"foo\"",
"\"0\""
]
}
}
],
"using": "gsi"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "(((cover ((`m`.`appId`)) = \"foo\") and cover ((0 < array_count((`m`.`subscriptions`))))) and (\"0\" < cover ((`m`.`token`))))"
},
{
"#operator": "InitialGroup",
"aggregates": [
"array_agg(cover ((meta(`m`).`id`)))"
],
"group_keys": [
"cover ((`m`.`token`))"
]
}
]
}
},
{
"#operator": "IntermediateGroup",
"aggregates": [
"array_agg(cover ((meta(`m`).`id`)))"
],
"group_keys": [
"cover ((`m`.`token`))"
]
},
{
"#operator": "FinalGroup",
"aggregates": [
"array_agg(cover ((meta(`m`).`id`)))"
],
"group_keys": [
"cover ((`m`.`token`))"
]
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "cover ((`m`.`token`))"
},
{
"as": "ids",
"expr": "array_agg(cover ((meta(`m`).`id`)))"
}
]
}
]
}
}
]
},
{
"#operator": "Order",
"limit": "10000",
"sort_terms": [
{
"expr": "cover ((`m`.`token`))"
}
]
},
{
"#operator": "Limit",
"expr": "10000"
},
{
"#operator": "FinalProject"
}
]
},
"text": "select m.token, ARRAY_AGG(meta(m).id) as ids\nFROM mobile m\nWHERE m.appId='foo'\nAND array_count(m.subscriptions) > 0\nAND m.token > \"0\"\nGROUP BY m.token\nORDER BY m.token\nLIMIT 10000"
}
]