query-optimization.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Before we perform any optimization, we need a schema.\n",
    "For this example, we'll use [`HrSchema`](https://calcite.apache.org/testapidocs/org/apache/calcite/test/JdbcTest.HrSchema.html) which is defined in [`JdbcTest.java`](https://github.com/apache/calcite/blob/master/core/src/test/java/org/apache/calcite/test/JdbcTest.java) in the Apache Calcite test suite.\n",
    "First we start by requiring Calcite.\n",
    "In this case, we also require tests JAR so we can use `HrSchema`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "%%loadFromPOM\n",
    "<dependency>\n",
    "    <groupId>org.apache.calcite</groupId>\n",
    "    <artifactId>calcite-core</artifactId>\n",
    "    <version>1.21.0</version>\n",
    "</dependency>\n",
    "<dependency>\n",
    "    <groupId>org.apache.calcite</groupId>\n",
    "    <artifactId>calcite-core</artifactId>\n",
    "    <version>1.21.0</version>\n",
    "    <classifier>tests</classifier>\n",
    "    <type>test-jar</type>\n",
    "</dependency>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we construct an instance of `HrSchema` using `CalciteAssert` which is also from Calcite's testing libraries.\n",
    "In practice, you would connect to a real database or at least some source of schema information.\n",
    "With the schema, we first construct a [`RelBuilder`](https://calcite.apache.org/apidocs/org/apache/calcite/tools/RelBuilder.html) that we can use to build up a tree of relational algebra operators."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import org.apache.calcite.jdbc.CalciteSchema;\n",
    "import org.apache.calcite.schema.SchemaPlus;\n",
    "import org.apache.calcite.tools.Frameworks;\n",
    "import org.apache.calcite.tools.FrameworkConfig;\n",
    "import org.apache.calcite.tools.RelBuilder;\n",
    "\n",
    "import org.apache.calcite.test.CalciteAssert;\n",
    "\n",
    "SchemaPlus rootSchema = CalciteSchema.createRootSchema(true).plus();\n",
    "FrameworkConfig config = Frameworks.newConfigBuilder()\n",
    "    .defaultSchema(\n",
    "        CalciteAssert.addSchema(rootSchema, CalciteAssert.SchemaSpec.HR))\n",
    "    .build();\n",
    "RelBuilder builder = RelBuilder.create(config);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we build a simple query which is roughly equivalent to the SQL query `SELECT * FROM emps, depts NATURAL JOIN deptno WHERE empid=100`.\n",
    "Note that we have constructed the filter so that it falls after the join."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "3:LogicalFilter(condition=[=($0, 100)])\n",
      "  2:LogicalJoin(condition=[=($1, $5)], joinType=[inner])\n",
      "    0:LogicalTableScan(table=[[hr, emps]])\n",
      "    1:LogicalTableScan(table=[[hr, depts]])\n"
     ]
    }
   ],
   "source": [
    "import org.apache.calcite.rel.RelNode;\n",
    "import org.apache.calcite.rel.RelWriter;\n",
    "import org.apache.calcite.rel.externalize.RelWriterImpl;\n",
    "import org.apache.calcite.rel.core.JoinRelType;\n",
    "\n",
    "RelNode opTree = builder.scan(\"emps\")\n",
    "    .scan(\"depts\")\n",
    "    .join(JoinRelType.INNER, \"deptno\")\n",
    "    .filter(builder.equals(builder.field(\"empid\"), builder.literal(100)))\n",
    "    .build();\n",
    "    \n",
    "RelWriter rw = new RelWriterImpl(new PrintWriter(System.out, true));\n",
    "opTree.explain(rw);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A common query optimization is pushing filters past joins, limiting the amount of data which must be joined.\n",
    "Calcite implements this with [`FilterJoinRule`](https://calcite.apache.org/apidocs/org/apache/calcite/rel/rules/FilterJoinRule.html).\n",
    "Calcite has two main planners which can be used for query optimization.\n",
    "The first is [`HepPlanner`](https://calcite.apache.org/apidocs/org/apache/calcite/plan/hep/HepPlanner.html) which simply applies a given set of rules until they no longer apply.\n",
    "We'll start by constructin a [`HepProgram`](https://calcite.apache.org/apidocs/org/apache/calcite/plan/hep/HepProgram.html) which specifies a set of rules to be applied and specify `FilterIntoJoinRule`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "import org.apache.calcite.plan.hep.HepProgram;\n",
    "import org.apache.calcite.plan.hep.HepProgramBuilder;\n",
    "import org.apache.calcite.rel.rules.FilterJoinRule;\n",
    "\n",
    "HepProgram program = HepProgram.builder().addRuleInstance(FilterJoinRule.FILTER_ON_JOIN).build();"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can use this program to construct an instance of `HepPlanner` and run the optimization."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "13:LogicalJoin(condition=[=($1, $5)], joinType=[inner])\n",
      "  10:LogicalFilter(condition=[=($0, 100)])\n",
      "    0:LogicalTableScan(table=[[hr, emps]])\n",
      "  1:LogicalTableScan(table=[[hr, depts]])\n"
     ]
    }
   ],
   "source": [
    "import org.apache.calcite.plan.hep.HepPlanner;\n",
    "\n",
    "HepPlanner hepPlanner = new HepPlanner(program);\n",
    "hepPlanner.setRoot(opTree);\n",
    "hepPlanner.findBestExp().explain(rw);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The second is [`VolcanoPlanner`](https://calcite.apache.org/apidocs/org/apache/calcite/plan/volcano/VolcanoPlanner.html) which attempts to apply a set of rules to minimize the expected cost of executing a query.\n",
    "`VolcanoPlanner` is the default planner used by Calcite.\n",
    "Note that all the operators in the tree we constructed above are *logical* operators meaning that they don't have a specific implementation.\n",
    "All logical operators must be converted to physical operators before a query can actually be implemented, which is required for `VolcanoPlanner` to provide an estimated cost.\n",
    "Each physical operator in Calcite has a *calling convention* which specifies how the query will actually be executed.\n",
    "Since we're not working with an actual database we'll use `EnumerableConvention` which simply implements queries over collections implementing the `Enumerable` interface."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "150:EnumerableHashJoin(condition=[=($1, $5)], joinType=[inner])\n",
      "  148:EnumerableFilter(condition=[=($0, 100)])\n",
      "    147:EnumerableInterpreter\n",
      "      63:BindableTableScan(table=[[hr, emps]])\n",
      "  149:EnumerableInterpreter\n",
      "    69:BindableTableScan(table=[[hr, depts]])\n"
     ]
    }
   ],
   "source": [
    "import org.apache.calcite.adapter.enumerable.EnumerableConvention;\n",
    "import org.apache.calcite.plan.RelTraitSet;\n",
    "import org.apache.calcite.plan.RelOptCluster;\n",
    "import org.apache.calcite.plan.volcano.VolcanoPlanner;\n",
    "\n",
    "RelOptCluster cluster = opTree.getCluster();\n",
    "VolcanoPlanner planner = (VolcanoPlanner) cluster.getPlanner();\n",
    "\n",
    "RelTraitSet desiredTraits = cluster.traitSet().replace(EnumerableConvention.INSTANCE);\n",
    "RelNode newRoot = planner.changeTraits(opTree, desiredTraits);\n",
    "planner.setRoot(newRoot);\n",
    "\n",
    "RelNode optimized = planner.findBestExp();\n",
    "optimized.explain(rw);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "`VolcanoPlanner` has a default set of rules which includes `FilterJoinRule` so we can see that in this case, the filter has also been moved before the join.\n",
    "However, we also see that logical operators have been replaced with enumerable operators.\n",
    "This means we can now execute the optimized query and get a result."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "100 Sales\n"
     ]
    }
   ],
   "source": [
    "import java.sql.ResultSet;\n",
    "\n",
    "import org.apache.calcite.tools.RelRunners;\n",
    "\n",
    "ResultSet result = RelRunners.run(optimized).executeQuery();\n",
    "int columns = result.getMetaData().getColumnCount();\n",
    "while (result.next()) {\n",
    "  System.out.println(result.getString(1) + \" \" + result.getString(7));\n",
    "}"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Java",
   "language": "java",
   "name": "java"
  },
  "language_info": {
   "codemirror_mode": "java",
   "file_extension": ".java",
   "mimetype": "text/x-java-source",
   "name": "Java",
   "pygments_lexer": "java",
   "version": "11.0.1+13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}