Grouping and aggregations not pushed down to indexer

SQL++
1

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"
                    }
                ]
            }
        }
    ]
}
3

Which version of Couchbase are you using?
Later versions of Couchbase have improvements to pushdown the grouping and aggregation to the Indexer, if it is covered.

The grouping and aggregation in the query you have posted can be pushed down to the indexer in later versions.

4

Thanks for replies. I’m using Couchbase CE 6.6.

Based on this link, I can see that there is no possibility for aggregate pushdowns in CE.
Thanks for help, I was not aware of the differences between CE and the EE.

5

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.