dependent join and aggregation pushdown

[sudeepd]

We are trying to use teiid to federate between sql and a custom in house db  (with a jdbc driver). Lets call this left for sql and right for our db.

right is heavily optimized for joins and aggregations, and ideally , we want to pushdown joins AND aggregations to the right.

Consider the query

select leftTable.leftColumn, count(*)

from leftTable full outer join rightTable on leftTable.ColumnId = rightTable.ColumnId

group by leftTable.leftColumn

The best case for us is to have the entire left resultset pushed down to the right, do the join AND the aggregation on the right.

Currently, I get the dependent join to work ok.. with columnId from left passed down to right in the IN clause.

I understand that I may have to do a custom translator, but even before that, if teiid wont split the query the way I need it to , then my translator can t do much...

so ...will a full dependent pushdown join also push down any subsequent  aggregation on the results ?

[rareddy]

Teiid will not pushdown like that, the right side has no knowledge of the schema, nor it may not have capabilities to handle such cases. What is leftTable mean on DB as is?

The alternative you can do is to create a FOREIGN TEMP TABLE as defined here Temp Tables - Teiid 8.9 (draft) - Project Documentation Editor, populate the needed columns from left side, then issue a query like above replacing the left with temp table. Here depending upon the table size you trade processing time vs population time. You should be able to accomplish this in virtual procedure.

alternatively, if you are writing custom connector take look at  "Full Pushdown" here Dependent Join Pushdown - Teiid 8.9 (draft) - Project Documentation Editor

Ramesh..

[shawkins]

To clarify, Teiid will not do a full dependent join pushdown or data shipment join by default.  It is currently hint driven and requires the translator to support the feature.  Our JDBC translator will support this with the enableDependentJoins option turned on and if they have a Hibernate dialect that supports temporary tables (which includes most common databases).  If so then yes, we will create a temporary table on the source put the entirety of one side of the relation there and allow processing including aggregation and what else is able to be pushed down to be performed at the source.

[sudeepd]

Thank you Ramesh and Steve for the response.

I understand the full dependent join pushdown, and that it needs appropriate translator support. What was not clear to me was this

If teiid sees my custom translator to support full dependent join pushdown , and if provided with the hint, then will the query planner decompose this to

a. Do a full dependent join pushdown, get the join results back , and then run aggregations

OR

b. Do a full dependent join pushdown AND push the aggregations to the source ( This means that teiid doesnt see the joined table, and doesnt run any aggregation, rather gets the aggregate results from the source)

If it is a. above , and the dep joins results in a very large resultset then its a problem for us,Since its the aggregate that I care about, I would rather have the source handle both the aggregation and the join together.

If it is b. then then thats exactly what I need.

Based on Stevens response, I believe its b (subject to translator support and hints) , would appreciate a confirmation just to make sure I got  it right

[rareddy]

It will be (b) granted your custom translator supports the Aggregate functions that are being pushed down. If not they will be handled in the engine.