Hi,
I created an index for my query with grouping and aggregations, but group_by is not pushed down to indexer and its completed outside it. I don’t know what I’m doing wrong or maybe I misunderstood something. Can you help me?
My query:
SELECT attached_document_id,
IFNULL(SUM(`count`),0) `count`,
IFNULL(SUM(brutto),0) brutto,
IFNULL(SUM(netto),0) netto
FROM dbstorage
WHERE type='store_detail_doc'
AND status=2
AND local_id="78c60xxx-c241-4969-8xxx-59551317bxxx"
GROUP BY attached_document_id
my index:
CREATE INDEX `dbstorage_store_state_covered2` ON `dbstorage`(`type`,`status`,`local_id`,`attached_document_id`,`execution_date`,(`store`.`id`),`count`,`brutto`,`netto`) WHERE ((`type` = "store_detail_doc") and (`status` = 2)) WITH { "defer_build":true }
Explain:
{
"#operator": "Sequence",
"~children": [
{
"#operator": "IndexScan3",
"covers": [
"cover ((`dbstorage`.`type`))",
"cover ((`dbstorage`.`status`))",
"cover ((`dbstorage`.`local_id`))",
"cover ((`dbstorage`.`attached_document_id`))",
"cover ((`dbstorage`.`execution_date`))",
"cover (((`dbstorage`.`store`).`id`))",
"cover ((`dbstorage`.`count`))",
"cover ((`dbstorage`.`brutto`))",
"cover ((`dbstorage`.`netto`))",
"cover ((meta(`dbstorage`).`id`))"
],
"filter_covers": {
"cover ((`dbstorage`.`status`))": 2,
"cover ((`dbstorage`.`type`))": "store_detail_doc"
},
"index": "dbstorage_store_state_covered2",
"index_id": "826c2c68ba94bfcc",
"index_projection": {
"entry_keys": [
0,
1,
2,
3,
6,
7,
8
]
},
"keyspace": "dbstorage",
"namespace": "default",
"spans": [
{
"exact": true,
"range": [
{
"high": "\"store_detail_doc\"",
"inclusion": 3,
"low": "\"store_detail_doc\""
},
{
"high": "2",
"inclusion": 3,
"low": "2"
},
{
"high": "\"78c60905-c241-4969-84bf-59551317b294\"",
"inclusion": 3,
"low": "\"78c60905-c241-4969-84bf-59551317b294\""
}
]
}
],
"using": "gsi"
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "Filter",
"condition": "(((cover ((`dbstorage`.`type`)) = \"store_detail_doc\") and (cover ((`dbstorage`.`status`)) = 2)) and (cover ((`dbstorage`.`local_id`)) = \"78c60905-c241-4xxx-8xxx-59551317bxxx\"))"
},
{
"#operator": "InitialGroup",
"aggregates": [
"sum(cover ((`dbstorage`.`brutto`)))",
"sum(cover ((`dbstorage`.`count`)))",
"sum(cover ((`dbstorage`.`netto`)))"
],
"group_keys": [
"cover ((`dbstorage`.`attached_document_id`))"
]
}
]
}
},
{
"#operator": "IntermediateGroup",
"aggregates": [
"sum(cover ((`dbstorage`.`brutto`)))",
"sum(cover ((`dbstorage`.`count`)))",
"sum(cover ((`dbstorage`.`netto`)))"
],
"group_keys": [
"cover ((`dbstorage`.`attached_document_id`))"
]
},
{
"#operator": "FinalGroup",
"aggregates": [
"sum(cover ((`dbstorage`.`brutto`)))",
"sum(cover ((`dbstorage`.`count`)))",
"sum(cover ((`dbstorage`.`netto`)))"
],
"group_keys": [
"cover ((`dbstorage`.`attached_document_id`))"
]
},
{
"#operator": "Parallel",
"~child": {
"#operator": "Sequence",
"~children": [
{
"#operator": "InitialProject",
"result_terms": [
{
"expr": "cover ((`dbstorage`.`attached_document_id`))"
},
{
"as": "count",
"expr": "ifnull(sum(cover ((`dbstorage`.`count`))), 0)"
},
{
"as": "brutto",
"expr": "ifnull(sum(cover ((`dbstorage`.`brutto`))), 0)"
},
{
"as": "netto",
"expr": "ifnull(sum(cover ((`dbstorage`.`netto`))), 0)"
}
]
},
{
"#operator": "FinalProject"
}
]
}
}
]
}