Execution plan display (EXPLAIN)

The EXPLAIN command displays the logical execution plan generated by the optimizer for a given SQL statement.

When the EXPLAIN command is run, the system generates a logical execution plan for the given SQL statement and displays the plan to the user. However, the system does not generate a physical execution plan (executable code), nor does it actually execute the plan or put the plan into the plan cache.

The display of the SQL execution plan is key to understanding the SQL execution logic and performance optimization.

Syntax of the EXPLAIN command

The EXPLAIN command has three options: BASIC, EXTENDED, and PARTITIONS. The level of detail in the information displayed is different.

Use the following command syntax:

EXPLAIN [BASIC | EXTENDED | PARTITIONS | FORMAT = format_name] explainable_stmt

format_name: { TRADITIONAL | JSON }

explainable_stmt: { SELECT statement 
                   | DELETE statement 
                    | INSERT statement 
                    | REPLACE statement 
                    | UPDATE statement }

Shape and operators of a plan

The first part of the output of the EXPLAIN command is a tree-like structure representing the execution plan. The hierarchy of each operation in the tree is represented by the indentation of its operator. Example execution plan:

|==========================================
|ID|OPERATOR         |NAME|EST. ROWS|COST|
------------------------------------------
|0 |SORT             |    |1        |2763|
|1 | MERGE INNER JOIN|    |1        |2735|
|2 |  SORT           |    |1000     |1686|
|3 |   TABLE SCAN    |t2  |1000     |1180|
|4 |  TABLE SCAN     |t1  |1        |815 |
==========================================

The following figure shows the tree-like structure of the execution plan.

The following table describes the items in the execution plan.

In a table operation, the NAME field displays names (alias) of tables involved in the operation. In the case of index access, the name of the index is displayed in parentheses after the table name. For example, t1(t1_c2) indicates that index t1_c2 is used. In the case of reverse scanning, the keyword RESERVE is added after the index name, with the index name and the keyword RESERVE separated with a comma (,). For example, t1(t1_c2,Reverse).

The following table lists the common types of operators.

Detailed output of operators

The second part of the output of the EXPLAIN command contains the details of each operator, including the output expression, filter conditions, partition information, and unique information of each operator, such as the sort keys, join keys, and pushdown conditions.

Outputs & filters:
-------------------------------------
  0 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil), sort_keys([t1.c1, ASC], [t1.c2, ASC]), prefix_pos(1)
  1 - output([t1.c1], [t1.c2], [t2.c1], [t2.c2]), filter(nil),
      equal_conds([t1.c1 = t2.c2]), other_conds(nil)
  2 - output([t2.c1], [t2.c2]), filter(nil), sort_keys([t2.c2, ASC])
  3 - output([t2.c2], [t2.c1]), filter(nil),
      access([t2.c2], [t2.c1]), partitions(p0)
  4 - output([t1.c1], [t1.c2]), filter(nil),
      access([t1.c1], [t1.c2]), partitions(p0)

The key information includes:

• output

  Each operator in the execution tree needs to output some expressions to the upstream operator for subsequent operations. This is called projection. The expressions (and common columns) that need to be projected are listed here.

• filters

  All operators in OceanBase Database are capable of executing filters. The filters part lists the filtering operations that are performed by the operator. For table access operations, all filters (including filters before and after the table retrieval) are pushed down to the storage layer.

• access

  Table access operations will call the storage layer interfaces to access the actual data. The access part shows the external output (projection) columns of the storage layer.

• is_index_back and filter_before_indexback

  OceanBase Database makes a distinction between filters that can be applied before table retrieval and those that need to be applied after table retrieval. This distinction is represented by the filter_before_indexback part. If all filters can be applied before table retrieval, the is_index_back part shows "true". Otherwise, it shows "false".

• partitions

  OceanBase Database supports sub-partitions. The optimizer filters out partitions that do not need to be accessed based on the conditions specified by the SQL statement. This step is called partition pruning. The partitions part displays the remaining partitions after partition pruning. If multiple consecutive partitions are involved, for example, from partition 0 to partition 20, the partitions are displayed in the format of "start partition number"-"end partition number", for example, partitions(p0-20).

• range_key and range

  Theoretically, physical tables in OceanBase Database are index-organized tables (including secondary indexes) and are scanned in a specific order. This order is the primary key of the table and reflected in the range_key part. When a different condition is given by the user, the optimizer locates the final scan range and displays it in the range part, as shown in the following plan:

obclient> explain extended 
                select /*+ index(t1 t1_c2) */* from t1 where c3 = 5 and c1 = 6 order by c2, c3;
========================================
|ID|OPERATOR  |NAME     |EST. ROWS|COST|
----------------------------------------
|0 |TABLE SCAN|t1(t1_c2)|1        |1255|
========================================
Outputs & filters:
-------------------------------------
 0 - output([t1.c1(0x7f1d520a0a98)], [t1.c2(0x7f1d520a0d98)], [t1.c3(0x7f1d5209fbe0)]), filter([t1.c3(0x7f1d5209fbe0) = 5(0x7f1d5209f5d8)], [t1.c1(0x7f1d520a0a98) = 6(0x7f1d520a0490)]),
     access([t1.c3(0x7f1d5209fbe0)], [t1.c1(0x7f1d520a0a98)], [t1.c2(0x7f1d520a0d98)]), partitions(p0),
     is_index_back=true, filter_before_indexback[false,true],
     range_key([t1.c2(0x7f1d520a0d98)], [t1.c1(0x7f1d520a0a98)]), range(MIN,MIN ; MAX,MAX)always true

You can see that the index table t1_c2 is to be accessed. The primary key of the table is (c2, c1), and the scan range is the whole table.

• sort_keys

  The sort keys for the sort operation, including the sort order.

The following table describes some common operator information.