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

[sajjoseph]

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

[Lewuathe]

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

[kokosing]

It looks like we run SimplifyCountOverConstant after PushAggregationIntoTableScan.

[Lewuathe]

@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.

[kokosing]

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.

[Lewuathe]

@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.

[alexjo2144]

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.

[Lewuathe]

@alexjo2144 Thanks! I confirmed putting the SimplifyCountOverConstant before column pruning fixed the issue.