FarragoMedJdbcFilterPushdown
From Eigenpedia
Contents |
Overview
This page describes enhancements to the SQL/MED JDBC foreign data wrapper for pushing filters down into the source DBMS.
A new wrapper-specific optimizer rule will match a FilterRel on top of a MedJdbcQueryRel and then
- attempt to translate the RexNode representation of the filter back into SqlNode form
- unparse the SQL using the appropriate dialect
- attempt to prepare the unparsed SQL against the source connection to verify that the translation was acceptable
If either of the "attempt" steps fails, the optimizer rule will swallow the exception and return, leaving the filter to be evaluated locally.
For the first cut, the rule will not attempt to decompose the filter into portions which can be translated and portions which cannot. Nor will it rely on any metadata from the source JDBC driver for governing the dialect; instead, it will just use SQL:2003 standard dialect.
Translation from RexNode tree to SqlNode tree
There is some very very old commented-out code for this in org.eigenbase.rex.RexToSqlTranslator, from back in the day before the RexNode framework was added.
The cleanest way to replace that is to follow the pattern for translation from SqlNode to RexNode in org.eigenbase.sql2rel. There, interface SqlRexConvertlet defines an abstract mapping from a SqlCall to a RexNode; we need a new interface org.eigenbase.rex.RexSqlConvertlet which defines the reverse mapping from a RexCall to a SqlNode. The rest of the pattern can be followed as well (a new RexSqlConvertletTable interface for looking up convertlets, plus a RexSqlStandardConvertletTable supplying the mapping implementations for standard operators). All of this can live in package org.eigenbase.rex.
Once SQL/MED routine mapping support (SQL:2003 Part 9 Section 12.8) is added, we'll need an implementation of RexSqlConvertletTable which knows how to look up mappings for UDF invocations from Farrago's catalog. (This can't live in package org.eigenbase.rex; the generic functionality for looking up a routine mapping should go in FarragoStmtValidator, whereas the specifics should go into the JDBC connector itself.)
Pushdown Disablement
In some cases, pushdown may be attempted in such a way that a source SQL incompatibility is not detected until execution time, meaning it is too late to skip the pushdown attempt, leading to an error. To provide the user control in this case, the connector will support an override option to explicitly disable pushdown.
The corresponding SQL/MED option name is DISABLED_PUSHDOWN_REL_PATTERN, and the value is a regular expression matching the names of logical relational expressions for which pushdown should be disabled. To enable all pushdown, the value can be left as empty string (the default). To disable all pushdown, the value can be set to a match-anything regular expression ('.*'). To disable a particular expression, the value can be set to the corresponding optimizer RelNode class name, e.g. 'FilterRel'. This specification is intended to cover pushdown of other operators such as join and aggregation in the future.
Projection Pushdown
--Jvs 16:40, 27 May 2009 (EDT): This section documents an enhancement for the way projections are pushed down (the code for this is necessarily intertwined with the filter pushdown).
The original projection pushdown does not reorder fields, meaning that it's possible that a reordering projection may be left on top:
0: jdbc:farrago:> explain plan for select sal,deptno from hsqldb_demo.sales.emp; +-------------------------------------------------------+ | column0 | +-------------------------------------------------------+ | ResultSetToFarragoIteratorConverter | | MedJdbcQueryRel(foreignSql=[SELECT "SAL", "DEPTNO" | | FROM "SALES"."EMP"]) | +-------------------------------------------------------+ 0: jdbc:farrago:> explain plan for select deptno,sal from hsqldb_demo.sales.emp; +-------------------------------------------------------------------------------------------------------------------+ | column0 | +-------------------------------------------------------------------------------------------------------------------+ | FennelToIteratorConverter | | FennelReshapeRel(projection=[[1, 0]], outputRowType=[RecordType(INTEGER DEPTNO, DECIMAL(19, 0) SAL) NOT NULL]) | | IteratorToFennelConverter | | ResultSetToFarragoIteratorConverter | | MedJdbcQueryRel(foreignSql=[SELECT "SAL", "DEPTNO" | | FROM "SALES"."EMP"]) | +-------------------------------------------------------------------------------------------------------------------+
(Planner tracing shows that the reordering ProjectRel is inserted in both cases, but in the first case, it is later removed by RemoveTrivialProjectRule.)
For this pushdown in isolation, production of the reordering ProjectRel is OK, since the main intention is to minimize the width of rows fetched from the remote server. However, if another rule is going to fire afterwards (e.g. pushing down an aggregation on top of the projection), then the presence of the reordering ProjectRel complicates the subsequent rule unnecessarily, so it would be preferable to avoid generating it in the first place: instead, generate the remote SELECT list in the desired order. However, in doing this, we have to make sure that the original top projection was purely reordering
--Jvs 16:40, 27 May 2009 (EDT): I don't think there are any positional dependencies on the fields produced, since the filter is below the top project.
