Kelun Chai created CALCITE-5673: ----------------------------------- Summary: FilterIntoJoinRule cannot pushdown filter to TableScan Key: CALCITE-5673 URL: https://issues.apache.org/jira/browse/CALCITE-5673 Project: Calcite Issue Type: Bug Components: core, spatial Affects Versions: 1.26.0 Reporter: Kelun Chai
The current rule can only push down the join condition from one side to table scan. If the join condition comes from both sides, it cannot push down the filter, but extracts the relevant columns to Join through scan for calculation. SQL Query: {code:sql} SELECT A.name AS cName, B.name AS fName FROM vehicles AS A JOIN dimTable as B ON ST_Contains(B.fence, ST_MakePoint(A.x, A.y));{code} Query Plan: {code:bash} == Abstract Syntax Tree == LogicalProject(cName=[$1], fName=[$5]) +- LogicalJoin(condition=[ST_Contains($6, ST_MakePoint($2, $3))], joinType=[inner]) :- LogicalTableScan(table=[[default_catalog, default_database, vehicles]]) +- LogicalTableScan(table=[[default_catalog, default_database, dimTable]]){code} The same query in postgres behaves as follows (w/ & w/o index): {code:bash} Nested Loop (cost=0.00..18071570.38 rows=722 width=64) Join Filter: st_contains(b.fence, a.location) -> Seq Scan on vehicles a (cost=0.00..18.50 rows=850 width=64) -> Materialize (cost=0.00..22.75 rows=850 width=64) -> Seq Scan on fences b (cost=0.00..18.50 rows=850 width=64) # Using GIST Index Nested Loop (cost=0.13..84.50 rows=1 width=64) -> Seq Scan on fences b (cost=0.00..1.03 rows=3 width=64) -> Index Scan using g_idx on vehicles a (cost=0.13..27.81 rows=1 width=64) Index Cond: (location @ b.fence) Filter: st_contains(b.fence, location){code} We created an in-memory based index in Tablescan, is there a way to convert the spatial join condition to NestedLoopJoin (LogicalCorrelate) and push down to the Tablescan node? -- This message was sent by Atlassian Jira (v8.20.10#820010)