Agreed. We have solved thus in other systems by presenting a name to each convention and use that in the rule name. On May 28, 2016 7:14 PM, "Jordan Halterman" <[email protected]> wrote:
> I actually got a chance to do some more digging in to this over the > weekend. When I step through the VolcanoPlanner in my debugger, I realize > that while a JdbcFilter rule is being created for each unique > JdbcConvention, > only one of those two rules is actually being fired. This seems to be a > result of the JDBC rule names. JdbcToEnumerableConverterRule is the only > JDBC rule using the convention in its description. All other JDBC rules > just have static descriptions, e.g. JdbcFilterRule or JdbcProjectRule. This > results in the rules being fired only for *one* of the JdbcConventions. I > verified that the expected plan is output when I change all the JDBC rules > to use a unique description-per-convention. > > Does this make sense? > > I will open a Jira ticket for this > > On Thu, May 26, 2016 at 1:21 PM, Jordan Halterman < > [email protected]> wrote: > > > Glad we're seeing the same thing. I can take a shot at reproducing it in > a > > test. > > > > On Thu, May 26, 2016 at 1:17 PM, Julian Hyde <[email protected]> wrote: > > > >> I agree with everything you said. The best plan is to join inside the > >> JDBC source, and we’d hope that Calcite could find that plan. > >> > >> It is curious that the JdbcFilter has infinite cost. I can’t tell by > >> reading the trace output exactly why. I wonder whether it has failed to > >> make a JdbcTableScan of the correct table within the correct JDBC > database > >> (which, as you note, is a particular instance of JdbcConvention). > >> > >> There’s clearly a Calcite bug here, likely in the JDBC adapter. Can you > >> log it? > >> > >> You may be able to convert this into a test case on the standard data > >> sources, e.g. by combining the scott and foodmart hsqldb databases. > >> > >> Julian > >> > >> > On May 26, 2016, at 1:46 AM, Jordan Halterman < > >> [email protected]> wrote: > >> > > >> > I've been doing some experimenting with the Calcite Planner and am > >> curious > >> > about a specific plan that's being generated. I've set up two JDBC > >> schemas, > >> > one with "users" and "items" tables, and one with "orders" and > >> "purchases". > >> > The Planner is set up using the same rules that are used in > >> > CalcitePrepareImpl with EnumerableConvention. > >> > > >> > When I optimize a simple query that joins two tables within the same > >> > database, I get the expected plan, e.g.: > >> > > >> > SELECT u.id AS user_id, i.id AS item_id, i.name, i.description FROM > >> users u > >> > JOIN items i ON u.id = i.user_id > >> > > >> > JdbcToEnumerableConverter: rowcount = 1500.0, cumulative cost = > {3050.0 > >> > rows, 5152.0 cpu, 0.0 io}, id = 43 > >> > JdbcProject(user_id=[$0], item_id=[$5], name=[$7], description=[$8]): > >> > rowcount = 1500.0, cumulative cost = {2900.0 rows, 5002.0 cpu, 0.0 > io}, > >> id > >> > = 42 > >> > JdbcJoin(condition=[=($0, $6)], joinType=[inner]): rowcount = > 1500.0, > >> > cumulative cost = {1700.0 rows, 202.0 cpu, 0.0 io}, id = 41 > >> > JdbcTableScan(table=[[USERS]]): rowcount = 100.0, cumulative > cost = > >> > {100.0 rows, 101.0 cpu, 0.0 io}, id = 5 > >> > JdbcTableScan(table=[[ITEMS]]): rowcount = 100.0, cumulative > cost = > >> > {100.0 rows, 101.0 cpu, 0.0 io}, id = 6 > >> > > >> > However, when I optimize a query that joins two tables in one database > >> with > >> > a table in another database, I don't get the plan I expect. None of > the > >> > joins or filters are done in JDBC, only table scans e.g.: > >> > > >> > SELECT u.id, o.id AS order_id FROM users u JOIN orders o ON u.id = > >> > o.user_id JOIN purchases p ON o.id = p.order_id WHERE p.order_id < 50 > >> > > >> > EnumerableProject(id=[$6], order_id=[$0]): rowcount = 11250.0, > >> cumulative > >> > cost = {29206.571923496576 rows, 22933.0 cpu, 0.0 io}, id = 141 > >> > EnumerableJoin(condition=[=($1, $6)], joinType=[inner]): rowcount = > >> > 11250.0, cumulative cost = {17956.571923496576 rows, 433.0 cpu, 0.0 > >> io}, id > >> > = 140 > >> > EnumerableJoin(condition=[=($0, $3)], joinType=[inner]): rowcount = > >> > 750.0, cumulative cost = {1531.517018598809 rows, 322.0 cpu, 0.0 io}, > >> id = > >> > 138 > >> > JdbcToEnumerableConverter: rowcount = 100.0, cumulative cost = > >> {110.0 > >> > rows, 111.0 cpu, 0.0 io}, id = 135 > >> > JdbcTableScan(table=[[ORDERS]]): rowcount = 100.0, cumulative > >> cost > >> > = {100.0 rows, 101.0 cpu, 0.0 io}, id = 10 > >> > EnumerableFilter(condition=[<($1, 50)]): rowcount = 50.0, > >> cumulative > >> > cost = {160.0 rows, 211.0 cpu, 0.0 io}, id = 137 > >> > JdbcToEnumerableConverter: rowcount = 100.0, cumulative cost = > >> > {110.0 rows, 111.0 cpu, 0.0 io}, id = 136 > >> > JdbcTableScan(table=[[PURCHASES]]): rowcount = 100.0, > >> cumulative > >> > cost = {100.0 rows, 101.0 cpu, 0.0 io}, id = 12 > >> > JdbcToEnumerableConverter: rowcount = 100.0, cumulative cost = > {110.0 > >> > rows, 111.0 cpu, 0.0 io}, id = 139 > >> > JdbcTableScan(table=[[USERS]]): rowcount = 100.0, cumulative > cost = > >> > {100.0 rows, 101.0 cpu, 0.0 io}, id = 9 > >> > > >> > It seems to me that the EnumerableFilter and EnumerableJoin of ORDERS > >> and > >> > PURCHASES (which each have the same JdbcConvention) could be done in > >> JDBC, > >> > i.e. something more like this: > >> > > >> > EnumerableProject > >> > EnumerableJoin > >> > JdbcToEnumerableConverter > >> > JdbcJoin > >> > JdbcTableScan(table=[[ORDERS]]: ...) > >> > JdbcFilter > >> > JdbcTableScan(table=[[PURCHASES]]: ...) > >> > JdbcToEnumerableConverter > >> > JdbcTableScan(table=[[USERS]]: ...) > >> > > >> > So, my question is, why is the join and even the filter not pushed > down > >> to > >> > JDBC? When I log the trace, I see that the JdbcJoin and JdbcFilter > have > >> an > >> > {inf} cost, but I'm ignorant as to why that is. Here's the trace: > >> > > >> > Set#0, type: RecordType(INTEGER ID, VARCHAR(1) NAME, VARCHAR(1) EMAIL, > >> > VARCHAR(1) CREATED_AT, VARCHAR(1) UPDATED_AT) > >> > rel#38:Subset#0.JDBC.site.[], best=rel#9, > importance=0.7290000000000001 > >> > rel#9:JdbcTableScan.JDBC.site.[](table=[USERS]), rowcount=100.0, > >> cumulative > >> > cost={100.0 rows, 101.0 cpu, 0.0 io} > >> > rel#97:Subset#0.ENUMERABLE.[0], best=null, > importance=0.7290000000000001 > >> > rel#100:Subset#0.ENUMERABLE.[], best=rel#120, > >> importance=0.7290000000000001 > >> > > >> > rel#120:JdbcToEnumerableConverter.ENUMERABLE.[](input=rel#38:Subset#0.JDBC.site.[]), > >> > rowcount=100.0, cumulative cost={110.0 rows, 111.0 cpu, 0.0 io} > >> > Set#1, type: RecordType(INTEGER ID, INTEGER USER_ID) > >> > rel#39:Subset#1.JDBC.cart.[], best=rel#10, > importance=0.7290000000000001 > >> > rel#10:JdbcTableScan.JDBC.cart.[](table=[ORDERS]), rowcount=100.0, > >> > cumulative cost={100.0 rows, 101.0 cpu, 0.0 io} > >> > rel#124:Subset#1.ENUMERABLE.[], best=rel#123, importance=0.6561 > >> > > >> > rel#123:JdbcToEnumerableConverter.ENUMERABLE.[](input=rel#39:Subset#1.JDBC.cart.[]), > >> > rowcount=100.0, cumulative cost={110.0 rows, 111.0 cpu, 0.0 io} > >> > rel#127:Subset#1.JDBC.site.[], best=null, > importance=0.5904900000000001 > >> > rel#131:Subset#1.ENUMERABLE.[0], best=null, importance=0.6561 > >> > Set#2, type: RecordType(INTEGER ID, INTEGER ORDER_ID, INTEGER ITEM_ID, > >> > VARCHAR(1) CREATED_AT) > >> > rel#40:Subset#2.JDBC.cart.[], best=rel#12, importance=0.6561 > >> > rel#12:JdbcTableScan.JDBC.cart.[](table=[PURCHASES]), rowcount=100.0, > >> > cumulative cost={100.0 rows, 101.0 cpu, 0.0 io} > >> > rel#91:Subset#2.JDBC.site.[], best=null, importance=0.531441 > >> > rel#102:Subset#2.ENUMERABLE.[], best=rel#114, > >> importance=0.5904900000000001 > >> > > >> > rel#114:JdbcToEnumerableConverter.ENUMERABLE.[](input=rel#40:Subset#2.JDBC.cart.[]), > >> > rowcount=100.0, cumulative cost={110.0 rows, 111.0 cpu, 0.0 io} > >> > Set#3, type: RecordType(INTEGER ID, INTEGER ORDER_ID, INTEGER ITEM_ID, > >> > VARCHAR(1) CREATED_AT) > >> > rel#42:Subset#3.NONE.[], best=null, importance=0.7290000000000001 > >> > > >> > rel#41:LogicalFilter.NONE.[](input=rel#40:Subset#2.JDBC.cart.[],condition=<($1, > >> > 50)), rowcount=50.0, cumulative cost={inf} > >> > rel#93:Subset#3.JDBC.site.[], best=null, importance=0.5904900000000001 > >> > > >> > rel#92:JdbcFilter.JDBC.site.[](input=rel#91:Subset#2.JDBC.site.[],condition=<($1, > >> > 50)), rowcount=50.0, cumulative cost={inf} > >> > rel#104:Subset#3.ENUMERABLE.[], best=rel#103, importance=0.6561 > >> > > >> > rel#103:EnumerableFilter.ENUMERABLE.[](input=rel#102:Subset#2.ENUMERABLE.[],condition=<($1, > >> > 50)), rowcount=50.0, cumulative cost={160.0 rows, 211.0 cpu, 0.0 io} > >> > > >> > rel#115:JdbcToEnumerableConverter.ENUMERABLE.[](input=rel#93:Subset#3.JDBC.site.[]), > >> > rowcount=50.0, cumulative cost={inf} > >> > rel#132:Subset#3.ENUMERABLE.[1], best=null, importance=0.6561 > >> > Set#4, type: RecordType(INTEGER ID, VARCHAR(1) NAME, VARCHAR(1) EMAIL, > >> > VARCHAR(1) CREATED_AT, VARCHAR(1) UPDATED_AT, INTEGER ID0, INTEGER > >> USER_ID, > >> > INTEGER ID1, INTEGER ORDER_ID, INTEGER ITEM_ID, VARCHAR(1) > CREATED_AT0) > >> > rel#44:Subset#4.NONE.[], best=null, importance=0.81 > >> > > >> > rel#43:MultiJoin.NONE.[](input#0=rel#38:Subset#0.JDBC.site.[],input#1=rel#39:Subset#1.JDBC.cart.[],input#2=rel#42:Subset#3.NONE.[],joinFilter=AND(=($5, > >> > $8), =($0, $6)),isFullOuterJoin=false,joinTypes=[INNER, INNER, > >> > INNER],outerJoinConditions=[NULL, NULL, NULL],projFields=[ALL, ALL, > >> ALL]), > >> > rowcount=1.0, cumulative cost={inf} > >> > > >> > rel#76:LogicalProject.NONE.[](input=rel#75:Subset#7.NONE.[],ID=$6,NAME=$7,EMAIL=$8,CREATED_AT=$9,UPDATED_AT=$10,ID0=$0,USER_ID=$1,ID1=$2,ORDER_ID=$3,ITEM_ID=$4,CREATED_AT0=$5), > >> > rowcount=11250.0, cumulative cost={inf} > >> > rel#49:Subset#4.JDBC.site.[], best=null, importance=0.81 > >> > > >> > rel#88:JdbcProject.JDBC.site.[](input=rel#82:Subset#7.JDBC.site.[],ID=$6,NAME=$7,EMAIL=$8,CREATED_AT=$9,UPDATED_AT=$10,ID0=$0,USER_ID=$1,ID1=$2,ORDER_ID=$3,ITEM_ID=$4,CREATED_AT0=$5), > >> > rowcount=11250.0, cumulative cost={inf} > >> > rel#56:Subset#4.ENUMERABLE.[], best=rel#94, importance=0.9 > >> > > >> > rel#94:EnumerableProject.ENUMERABLE.[](input=rel#86:Subset#7.ENUMERABLE.[],ID=$6,NAME=$7,EMAIL=$8,CREATED_AT=$9,UPDATED_AT=$10,ID0=$0,USER_ID=$1,ID1=$2,ORDER_ID=$3,ITEM_ID=$4,CREATED_AT0=$5), > >> > rowcount=11250.0, cumulative cost={29206.571923496576 rows, 124183.0 > >> cpu, > >> > 0.0 io} > >> > > >> > rel#113:JdbcToEnumerableConverter.ENUMERABLE.[](input=rel#49:Subset#4.JDBC.site.[]), > >> > rowcount=1.0, cumulative cost={inf} > >> > Set#5, type: RecordType(INTEGER id, INTEGER order_id) > >> > rel#46:Subset#5.NONE.[], best=null, importance=0.9 > >> > > >> > rel#45:LogicalProject.NONE.[](input=rel#44:Subset#4.NONE.[],id=$0,order_id=$5), > >> > rowcount=1.0, cumulative cost={inf} > >> > > >> > rel#81:LogicalProject.NONE.[](input=rel#75:Subset#7.NONE.[],id=$6,order_id=$0), > >> > rowcount=11250.0, cumulative cost={inf} > >> > > >> > rel#95:LogicalProject.NONE.[](input=rel#86:Subset#7.ENUMERABLE.[],id=$6,order_id=$0), > >> > rowcount=11250.0, cumulative cost={inf} > >> > > >> > rel#107:LogicalProject.NONE.[](input=rel#82:Subset#7.JDBC.site.[],id=$6,order_id=$0), > >> > rowcount=11250.0, cumulative cost={inf} > >> > rel#47:Subset#5.ENUMERABLE.[], best=rel#87, importance=1.0 > >> > > >> > rel#48:AbstractConverter.ENUMERABLE.[](input=rel#46:Subset#5.NONE.[],convention=ENUMERABLE,sort=[]), > >> > rowcount=1.0, cumulative cost={inf} > >> > > >> > rel#52:AbstractConverter.ENUMERABLE.[](input=rel#51:Subset#5.JDBC.site.[],convention=ENUMERABLE,sort=[]), > >> > rowcount=1.0, cumulative cost={inf} > >> > > >> > rel#53:JdbcToEnumerableConverter.ENUMERABLE.[](input=rel#51:Subset#5.JDBC.site.[]), > >> > rowcount=1.0, cumulative cost={inf} > >> > > >> > rel#57:EnumerableProject.ENUMERABLE.[](input=rel#56:Subset#4.ENUMERABLE.[],id=$0,order_id=$5), > >> > rowcount=11250.0, cumulative cost={40456.57192349658 rows, 146683.0 > cpu, > >> > 0.0 io} > >> > > >> > rel#87:EnumerableProject.ENUMERABLE.[](input=rel#86:Subset#7.ENUMERABLE.[],id=$6,order_id=$0), > >> > rowcount=11250.0, cumulative cost={29206.571923496576 rows, 22933.0 > cpu, > >> > 0.0 io} > >> > rel#51:Subset#5.JDBC.site.[], best=null, importance=0.9 > >> > > >> > rel#50:JdbcProject.JDBC.site.[](input=rel#49:Subset#4.JDBC.site.[],id=$0,order_id=$5), > >> > rowcount=1.0, cumulative cost={inf} > >> > > >> > rel#83:JdbcProject.JDBC.site.[](input=rel#82:Subset#7.JDBC.site.[],id=$6,order_id=$0), > >> > rowcount=11250.0, cumulative cost={inf} > >> > Set#6, type: RecordType(INTEGER ID, INTEGER USER_ID, INTEGER ID0, > >> INTEGER > >> > ORDER_ID, INTEGER ITEM_ID, VARCHAR(1) CREATED_AT) > >> > rel#73:Subset#6.NONE.[], best=null, importance=0.6561 > >> > > >> > rel#60:LogicalJoin.NONE.[](left=rel#39:Subset#1.JDBC.cart.[],right=rel#42:Subset#3.NONE.[],condition==($0, > >> > $3),joinType=inner), rowcount=750.0, cumulative cost={inf} > >> > rel#89:Subset#6.JDBC.site.[], best=null, importance=0.6561 > >> > > >> > rel#128:JdbcJoin.JDBC.site.[](left=rel#127:Subset#1.JDBC.site.[],right=rel#93:Subset#3.JDBC.site.[],condition==($0, > >> > $3),joinType=inner), rowcount=750.0, cumulative cost={inf} > >> > rel#96:Subset#6.ENUMERABLE.[1], best=null, > importance=0.7290000000000001 > >> > rel#99:Subset#6.ENUMERABLE.[], best=rel#134, > >> importance=0.7290000000000001 > >> > > >> > rel#130:JdbcToEnumerableConverter.ENUMERABLE.[](input=rel#89:Subset#6.JDBC.site.[]), > >> > rowcount=750.0, cumulative cost={inf} > >> > rel#133:EnumerableMergeJoin.ENUMERABLE.[[0], > >> > > >> > [3]](left=rel#131:Subset#1.ENUMERABLE.[0],right=rel#132:Subset#3.ENUMERABLE.[1],condition==($0, > >> > $3),joinType=inner), rowcount=750.0, cumulative cost={inf} > >> > > >> > rel#134:EnumerableJoin.ENUMERABLE.[](left=rel#124:Subset#1.ENUMERABLE.[],right=rel#104:Subset#3.ENUMERABLE.[],condition==($0, > >> > $3),joinType=inner), rowcount=750.0, cumulative > cost={1531.517018598809 > >> > rows, 322.0 cpu, 0.0 io} > >> > Set#7, type: RecordType(INTEGER ID, INTEGER USER_ID, INTEGER ID0, > >> INTEGER > >> > ORDER_ID, INTEGER ITEM_ID, VARCHAR(1) CREATED_AT, INTEGER ID1, > >> VARCHAR(1) > >> > NAME, VARCHAR(1) EMAIL, VARCHAR(1) CREATED_AT0, VARCHAR(1) UPDATED_AT) > >> > rel#75:Subset#7.NONE.[], best=null, importance=0.7290000000000001 > >> > > >> > rel#74:LogicalJoin.NONE.[](left=rel#73:Subset#6.NONE.[],right=rel#38:Subset#0.JDBC.site.[],condition==($6, > >> > $1),joinType=inner), rowcount=11250.0, cumulative cost={inf} > >> > rel#82:Subset#7.JDBC.site.[], best=null, importance=0.7290000000000001 > >> > > >> > rel#90:JdbcJoin.JDBC.site.[](left=rel#89:Subset#6.JDBC.site.[],right=rel#38:Subset#0.JDBC.site.[],condition==($6, > >> > $1),joinType=inner), rowcount=11250.0, cumulative cost={inf} > >> > rel#86:Subset#7.ENUMERABLE.[], best=rel#101, importance=0.81 > >> > rel#98:EnumerableMergeJoin.ENUMERABLE.[[1], > >> > > >> > [6]](left=rel#96:Subset#6.ENUMERABLE.[1],right=rel#97:Subset#0.ENUMERABLE.[0],condition==($1, > >> > $6),joinType=inner), rowcount=11250.0, cumulative cost={inf} > >> > > >> > rel#101:EnumerableJoin.ENUMERABLE.[](left=rel#99:Subset#6.ENUMERABLE.[],right=rel#100:Subset#0.ENUMERABLE.[],condition==($1, > >> > $6),joinType=inner), rowcount=11250.0, cumulative > >> cost={17956.571923496576 > >> > rows, 433.0 cpu, 0.0 io} > >> > > >> > rel#109:JdbcToEnumerableConverter.ENUMERABLE.[](input=rel#82:Subset#7.JDBC.site.[]), > >> > rowcount=11250.0, cumulative cost={inf} > >> > >> > > >
