Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Eliminate pushed down filters in JDBC connectors #4111

Closed
findepi opened this issue Jun 20, 2020 · 3 comments · Fixed by #4565 or #4919
Closed

Eliminate pushed down filters in JDBC connectors #4111

findepi opened this issue Jun 20, 2020 · 3 comments · Fixed by #4565 or #4919
Assignees
Labels
enhancement New feature or request
Milestone

Comments

@findepi
Copy link
Member

findepi commented Jun 20, 2020

In JDBC connectors, pushed down filters are retained in the plan, because we do not know whether and which portion of the filter will actually be applied.

For example:

  • for some columns we do not do filter pushdown (eg for PostgreSQL varbinary)
  • some predicates are simplified for the remote database (e.g. large IN predicates in SQL Server connector)

Introduce the base-jdbc level API to let JdbcClient provide feedback whether predicate is fully pushed down.

The goal is to eliminate filters to enable subsequent aggregation pushdown.

Motivation

Agg pushdown is only possible when aggregation is direct above TS.
So, for a query like

{code}
SELECT sale_date, count(*)
FROM sales
WHERE sale_date >= DATE '2020-01-01'
{code}

we would need not only to push the filter into JDBC connector ((this is the case today)
but also eliminate it from above the TS (this is not the case today)

@findepi findepi added the enhancement New feature or request label Jun 20, 2020
@findepi findepi self-assigned this Jun 20, 2020
@findepi
Copy link
Member Author

findepi commented Jul 3, 2020

From offline conversation with @martint :

Not sure we need a dedicated rule. The connector should be able to consume the predicate, which leaves the query as an Aggregation(TableScan).

I agree. We need JdbcMetadata / JdbcClient / ColumnMapping change allowing the connector to opt out¹ from
"full pushdown".

Today we have pushdownConverter which is related (if pushdownConverter changes the Domain,
we can assume the pushdown is not "full"). We need, however, some additional piece of information.
For example, MySQL is case insensitive by default, so MySQL connector would want to keep varchar-based
predicates even if it attempts to push all of the Domain.

¹) "opt in" into "full pushdown" is safer from backward compatibility perspective, but it is IMO wrong choice
long term. The case for "opt out" are more infrequent and we should be able to identify them all.

@findepi
Copy link
Member Author

findepi commented Jul 24, 2020

PR: #4565

@findepi
Copy link
Member Author

findepi commented Aug 21, 2020

This works for filters (predicates), but doesn't work well for aggregations + predicates (WHERE clause).
I missed a TODO i put in the code...
cc @kokosing @losipiuk

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request
1 participant