Aggregation Pushdown

You are looking at an older version of the documentation. The latest version is found here.

In this example, the query is performing a GROUP BY and a SUM:

SELECT
    "ProductID"
    ,sum ("UnitPrice")
FROM
    "mssql_advworks_2019.AdventureWorks2019.Sales.SalesOrderDetail"
GROUP BY
    "ProductID" LIMIT 500;;

Since MSSQL supports both operations, the entire query is pushed down to the data source. This can be verified by inspecting the query plan.

In the following example, the query is performing a similar operation of GROUP BY, SUM, and ORDER BY, but against a different data source which does not support these operations natively:

SELECT
    "salespersonid"
    ,sum(CAST("totaldue" AS float)) AS "totaldue"
FROM
    "no_pushdown_sum.SalesOrderHeader_All"
GROUP BY
    "salespersonid"
ORDER BY
    "salespersonid" LIMIT 500;;

Upon inspecting the query plan, we can see it is very different:

The detailed query plan for #1 is shown below. Because the data source does not implement GROUP BY nor SUM, the first step is to read all of the data into Data Virtuality:

In step #2, Data Virtuality performs the grouping and aggregation in memory:

In step #3, Data Virtuality implements the LIMIT 500 part of the query: