Skip to main content

KlustronDB Execution Plan Explanation and Interpretation

KlustronDBAbout 8 min

KlustronDB Execution Plan Explanation and Interpretation

Note:

Unless otherwise specified, the version numbers in the text can be replaced with the version numbers of any released version. For all released versions, see: Release_note

Content of this article:

In KlustronDB, an execution plan refers to a detailed set of steps for SQL execution generated by the optimizer. An execution plan can help us understand the SQL execution process, including the execution order of SQL, the indexes used, the join methods used, and so on. By analyzing the execution plan, we can identify query performance bottlenecks and optimize them. In this article, we interpret the execution plan of running SQL in KlustronDB through specific examples.

01 Generation of Execution Plan

The generation process of the KlustronDB execution plan can be divided into the following steps:

  1. Syntax analysis: Parsing the query statement into a syntax tree.
  2. Semantic Analysis: Perform semantic analysis on the syntax tree, including table name resolution, column name resolution, type checking, and so on.
  3. Optimizer: Performs distributed query optimization on query statements and generates the optimal execution plan.
  4. Execution: Convert the optimized execution plan into executable code, and then complete distributed query execution by interacting with backend storage shards. The method of interaction is to generate SQL statements for the relevant backend storage shards based on the needs of the SQL statements and the distribution information of table partitions in the backend shards.

In this process, the optimizer is the most crucial step. The optimizer selects the optimal execution plan based on the characteristics of the query statement and the statistical information of the related tables. The process of optimizing the execution plan is very complex, including choosing the optimal join method, selecting the optimal index, choosing the optimal sorting method, and so on. With the help of the optimizer, KlustronDB can handle very complex SQL statements and ensure SQL performance.

02 Explain Grammar

In KlustronDB, the EXPLAIN command can output the query plan of an SQL statement, with the specific syntax as follows:

EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

where option can be one of:

    ANALYZE [ boolean ]
    VERBOSE [ boolean ]
    COSTS [ boolean ]
    BUFFERS [ boolean ]
    TIMING [ boolean ]
    SUMMARY [ boolean ]
    FORMAT { TEXT | XML | JSON | YAML }

Among them:

If the ANALYZE option is TRUE, SQL will actually be executed and the corresponding query plan will be obtained. The default is FALSE. If you want to optimize some SQL that modifies data, but need to execute it for real without affecting existing data, you can put it in a transaction and roll it back directly after the analysis is completed.

When the VERBOSE option is TRUE, additional information about the query plan will be displayed; the default is FALSE. The additional information includes the columns (Output) produced by each node in the query plan (the meaning of nodes will be explained later), the table's SCHEMA information, the function's SCHEMA information, the aliases of the tables to which columns in expressions belong, the names of triggered triggers, and so on.

When the COSTS option is TRUE, it will display the estimated startup cost for each plan node (the cost to find the first qualifying result) and the total cost, as well as the estimated number of rows and the width per row. The default is TRUE.

When the TIMING option is TRUE, the actual start time and total execution time of each plan node will be displayed. The default is TRUE. This parameter can only be used together with the ANALYZE parameter. For some systems, obtaining the system time can be costly. If only the accurate number of returned rows is needed, and accurate timing is not required, this parameter can be turned off.

The SUMMARY option, when set to TRUE, will output summary information after the query plan, such as the time the query plan was generated and the time the query plan was executed. When the ANALYZE option is enabled, it defaults to TRUE.

FORMAT specifies the output format, with the default being TEXT. The content of each format output is the same, among which XML | JSON | YAML are more conducive to parsing SQL statement query plans through programs. For easier reading, the examples below all use the output results in TEXT format.

The following method is generally used to execute SQL and generate detailed SQL execution information.

explain analyze verbose select … ;

03 Interpretation of the Execution Plan

KlustronDB is an architecture with separated storage and computation. SQL is parsed and an execution plan is generated on the Kluscomp instance, and then the Klustore instance is accessed for data.

The execution plan output mainly includes the following parts:

  1. Access method. RemotePlan is a newly added query plan node in KlustronDB, used to obtain user data on Klustore instances. From the execution plan RemotePlan above, it can be seen that this SQL is executed entirely on the Klustore instances, where the data access method on the Klustore instances is Table scan. The filter condition on the Klustore instances is testdb_$$_public.sales_order.product_code = 1.
  2. Access cost. cost=343.15..343.15 The estimated cost of executing the query is 343.15.
  3. Return row count. The query is estimated to return rows=49, while the actual number of returned rows is rows=19. The following Table scan estimates the total row count of sales_order as rows=9862, and after executing the filter (product_code=1), it is estimated to return rows=986. Here, you can see that the estimated rows=49 from the RemotePlan do not match well with the estimated rows=986 after filtering. This is because the row count in the RemotePlan is estimated by the Kluscomp instance, whereas the operators and cost estimates under the RemotePlan are completed by the Klustore instance MySQL. Additionally, the MySQL execution plan steps displayed under analyze do not include actual execution time.
  4. Execution time. The Planning Time section indicates that the time to generate the execution plan is 0.066 ms, and the Execution section indicates that the actual execution time is 2.299 ms.

3.1 Full Table Scan

Analyze the previous execution plan again, in which a Table scan on sales_order was performed on the Klustore instance, with a Filter (product_code=1) applied during the full table scan. The actual execution time was 2.299 ms, returning 19 rows.

3.2 Index Scan

3.2.1 Primary Key Index /** Single Row Scan of Unique Index

When accessing a single row record through a primary key index or a unique index, the execution plan will show Rows fetched before execution.

3.2.2 Index lookup (Equality condition)

From the execution plan, on the Klustore instance, an Index lookup on sales_order using sales_order_idx1 is performed. The optimizer's estimate for this query is quite accurate (cost=6.65 rows=19), and the actual execution time was 1.792 ms.

3.2.3 Index range scan (Range condition)

A range scan on the index was performed on the sales_order_idx2 of the Klustore instance.

3.2.4 Index scan

Since the returned fields are all included in the index, an Index scan is performed on the Klustore instance to scan the entire index, eliminating the need for a table lookup.

3.3 Nested Loop Join

The specific implementation steps are as follows:

  1. The driving table of the Nested Loop is product, and it is accessed via RemotePlan. An Index scan is performed on the primary key index of the Klustore instance, filtered by the field t1.product_name like 'LCD%', and finally 4 rows satisfying the condition (rows=4) are returned to the computation node.
  2. At the Kluscomp instance, take the product_code of each record returned from step 1 and send it to the Klustore instance to execute Remote SQL: SELECT t2.order_amount, t2.product_code FROM testdb_$$_public.sales_order as t2 WHERE (? = t2.product_code), looping 4 times (loops=4), and the resulting result set is also sent back to the Kluscomp instance.
  3. The result set that meets the Join condition has 81 rows (rows=81)
  4. Finally, complete the final SUM on the Kluscomp instance.

3.4 Hash Join

In the execution plan, the access to the driving table is in the lower half of the Hash Join, which is different from the access to the driving table in the previous Nested Loop Join.

  1. First, perform a Table scan on t1 at the Klustore instance, and apply a filter (product_name like 'LCD%'), then return the result set to the Kluscomp instance.

  2. Create a hash table on the Kluscomp instance based on the fields of the Join. The situation of the hash table is:

Buckets: 1024 Batches: 1 Memory Usage: 20kB

  1. Perform a Table scan to t2 on the Klustore instance, returning the fields order_amount and product_code to the Kluscomp instance.

  2. Perform a Hash Join on the Kluscomp instance, using the Hash table created in step 2 to probe the result set returned in step 3. Hash Cond: (t2.product_code = t1.product_code), returning the order_amount values of records that meet the join condition.

  3. Finally, perform the aggregation operation sum(order_amount).

3.5 Sort Merge Join

In general, the join efficiency of a Hash Join is better than that of a Sort Merge Join. However, if the source rows are already sorted, then no additional sorting is needed when executing a Sort Merge Join, and in this case, the performance of the Sort Merge Join will be better than that of the Hash Join. The join fields of the following two tables both have indexes created, so the results scanned through an Index scan are already sorted and can be directly used as input for a Sort Merge Join.

  1. Perform an Index scan on t1 at the Klustore instance, and after looking up the table, return product_code, order_amount, and order_number.
  2. Perform an Index scan on t2 at the Klustore instance, and after returning to the table, return product_code and product_name.
  3. Perform a Merge Join on the computation nodes. Note that since the results returned by the index are all sorted by product_code, the Sort step is omitted.

If the product_code index of one of the tables is deleted, the following execution plan appears, with an additional Sort step.

3.6 Parallel Execution Plan

After enabling enable_parallel_remotescan in KlustronDB, the optimizer on Kluscomp instances can not only assign RemotePlans that scan different table partitions to multiple sub-tasks, allowing multiple worker processes to execute in parallel, but also split the RemotePlans that scan the same table partition or unpartitioned single tables into multiple sub-tasks based on the range of rows that need to be scanned according to statistics. This allows parallel execution even for RemotePlans that need to scan non-partitioned tables.

View parameters related to Kluscomp instance parallelism:

Next, let's look at the execution plan for distributed parallel queries.

3.6.1 Non-Partitioned Table Parallel Scan

  1. We see Workers Planned: 5 Workers Launched: 5, but actually a total of 6 parallel processes were allocated: Leader, Worker 0-4. You can also see the 6 parallel processes through the final loops=6 in Parallel RemotePlan.

  2. Each parallel process executes an Index range scan on sales_order using sales_order_idx1 on the Klustore instances, each performing sum(order_amount), and outputs the PARTIAL sum(order_amount) result to the Kluscomp instances. Since this is a summary of partial result sets, the word 'PARTIAL' appears here.

  3. The Leader process gathers the sum(order_amount) results from the other five, and then performs the final aggregation.

3.6.2 Partition Table Parallel Scan

  1. Similarly, we see Workers Planned: 3, Workers Launched: 3, but there are a total of 4 processes working in parallel: Leader, Worker 0, Worker 1, Worker 2. Additionally, some clues can be gleaned from loops=4.

  2. The two tables are joined using a Hash Join. The driving table, product, is a non-partitioned table. On the Klustore instances, four parallel processes each performed an Index Range Scan on the product table once. The result set that meets the conditions contains 333 rows, which are returned to the Kluscomp instance to create the Hash Table.

  1. Parallel Append indicates that four index partitions of sales_order were scanned in parallel using an Index Scan. The records that meet the conditions are returned to the Kluscomp instances. (2562 2506 2497 2435)

  1. Completing Hash Join in parallel on Kluscomp instances

  1. Partial Aggregate performs aggregation on the rows returned by the Hash Join of each parallel worker.

  2. Gather collects the results returned by each worker process. The leader process performs Gather and the parts above Gather. Gather needs to send the result set to Finalize Aggregate in order to form the final output using each worker's Partial Aggregate results.

The RemotePlan executed in parallel will each connect to the Klustore instances in the worker processes to perform query tasks. In order to obtain consistent query results, these worker processes must use the same snapshot in their respective connections. Therefore, KlustronDB has added connection snapshot sharing capability at the Klustore instance layer to cooperate with the Kluscomp instances in executing parallel distributed query plans.

3.7 The Presentation of Operator Pushdown in Execution Plans

KlustronDB supports pushing down multiple types of operators to Klustore instances for execution. Referring to the case of parallel execution of a large table in the previous section, operator pushdown can fully utilize the computing power of multiple underlying Klustore instances. The following shows the pushdown functionality through a specific execution plan.

3.7.1 Pushdown of Aggregate Functions

Push down the aggregate function to be executed at the Klustore instance.

3.7.2 Distinct Operator Pushdown

The following execution plan will push down count(distinct customer_number) to the Klustore instances for execution.

3.7.3 Pushdown of Limit and Order by

The execution plan below shows that 'order by order_amount desc limit 10' has been pushed down to the Klustore instance for execution.

3.7.4 Join Pushdown

The execution plan below shows pushing down the Nested Loop Join operation of the sales_order and product tables to the Klustore instance for execution.

END

Copyright © KlustronDB
Copyright © 2026 KlustronDB