Computation reuse via fusion in Amazon Athena
This is really neat, but I&#x27;m curious how much of this is novel?<p>I don&#x27;t know much about relational algebra and query planning&#x2F;optimization. But it seemed like many of these things were &quot;no-brainers&quot; (completely duplicate query conditions, etc)<p>Is the point more the general-purpose framework rather than the query outcomes&#x2F;rules?<p>I took one of the queries from the paper (the first one), and used Apache Calcite to run the same query, on the same schema<p><a href="https:&#x2F;&#x2F;github.com&#x2F;GavinRay97&#x2F;athena-paper-calcite-sandbox&#x2F;blob&#x2F;fffbb5b5d3fa741ebf3e97838dfc7d005d9d1283&#x2F;src&#x2F;main&#x2F;java&#x2F;org&#x2F;example&#x2F;Main.java#L29-L33" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;GavinRay97&#x2F;athena-paper-calcite-sandbox&#x2F;b...</a><p>Here is the original plan for Query 1:<p><pre><code>    LogicalSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC], fetch=[100])
    LogicalProject(exprs=[[$5, $33, $55]])
        LogicalFilter(condition=[AND(=($51, $53), &lt;=($55, *(0.1:DECIMAL(2, 1), $52)), =($0, $53), =($29, $54))])
        LogicalJoin(condition=[true], joinType=[inner])
            LogicalJoin(condition=[true], joinType=[inner])
            LogicalJoin(condition=[true], joinType=[inner])
                LogicalTableScan(table=[[tpc_ds, STORE]])
                LogicalTableScan(table=[[tpc_ds, ITEM]])
            LogicalAggregate(group=[{0}], AVE=[AVG($1)])
                LogicalProject(inputs=[0], exprs=[[$2]])
                LogicalAggregate(group=[{0, 1}], REVENUE=[SUM($2)])
                    LogicalProject(exprs=[[$7, $2, $13]])
                    LogicalFilter(condition=[AND(=($0, $23), &gt;=($26, 1212), &lt;=($26, 1247))])
                        LogicalJoin(condition=[true], joinType=[inner])
                        LogicalTableScan(table=[[tpc_ds, STORE_SALES]])
                        LogicalTableScan(table=[[tpc_ds, DATE_DIM]])
            LogicalAggregate(group=[{0, 1}], REVENUE=[SUM($2)])
            LogicalProject(exprs=[[$7, $2, $13]])
                LogicalFilter(condition=[AND(=($0, $23), &gt;=($26, 1212), &lt;=($26, 1247))])
                LogicalJoin(condition=[true], joinType=[inner])
                    LogicalTableScan(table=[[tpc_ds, STORE_SALES]])
                    LogicalTableScan(table=[[tpc_ds, DATE_DIM]])
</code></pre>
After using the default query planner rules, it gives this:<p><pre><code>    EnumerableProject(S_STORE_NAME=[$8], I_ITEM_DESC=[$36], REVENUE=[$2])
    EnumerableLimit(fetch=[100])
        EnumerableSort(sort0=[$8], sort1=[$36], dir0=[ASC], dir1=[ASC])
        EnumerableMergeJoin(condition=[AND(=($0, $54), &lt;=($2, *(0.1:DECIMAL(2, 1), $55)))], joinType=[inner])
            EnumerableMergeJoin(condition=[AND(=($0, $3), =($1, $32))], joinType=[inner])
            EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
                EnumerableProject(SS_STORE_SK=[$1], SS_ITEM_SK=[$0], REVENUE=[$2])
                EnumerableAggregate(group=[{30, 35}], REVENUE=[$SUM0($41)])
                    EnumerableHashJoin(condition=[=($0, $28)], joinType=[inner])
                    EnumerableFilter(condition=[SEARCH($3, Sarg[[1212..1247]])])
                        EnumerableTableScan(table=[[tpc_ds, DATE_DIM]])
                    EnumerableTableScan(table=[[tpc_ds, STORE_SALES]])
            EnumerableSort(sort0=[$0], sort1=[$29], dir0=[ASC], dir1=[ASC])
                EnumerableNestedLoopJoin(condition=[true], joinType=[inner])
                EnumerableTableScan(table=[[tpc_ds, STORE]])
                EnumerableTableScan(table=[[tpc_ds, ITEM]])
            EnumerableSort(sort0=[$0], dir0=[ASC])
            EnumerableProject(SS_STORE_SK=[$0], AVE=[&#x2F;($1, $2)])
                EnumerableAggregate(group=[{1}], agg#0=[$SUM0($2)], agg#1=[COUNT()])
                EnumerableAggregate(group=[{30, 35}], REVENUE=[$SUM0($41)])
                    EnumerableHashJoin(condition=[=($0, $28)], joinType=[inner])
                    EnumerableFilter(condition=[SEARCH($3, Sarg[[1212..1247]])])
                        EnumerableTableScan(table=[[tpc_ds, DATE_DIM]])
                    EnumerableTableScan(table=[[tpc_ds, STORE_SALES]])</code></pre>