Execution plans and basic operators

The EXPLAIN command

EXPLAIN <SQL statement>
EXPLAIN <Hint> <SQL statement>

Place Hint immediately after EXPLAIN.

All examples in this topic use the following table schema:

CREATE TABLE `sbtest1` (
  `id`  INT(10) UNSIGNED NOT NULL,
  `k`   INT(10) UNSIGNED NOT NULL DEFAULT '0',
  `c`   CHAR(120)        NOT NULL DEFAULT '',
  `pad` CHAR(60)         NOT NULL DEFAULT '',
  KEY `xid` (`id`),
  KEY `k_1` (`k`)
) dbpartition BY HASH (`id`) tbpartition BY HASH (`id`) tbpartitions 4

Run EXPLAIN on a query to get its logical plan:

mysql> explain select a.k, count(*) cnt from sbtest1 a, sbtest1 b where a.id = b.k and a.id > 1000 group by k having cnt > 1300 order by cnt limit 5, 10;

+---------------------------------------------------------------------------------------------------------------------------------------------------+
| LOGICAL PLAN                                                                                                                                      |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
| MemSort(sort="cnt ASC", offset=? 2, fetch=? 3)                                                                                                    |
|   Filter(condition="cnt > ? 1")                                                                                                                   |
|     HashAgg(group="k", cnt="COUNT()")                                                                                                             |
|       BKAJoin(id="id", k="k", c="c", pad="pad", id0="id0", k0="k0", c0="c0", pad0="pad0", condition="id = k", type="inner")                      |
|         MergeSort(sort="k ASC")                                                                                                                   |
|           LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT * FROM `sbtest1` WHERE (`id` > ?) ORDER BY `k`")        |
|         Gather(concurrent=true)                                                                                                                   |
|           LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT * FROM `sbtest1` WHERE ((`k` > ?) AND (`k` IN ('?')))") |
| HitCache:false                                                                                                                                    |
+---------------------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.01 sec)

How to read this plan: The execution plan is a tree — read it from the innermost (most indented) operators upward. LogicalView operators at the bottom pull data from the storage layer. Each operator above processes and passes data up until MemSort produces the final result.

The plan also includes metadata after the tree. In this example, HitCache:false indicates that plan cache was not hit. For details on plan cache, see Manage execution plans.

Operators overview

PolarDB-X 1.0 supports the following operators:

The sections below describe the query-delivery and data-exchange operators. Join, aggregation, and sorting operators are described in follow-up topics.

LogicalView

LogicalView pulls data from the storage layer MySQL. Unlike a simple table scan, it pushes down a rich set of operations — including projections, filters, aggregations, sorts, joins, and subqueries — so that MySQL executes them close to the data before returning results.

Example:

> explain select * From sbtest1 where id > 1000;

Gather(concurrent=true)
  LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT * FROM `sbtest1` WHERE (`id` > ?)")

LogicalView output fields:

Gather

Gather merges data from multiple table shardings into a single result set.

Without parallel queries, Gather sits directly above LogicalView and collects data from all shardings. The concurrent parameter controls whether sub-operators run concurrently (default: true).

With parallel queries enabled, Gather moves higher in the plan tree and collects results from all Workers instead. Its attribute changes to parallel=true.

MergeSort

MergeSort merges multiple ordered data streams into a single ordered stream.

Example:

> explain select * from sbtest1 where id > 1000 order by id limit 5,10;

MergeSort(sort="id ASC", offset=? 1, fetch=? 2)
  LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT * FROM `sbtest1` WHERE (`id` > ?) ORDER BY `id` LIMIT (? + ?)")

MergeSort output fields:

Project

Project selects or transforms columns from the input. It can select a subset of columns, compute expressions or function results, and include constants.

Example:

> explain select 'Hello, DRDS', 1 / 2, CURTIME();

Project(Hello, DRDS="_UTF-16'Hello, DRDS'", 1 / 2="1 / 2", CURTIME()="CURTIME()")

Each entry in the Project output maps a column name to its source column, constant, function, or expression.

Filter

Filter applies conditions to the input and passes through only rows that match.

The following example contains most of the operators described so far:

> explain select k, avg(id) avg_id from sbtest1 where id > 1000 group by k having avg_id > 1300;

Filter(condition="avg_id > ? 1")
  Project(k="k", avg_id="sum_pushed_sum / sum_pushed_count")
    SortAgg(group="k", sum_pushed_sum="SUM(pushed_sum)", sum_pushed_count="SUM(pushed_count)")
      MergeSort(sort="k ASC")
        LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT `k`, SUM(`id`) AS `pushed_sum`, COUNT(`id`) AS `pushed_count` FROM `sbtest1` WHERE (`id` > ?) GROUP BY `k` ORDER BY `k`")

The WHERE id > 1000 condition does not appear as a separate Filter node because it was pushed down into LogicalView. You can see it as WHERE (id > ?) in the LogicalView SQL.

Union All and Union Distinct

UnionAll corresponds to UNION ALL and UnionDistinct corresponds to UNION DISTINCT. Both operators accept two or more inputs and merge their data.

Example:

> explain select * From sbtest1 where id > 1000 union distinct select * From sbtest1 where id < 200;

UnionDistinct(concurrent=true)
  Gather(concurrent=true)
    LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT * FROM `sbtest1` WHERE (`id` > ?)")
  Gather(concurrent=true)
    LogicalView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="SELECT * FROM `sbtest1` WHERE (`id` < ?)")

LogicalModifyView

LogicalModifyView writes to the storage layer — the counterpart to LogicalView, which reads from it. The recorded SQL can be INSERT, UPDATE, or DELETE.

Examples:

> explain update sbtest1 set c='Hello, DRDS' where id > 1000;

LogicalModifyView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="UPDATE `sbtest1` SET `c` = ? WHERE (`id` > ?)")

> explain delete from sbtest1 where id > 1000;

LogicalModifyView(tables="[0000-0031].sbtest1_[000-127]", shardCount=128, sql="DELETE FROM `sbtest1` WHERE (`id` > ?)")

The output fields match those of LogicalView: physical table shardings, sharding count, and an SQL template with parameterized constants (?).

PhyTableOperation

PhyTableOperation operates directly on a single physical table sharding. Typically, this operator is used only in INSERT statements. However, when SQL statements are routed to a shard, this operator also appears in SELECT statements.

Example:

> explain insert into sbtest1 values(1, 1, '1', '1'),(2, 2, '2', '2');

PhyTableOperation(tables="SYSBENCH_CORONADB_1526954857179TGMMSYSBENCH_CORONADB_VGOC_0000_RDS.[sbtest1_001]", sql="INSERT INTO ? (`id`, `k`, `c`, `pad`) VALUES(?, ?, ?, ?)", params="`sbtest1_001`,1,1,1,1")
PhyTableOperation(tables="SYSBENCH_CORONADB_1526954857179TGMMSYSBENCH_CORONADB_VGOC_0000_RDS.[sbtest1_002]", sql="INSERT INTO ? (`id`, `k`, `c`, `pad`) VALUES(?, ?, ?, ?)", params="`sbtest1_002`,2,2,2,2")

This INSERT inserts two rows, so PolarDB-X 1.0 generates one PhyTableOperation per row.

PhyTableOperation output fields:

What's next

• To analyze actual execution statistics, use EXPLAIN ANALYZE. For details, see Introduction to the query executor.

• To override the optimizer's choices, use Hint. To pin or manage execution plans, see Manage execution plans.