Presto: Aggregate pushdown for count(const) or count(non null) in JDBC connectors

Created on 6 Jul 2020 · 7Comments · Source: prestosql/presto

Aggregate pushdown currently doesn't support the following use case when the count is done on a constant value.

select count(1) from jdbcdb.schema.table;

Current aggregate pushdown implementation generates this query on the datasource side - select 1 from schema.table;.
Instead we like to see - select count(1) from schema.table; or a flavor of it with count operation pushed down.
More details are here - slack link

Source

sajjoseph

Most helpful comment

I think we need to add this case to io.prestosql.plugin.postgresql.TestPostgreSqlIntegrationSmokeTest#testAggregationPushdown and reorder rules. This will give us good enough coverage.

kokosing on 10 Jul 2020

👍2

All 7 comments

We have SimplifyCountOverConstant rule. Does it mean the rule does not work properly? Looks like it supports simplifying the count over constant.

Lewuathe on 9 Jul 2020

It looks like we run SimplifyCountOverConstant after PushAggregationIntoTableScan.

kokosing on 9 Jul 2020

👍1

@kokosing How can we test the dependency of the rules? We can use BaseRuleTest or RuleTester to verify the rule independently. But I'm not sure how to check the final output of the sequence of multiple rules.

Lewuathe on 10 Jul 2020

I think we need to add this case to io.prestosql.plugin.postgresql.TestPostgreSqlIntegrationSmokeTest#testAggregationPushdown and reorder rules. This will give us good enough coverage.

kokosing on 10 Jul 2020

👍2

@kokosing
I tried to test the following queries. The former was correctly pushdown to the connector but the latter was not.

        assertPushedDown("SELECT count(*) FROM nation");  // Aggregation pushdown happens
        assertPushedDown("SELECT count(1) FROM nation");  // It does not happen

PushAggregationIntoTableScan optimizer was not triggered. It looks like the source of the aggregation node is not TableScanNode, actually ProjectNode. The plan of the latter SQL cannot be applied to the optimizer.

Do you have any idea why aggregation for count(1) is not over table scan? I guess it can be affected by other optimizers but not sure.

Lewuathe on 14 Jul 2020

Hey @Lewuathe. @findepi and I were taking a look at this today too. Can you try moving SimplifyCountOverConstant to run before columnPruningOptimizer. That should take care of the extra ProjectNode.