[YSQL] Support for pushdown of aggregate queries #1851
Comments
JDNdeveloper
changed the title
JDNdeveloper
added a commit
that referenced
this issue
Sep 21, 2019
Summary: This adds support for aggregate pushdowns to DocDB for COUNT/MAX/MIN/SUM. There are various conditions where we will not pushdown the aggregate, such as when predicates are present or non-pushdown supported aggregates are present (e.g. AVG, at least for now). We pushdown if the following conditions are met: - Outer plan is a YB foreign scan. - No `WHERE` clause. - No `ORDER BY`, `GROUP BY`, `DISTINCT`, or `FILTER`. - Aggregates are one of: `COUNT`/`MIN`/`MAX`/`SUM`. - Aggregate output type (i.e. transition type) is a supported YB key type and not a postgres internal or numeric type. - Column type is a supported YB key type. **Performance numbers:** MIN(col02), 10M rows, RF 1, 2 integer cols. - Without change (with default 1024 prefetch limit): 58.6s - Without change (with 5M prefetch limit): 54.4s - With change (with default 1024 prefetch limit): 52.2s - With change (with 5M prefetch limit): 52.4s - Speedup (with default 1024 prefetch limit): 10.9% MIN(col02), 1M rows, RF 1, 2 integer cols. - Without change: 5.7s - With change: 5.1s - Speedup: 10.5% MIN(col02), 1M rows, RF 1, 10 integer cols. - Without change: 7.7s - With change: 7.1s - Speedup: 7.8% MIN(col02), 1M rows, RF 3, 10 integer cols. - Without change: 7.5s - With change: 6.7s - Speedup: 10.6% Test Plan: Added existing postgres aggregates.sql regress test as yb_pg_aggregates.sql. Will add a Java test to verify that supported aggregate queries are indeed being pushed down, and unsupported queries are not pushed down. Reviewers: neha, mihnea, neil Reviewed By: neil Subscribers: karthik, kannan, yql Differential Revision: https://phabricator.dev.yugabyte.com/D7174
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
In CQL we currently pushdown aggregate queries (count/max/min) to DocDB, whereas in YSQL we retrieve all rows and leave it up to Postgres to perform the aggregation. This significantly impacts YSQL aggregate query performance as it has a prefetch limit of 1024 rows, so there is more RPC overhead. This issue tracks adding YSQL pushdown of aggregate queries to DocDB.
The text was updated successfully, but these errors were encountered: