"G" Moment: Deep Dive into GBase 8s Query Optimization – Understanding Query Plan Principles and Applications

Published on 2025-05-26

In database development and optimization, the query plan is an essential tool. It helps us understand the execution process of SQL queries, thereby optimizing query performance. This article systematically explains how to use query plans in GBase 8s, and provides a detailed analysis of how to read and interpret the contents of a query plan.

Data Preparation

The script prepare_data.sh performs table creation and data insertion. The snippet is as follows:

set explain on

“set explain on” is the GBase 8s command used to view execution plans; execute it before running your SQL statement.

Options:

  • ON: Generates an estimate for each subsequent query and writes the result to an output file in the current directory. If the file already exists, new output will be appended to the existing file.

  • AVOID_EXECUTE: Prevents SELECT, INSERT, UPDATE, or DELETE statements from executing while the database server prints the query plan to the output file.

  • OFF: Terminates the SET EXPLAIN statement; no further estimates are generated or written to the output file for subsequent queries.

  • FILE TO: Generates an estimate for each subsequent query and allows you to specify the location of the output file.

To view the query plan for the SQL statement below, execute it:

If you only want to see the query plan without executing the SQL statement, you can run:

If you want to view the query plan and output the result to a file of your choice on the database server, run:

Reading the Query Plan

The execution plan consists of the following sections:

(1) Execution time (OPTIMIZATION TIMESTAMP): The actual time the SQL statement was executed.

(2) Query statement: The original SQL statement for the execution plan.

(3) Estimated Cost: A value used by the optimizer to determine the query path. It is not directly related to query duration and should not be used to compare the execution efficiency of different SQL statements. However, it can be used to compare the cost of different execution plans for the same SQL.

(4) Estimated # of Rows Returned: The estimated number of rows returned by the SQL statement.

(5) Table access order

(6) Table access method

(7) Index paths used

(8) Actual execution statistics

Based on these parts of the execution plan, we will illustrate them one by one in the example below:

GBASE Insights

Through this article, we hope you now understand how to generate and view query plans in GBase 8s, and how to interpret and analyze their contents. With the query plan tool, we can optimize SQL query logic, improve database performance, and build a more efficient data processing system.

To learn more about query plan principles, practical tips, or other technical features of GBase 8s, please visit the GBase Community(www.gbase.cn).