Loading TOC...
SQL Data Modeling Guide (PDF)

SQL Data Modeling Guide — Chapter 9

« Previous chapter

Execution Plan

This section describes how to interpret an execution plan output by the SQL EXPLAIN statement, the Optic AccessPlan.prototype.explain method, or the xdmp:sql-plan function.

Generating an Execution Plan

You can use the EXPLAIN statement or xdmp:sql-plan function to generate the query execution plan for a SQL query. For example, the following produces and execution plan for the SELECT query:

EXPLAIN SELECT employees.FirstName, employees.LastName,
SUM(expenses.Amount) AS ExpensesPerEmployee
FROM employees, expenses
WHERE employees.EmployeeID = expenses.EmployeeID
GROUP BY employees.FirstName, employees.LastName

Outputs the following execution plan:

<plan:plan xmlns:plan="http://marklogic.com/plan">
  <plan:select>
    <plan:project order="">
      <plan:column name="employees.FirstName" 
            column-index="0" static-type="STRING">
      </plan:column>
      <plan:column name="employees.LastName" 
            column-index="1" static-type="STRING">
      </plan:column>
      <plan:column name="ExpensesPerEmployee" 
            column-index="2" static-type="DOUBLE">
      </plan:column>
      <plan:hash-group order="">
        <plan:order-spec descending="false" 
            column="main.employees.FirstName" column-index="1">
        </plan:order-spec>
        <plan:order-spec descending="false" 
            column="main.employees.LastName" column-index="2">
        </plan:order-spec>
        <plan:aggregate column="ExpensesPerEmployee" 
            column-index="2" name="sum" distinct="false">
          <plan:column-ref name="main.expenses.Amount" 
            column-index="5">
          </plan:column-ref>
        </plan:aggregate>
        <plan:aggregate column="employees.FirstName" 
            column-index="0" name="sample" distinct="false">
          <plan:column-ref name="main.employees.FirstName" column-index="1">
          </plan:column-ref>
        </plan:aggregate>
        <plan:aggregate column="employees.LastName" 
            column-index="1" name="sample" distinct="false">
          <plan:column-ref name="main.employees.LastName" 
            column-index="2">
          </plan:column-ref>
        </plan:aggregate>
        <plan:parallel-hash-join order="3,2">
          <plan:hash left="4" right="0" operator="=">
          </plan:hash>
          <plan:sort-merge-join order="6,4">
            <plan:hash left="6" right="6" operator="=">
            </plan:hash>
            <plan:triple-index order="6,5" permutation="PSO">
              <plan:subject>
                <plan:column name="main.expenses.rowid" 
                      column-index="6" static-type="UNKNOWN">
                </plan:column>
              </plan:subject>
              <plan:predicate>
                <plan:value column="main.expenses.Amount"
                       columnID="14904495488947884968">
                </plan:value>
              </plan:predicate>
              <plan:object>
                <plan:column name="main.expenses.Amount" 
                      column-index="5" static-type="DECIMAL">
                </plan:column>
              </plan:object>
            </plan:triple-index>
            <plan:triple-index order="6,4" permutation="PSO">
              <plan:subject>
                <plan:column name="main.expenses.rowid" 
                      column-index="6" static-type="UNKNOWN">
                </plan:column>
              </plan:subject>
              <plan:predicate>
                <plan:value column="main.expenses.EmployeeID"
                       columnID="3887479265206160521">
                </plan:value>
              </plan:predicate>
              <plan:object>
                <plan:column name="main.expenses.EmployeeID" 
                      column-index="4" static-type="INT">
                </plan:column>
              </plan:object>
            </plan:triple-index>
          </plan:sort-merge-join>
          <plan:hash-join order="3,2">
            <plan:hash left="3" right="3" operator="=">
            </plan:hash>
            <plan:sort-merge-join order="3,1">
              <plan:hash left="3" right="3" operator="=">
              </plan:hash>
              <plan:triple-index order="3,0" permutation="PSO">
                <plan:subject>
                  <plan:column name="main.employees.rowid" 
                      column-index="3" static-type="UNKNOWN">
                  </plan:column>
                </plan:subject>
                <plan:predicate>
                  <plan:value column="main.employees.EmployeeID"
                       columnID="4691838910292433538">
                  </plan:value>
                </plan:predicate>
                <plan:object>
                  <plan:column name="main.employees.EmployeeID"
                       column-index="0" static-type="INT">
                  </plan:column>
                </plan:object>
              </plan:triple-index>
              <plan:triple-index order="3,1" permutation="PSO">
                <plan:subject>
                  <plan:column name="main.employees.rowid" 
                      column-index="3" static-type="UNKNOWN">
                  </plan:column>
                </plan:subject>
                <plan:predicate>
                  <plan:value column="main.employees.FirstName"
                       columnID="2346001466860406442">
                  </plan:value>
                </plan:predicate>
                <plan:object>
                  <plan:column name="main.employees.FirstName"
                       column-index="1" static-type="STRING">
                  </plan:column>
                </plan:object>
              </plan:triple-index>
            </plan:sort-merge-join>
            <plan:triple-index order="3,2" permutation="PSO">
              <plan:subject>
                <plan:column name="main.employees.rowid" 
                      column-index="3" static-type="UNKNOWN">
                </plan:column>
              </plan:subject>
              <plan:predicate>
                <plan:value column="main.employees.LastName"
                       columnID="3470857143136371394">
                </plan:value>
              </plan:predicate>
              <plan:object>
                <plan:column name="main.employees.LastName" 
                      column-index="2" static-type="STRING">
                </plan:column>
              </plan:object>
            </plan:triple-index>
          </plan:hash-join>
          <plan:join-filter op="=">
            <plan:column name="main.employees.EmployeeID" 
                      column-index="0" static-type="UNKNOWN">
            </plan:column>
            <plan:column name="main.expenses.EmployeeID" 
                      column-index="4" static-type="UNKNOWN">
            </plan:column>
          </plan:join-filter>
        </plan:parallel-hash-join>
      </plan:hash-group>
    </plan:project>
  </plan:select>
</plan:plan>

Parsing an Execution Plan

This section breaks down and describes each portion of the execution plan.

Notice that new column numbers are assigned to everything that gets used in the query. For example, column numbers are reassigned after a group-by, so that column 0 is something different inside the group-by compared to outside of it.

<plan:plan xmlns:plan="http://marklogic.com/plan">
  <plan:select>
    <plan:project order="">

Column names and numbers (three columns total):

      <plan:column name="employees.FirstName" 
            column-index="0" static-type="STRING">
      </plan:column>
      <plan:column name="employees.LastName" 
            column-index="1" static-type="STRING">
      </plan:column>
      <plan:column name="ExpensesPerEmployee" 
            column-index="2" static-type="DOUBLE">
      </plan:column>

The output order of the employees.FirstName and employees.LastName columns:

      <plan:hash-group order="">
        <plan:order-spec descending="false" 
            column="main.employees.FirstName" column-index="1">
        </plan:order-spec>
        <plan:order-spec descending="false" 
            column="main.employees.LastName" column-index="2">
        </plan:order-spec>

The aggregation sequence for calculating SUM(expenses.Amount) AS ExpensesPerEmployee FROM employees, expenses. The results are identified as column-index="5"

        <plan:aggregate column="ExpensesPerEmployee" 
            column-index="2" name="sum" distinct="false">
          <plan:column-ref name="main.expenses.Amount" 
            column-index="5">
          </plan:column-ref>
        </plan:aggregate>
        <plan:aggregate column="employees.FirstName" 
            column-index="0" name="sample" distinct="false">
          <plan:column-ref name="main.employees.FirstName" column-index="1">
          </plan:column-ref>
        </plan:aggregate>
        <plan:aggregate column="employees.LastName" 
            column-index="1" name="sample" distinct="false">
          <plan:column-ref name="main.employees.LastName" column-index="2">
          </plan:column-ref>
        </plan:aggregate>

The columns are joined and the triple-index elements indicate which triples are accessed for the data. The permutation indicates how the results from a triple is ordered. For example, PSO indicates an order of predicate, subject, and object.

The order attribute details the known natural order of the result of the operators. For example, order="6,4" indicates that the result is ordered first by column 6 (ascending) and then by column 4 (ascending). Ascending is implied if descending is not shown.

        <plan:parallel-hash-join order="3,2">
          <plan:hash left="4" right="0" operator="=">
          </plan:hash>
          <plan:sort-merge-join order="6,4">
            <plan:hash left="6" right="6" operator="=">
            </plan:hash>
            <plan:triple-index order="6,5" permutation="PSO">
              <plan:subject>
                <plan:column name="main.expenses.rowid" 
                      column-index="6" static-type="UNKNOWN">
                </plan:column>
              </plan:subject>
              <plan:predicate>
                <plan:value column="main.expenses.Amount"
                       columnID="14904495488947884968">
                </plan:value>
              </plan:predicate>
              <plan:object>
                <plan:column name="main.expenses.Amount" 
                      column-index="5" static-type="DECIMAL">
                </plan:column>
              </plan:object>
            </plan:triple-index>
            <plan:triple-index order="6,4" permutation="PSO">
              <plan:subject>
                <plan:column name="main.expenses.rowid" 
                      column-index="6" static-type="UNKNOWN">
                </plan:column>
              </plan:subject>
              <plan:predicate>
                <plan:value column="main.expenses.EmployeeID"
                       columnID="3887479265206160521">
                </plan:value>
              </plan:predicate>
              <plan:object>
                <plan:column name="main.expenses.EmployeeID" 
                      column-index="4" static-type="INT">
                </plan:column>
              </plan:object>
            </plan:triple-index>
          </plan:sort-merge-join>
          <plan:hash-join order="3,2">
            <plan:hash left="3" right="3" operator="=">
            </plan:hash>
            <plan:sort-merge-join order="3,1">
              <plan:hash left="3" right="3" operator="=">
              </plan:hash>
              <plan:triple-index order="3,0" permutation="PSO">
                <plan:subject>
                  <plan:column name="main.employees.rowid" 
                      column-index="3" static-type="UNKNOWN">
                  </plan:column>
                </plan:subject>
                <plan:predicate>
                  <plan:value column="main.employees.EmployeeID"
                       columnID="4691838910292433538">
                  </plan:value>
                </plan:predicate>
                <plan:object>
                  <plan:column name="main.employees.EmployeeID"
                       column-index="0" static-type="INT">
                  </plan:column>
                </plan:object>
              </plan:triple-index>
              <plan:triple-index order="3,1" permutation="PSO">
                <plan:subject>
                  <plan:column name="main.employees.rowid" 
                      column-index="3" static-type="UNKNOWN">
                  </plan:column>
                </plan:subject>
                <plan:predicate>
                  <plan:value column="main.employees.FirstName"
                       columnID="2346001466860406442">
                  </plan:value>
                </plan:predicate>
                <plan:object>
                  <plan:column name="main.employees.FirstName"
                       column-index="1" static-type="STRING">
                  </plan:column>
                </plan:object>
              </plan:triple-index>
            </plan:sort-merge-join>
            <plan:triple-index order="3,2" permutation="PSO">
              <plan:subject>
                <plan:column name="main.employees.rowid" 
                      column-index="3" static-type="UNKNOWN">
                </plan:column>
              </plan:subject>
              <plan:predicate>
                <plan:value column="main.employees.LastName"
                       columnID="3470857143136371394">
                </plan:value>
              </plan:predicate>
              <plan:object>
                <plan:column name="main.employees.LastName" 
                      column-index="2" static-type="STRING">
                </plan:column>
              </plan:object>
            </plan:triple-index>
          </plan:hash-join>

The following is the execution pipeline for the WHERE clause.

          <plan:join-filter op="=">
            <plan:column name="main.employees.EmployeeID" 
                      column-index="0" static-type="UNKNOWN">
            </plan:column>
            <plan:column name="main.expenses.EmployeeID" 
                      column-index="4" static-type="UNKNOWN">
            </plan:column>
          </plan:join-filter>
        </plan:parallel-hash-join>
      </plan:hash-group>
    </plan:project>
  </plan:select>
</plan:plan>
« Previous chapter