Lindorm:Overview of query plans

Applicable engines

Query plans are applicable to LindormTable and LindormTSDB.

Background information

A query plan describes the execution process of a SQL query statement in a database. Query plans are also referred as execution plans. When you diagnose the performance of a query, you can first view the query plan of the query statement to check whether the query is successfully executed by step. Therefore, you must understand query plans before you can optimize SQL statements. To achieve this goal, you must first understand the operators used in query plans that can be viewed by the EXPLAIN syntax.

Query plan depth

You can use the EXPLAIN syntax to view the query plans that are generated for a query statement at the following three depths:

logical plan, physical plan, and type plan.

Logical plan

The logical plan of a query is not optimized by the optimizer.

Lindorm SQL generates the logical plan of a query by rewriting the query based on the abstract syntax tree. Some logical operations of the query are rewritten in the query plan for easier optimization and execution. For example, Lindorm SQL may replace a constant expression in the query with the calculated constant in the query plan, or replace the AVG function in the query with the SUM or COUNT function in the query plan. You can specify the WITHOUT IMPLEMENTATION expression in the EXPLAIN syntax to view the logical plan of a query. For more information about the EXPLAN syntax, see EXPLAIN.

The following statement is used as an example:

SELECT LOCATE('9378', p1) AS lp, COUNT(c1) FROM (SELECT * FROM test WHERE p1 like '3_%') GROUP BY lp ORDER BY lp;

View the query plan of the preceding statement.

EXPLAIN WITHOUT IMPLEMENTATION FOR SELECT LOCATE('9378', p1) AS lp, COUNT(c1) FROM (SELECT * FROM test WHERE p1 like '3_%') GROUP BY lp ORDER BY lp;
********************* 1. row *********************
PLAN: LogicalSort(sort0=[$0], dir0=[ASC])
  LogicalAggregate(group=[{0}], EXPR$1=[COUNT($1)])
    LogicalProject(lp=[LOCATE('9378', $0)], c1=[$1])
      LogicalFilter(condition=[LIKE($0, '3_%')])
        LindormTableScan(table=[[db, test]])

Physical plan

The physical plan of a query is optimized by the optimizer. The optimizer of Lindorm SQL selects the optimal execution mode for a logical plan based on specific rule policies or cost policies. The selected execution mode and its candidates are referred as a physical plan.

When you use the EXPLAIN syntax to view the query plan of a query, the physical plan of the query is returned by default. The physical plan contains the operators executed by Lindorm SQL and those are executed by LindormTable.

The physical plan of a query can clearly show the execution process of a SQL statement, including the data scanning mode and the sequence of relational operations.

The following statement is used as an example:

SELECT LOCATE('9378', p1) AS lp, COUNT(c1) FROM (SELECT * FROM test WHERE p1 like '3_%') GROUP BY lp ORDER BY lp;

View the physical plan of the preceding statement.

EXPLAIN SELECT LOCATE('9378', p1) AS lp, COUNT(c1) FROM (SELECT * FROM test WHERE p1 like '3_%') GROUP BY lp ORDER BY lp;
********************* 1. row *********************
PLAN: EnumerableSort(sort0=[$0], dir0=[ASC])
  EnumerableAggregate(group=[{0}], EXPR$1=[COUNT($1)])
    EnumerableCalc(expr#0..3=[{inputs}], expr#4=['9378'], expr#5=[LOCATE($t4, $t0)], lp=[$t5], c1=[$t1])
      EnumerableLindormDirectQuery(
explanation=[SELECT p1,c1,c2,c3 from test where ((p1 < 4) AND (p1 >= 3) AND (p1 LIKE 3_%)) supportEmptyResult true
Candidate tables: 
    Data table: test, scores=1..0..1, need query back=false, sort type=FORWARD
Chose data table [test].
---
SELECT p1,c1,c2,c3 from test supportEmptyResult true
SingleScan on test
    ranges: [3\x00, 4\x00]
    filter: ((p1 < 4) AND (p1 >= 3) AND (p1 LIKE 3_%))
])

Type plan

The type plan of a query contains only the types of each column in the result sets of the query.

Type plans are generated based on query types and are used is specific scenarios. Compared with logical plans and physical plans, type plans contains the least information. You can specify the WITH TYPE expression in the EXPLAIN syntax to view the type plan of a query. For more information about the EXPLAN syntax, see EXPLAIN.

The following statement is used as an example:

SELECT LOCATE('9378', p1) AS lp, COUNT(c1) FROM (SELECT * FROM test WHERE p1 like '3_%') GROUP BY lp ORDER BY lp;

View the type plan of the preceding statement.

EXPLAIN WITH TYPE FOR SELECT LOCATE('9378', p1) AS lp, COUNT(c1) FROM (SELECT * FROM test WHERE p1 like '3_%') GROUP BY lp ORDER BY lp;
********************* 1. row *********************
PLAN: lp INTEGER NOT NULL,
EXPR$1 BIGINT NOT NULL

Operators in query plans

This section describes the common operators that are included in query plans.

General-purpose physical operators

Lindorm SQL provides a set of general-purpose physical operators for the optimizer to generate physical plans. These operators can be used to perform general-purpose computing operations such as sorting, aggregation, projection, and calculation in queries. The following general-purpose physical operators are commonly used in queries:

• EnumerableLimit

  This operator is used to skip and truncate the result set data based on the specified OFFSET and LIMIT values. The sources of the offset and fetch values required by this operator are displayed in the returned query plan.

• EnumerableSort

  This operator is used to sort data based on the ORDER BY clause specified in the query. The default sorting algorithm is implemented based on treemapping. The source of the sorting key used by this operator is displayed in the returned query plan.

• EnumerableAggregate

  This operator is used to aggregate data that are iteratively returned by lower-level operators based on the specified aggregate functions and possible grouping columns. This operator can also be used to implement the DISTINCT syntax. The grouping key of the operator and expressions related to the aggregate function are displayed in the returned query plan.

• EnumerableCalc

  This operator can be used to calculate the values of common expressions, such as arithmetic expressions and expressions of functions except for aggregate and window functions. In addition, calculation operations such as projection and filtering are also implemented by using this operator.

Extended physical operators

EnumerableLindormDirectQuery

The EnumerableLindormDirectQuery operator is a general-purpose extension operator provided by Lindorm SQL to indicate the part of a query that is pushed down to the storage engine for computing. The returned result of this operator is contained in the explanation field. The information contained in the explanation field varies with different Lindorm engines.

Extended operators for LindormTSDB

• TSDBDataScanRel

  This operator is a variant of the logical operator LogicalTableScan and is used to perform scanning operations on a specific metric. The following fields of the operator are displayed in the returned query plan:

  • filter: the filter conditions of the scanning operations.

  • project: the mapping between the operator expressions in the higher-level query plan with the time series table.

  • hint: the hints that are pushed down together with the scanning operations, such as _l_series_only.

• TSDBShowTagRel

  This operator is used to optimize queries that are performed on all tag values in a tag column. This operator provides the same capability as that of the SHOW TAG VALUES syntax of InfluxDB. This operator is usually at the lowest level in a query plan tree, which is similar to the TSDBDataScanRel operator. This operator is used to indicate the data scanning method in a data engine. Only queries in the following format can be optimized by using this operator: SELECT DISTINCT ${tagkey column} FROM ${table}.

• TSDBAggScanRel

  This operator is a combination of the EnumerableAggregation and TSDBDataScanRel operator and is used to aggregate time series.

• EnumerableDownsampleQuery

  This operator is specific to the SAMPLE BY syntax provided by Lindorm SQL. The SAMPLE BY syntax is an extended SQL dialect for LindormTSDB in downsampling scenarios. All contexts of the syntax are extended, including its logical operator LogicalDownsampleQuery and physical operator EnumerableDownsampleQuery. Therefore, the EnumerableDownsampleQuery operator is included only in the query plan of a query that uses the SAMPLE BY syntax.

  The following fields of the operator are displayed in the returned query plan:

  • project: the mapping between the operator expressions in the higher-level query plan with the EnumerableDownsampleQuery operator.

  • filter: the filter conditions that is pushed down to LindormTSDB together with the downsampling operations.

  • ds_agg: the downsampling function used in the query and the parameters of the function.

  • aggregator: the aggregate operators that are pushed down to LindormTSDB together with the aggregating operations across time series.

  • hint: the hints that are pushed down to LindormTSDB.