EXPLAIN

Function

EXPLAIN and DESCRIBE are synonyms for each other, and can be used to view the structure of a specified table, or to view the execution plan of a specified SQL.

Please refer to DESCRIBE for the syntax of viewing a table structure, and the following content only describes the part of viewing the execution plan.

The execution plan will show what kind of scanning method will be used for the table referenced by the SQL statement, e.g. simple sequential scanning, index scanning, etc. If multiple tables are referenced, the execution plan will also show the JOIN algorithm used.

The most critical part of the execution plan is the statement's expected execution overhead, which is the plan generator estimates how long it will take to execute the statement.

If the ANALYZE option is specified, the statement is executed, and then statistics are displayed based on the actual run results, including the total time overhead in milliseconds and the total number of rows actually returned within each plan node. This is useful for determining whether the plan generator's estimates are close to reality.

Precautions

• The statement is executed when the ANALYZE option is specified. If the user wants to use EXPLAIN to analyze INSERT, UPDATE, DELETE, CREATE TABLE AS, or EXECUTE statements and does not want to alter the data (executing these statements affects the data), use the following method.

  START TRANSACTION;
  EXPLAIN ANALYZE ...;
  ROLLBACK;

• Since the parameters DETAIL, NODES, and NUM_NODES are functions in distributed mode, their use is prohibited in standalone mode. If they are used, the following error is generated.

  MogDB=# create table student(id int, name char(20));
  CREATE TABLE
  MogDB=# explain (nodes true) insert into student values(5,'a'),(6,'b');
  ERROR:  unrecognized EXPLAIN option "nodes"
  MogDB=# explain (num_nodes true) insert into student values(5,'a'),(6,'b');
  ERROR:  unrecognized EXPLAIN option "num_nodes"

Syntax

• Displays the execution plan of the SQL statement, supports multiple options, and has no requirement for the order of the options.

  {EXPLAIN | DESCRIBE | DESC} [ (  option  [, ...] )  ] statement;

  or

  {EXPLAIN | DESCRIBE | DESC} [FORMAT = format_name] statement;

  or

  {EXPLAIN | DESCRIBE | DESC} [EXTENDED] statement;

  where {EXPLAIN | DESCRIBE | DESC} means that using DESCRIBE, DESC and EXPLAIN are equivalent.

  The syntax of the option clause is:

  ANALYZE [ boolean ] |
      ANALYSE [ boolean ] |
      VERBOSE [ boolean ] |
      COSTS [ boolean ] |
      CPU [ boolean ] |
      DETAIL [ boolean ] | (Not available)
      NODES [ boolean ] | (Not available)
      NUM_NODES [ boolean ] | (Not available)
      BUFFERS [ boolean ] |
      TIMING [ boolean ] |
      PLAN [ boolean ] |
      FORMAT { TEXT | XML | JSON | YAML }

• Display the execution plan of the SQL statement and give the options in order.

  {EXPLAIN | DESCRIBE | DESC} { [  { ANALYZE  | ANALYSE  }  ] [ VERBOSE  ]  | PERFORMANCE  } statement;

Parameter Description

• statement

  Specifies the SQL statement to be analyzed.

• ANALYZE boolean | ANALYSE boolean

  Displays the actual runtime and other statistics.

  Takes a range of values:

  • TRUE (default): Displays the actual running time and other statistics.
  • FALSE: Do not display.

• VERBOSE boolean

  Displays additional information about the program.

  Range of values:

  • TRUE (default): additional information is displayed.
  • FALSE: not displayed.

• COSTS boolean.

  Includes the estimated total cost for each planning node, as well as the estimated number of rows and the width of each row.

  Takes a range of values:

  • TRUE (default value): the estimated total cost and width are displayed.
  • FALSE: not displayed.

• CPU boolean.

  Prints information about CPU usage.

  Range of values:

  • TRUE (default value): displays CPU utilization.
  • FALSE: Not displayed.

• DETAIL boolean (not available)

  Prints information on the database node.

  Takes a range of values:

  • TRUE (default value): prints information on the database node.
  • FALSE: Do not print.

• NODES boolean (not available)

  Prints information on the node on which the query was executed.

  Takes a range of values:

  • TRUE (default): prints information about the node on which the query was executed.
  • FALSE: do not print.

• NUM_NODES boolean (not available)

  Prints information about the number of nodes in execution.

  Takes a range of values:

  • TRUE (default value): prints information about the number of database nodes.
  • FALSE: not printed.

• BUFFERS boolean.

  Includes information about buffer usage.

  Takes a range of values:

  • TRUE: Displays the usage of the buffers.
  • FALSE (default value): not displayed.

• TIMING boolean.

  Includes information about the actual startup time and time spent on the output node.

  Takes a range of values:

  • TRUE (default value): information about the startup time and the time spent on the output node is displayed.
  • FALSE: not displayed.

• PLAN.

  Whether to store execution plans in plan_table. When this option is on, the execution plan is stored in PLAN_TABLE and is not printed to the current screen, so this option cannot be used in conjunction with other options when it is on.

  Range of values:

  • ON (default): stores the execution plan in PLAN_TABLE and does not print to the current screen. Successful execution returns EXPLAIN SUCCESS.
  • OFF: does not store the execution plan, prints the execution plan to the current screen.

• FORMAT.

  Specifies the output format.

  The range of values: TEXT, XML, JSON and YAML.

  Default: TEXT.

• PERFORMANCE

  When this option is used, all relevant information from the execution is printed.

• format_name

  Specifies the output format.

  Range of values: JSON or TRADITIONAL.

  Default: TRADITIONAL

• EXTENDED

  Optional, makes no difference.

Examples

-- 1. First of all, create a compatibility for the B mode of the database, and switch to the database
MogDB=# create database MogDB with dbcompatibility 'B';
CREATE DATABASE
MogDB=# \c MogDB
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "MogDB" as user "omm".

-- 2. Create a table on the new database
MogDB=# create table test_t(c1 int, c2 varchar(30));
CREATE TABLE

-- 3. View the SQL execution plan
MogDB=# explain select * from test_t;
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on test_t  (cost=0.00..17.29 rows=729 width=82)
(1 row)

-- 4. You can specify the output format when viewing the plan
-- Note: The json format is only supported if explain_perf_mode is normal.
MogDB=# SET explain_perf_mode=normal;
SET
MogDB=# explain (format json) select * from test_t;
            QUERY PLAN
----------------------------------
 [                               +
   {                             +
     "Plan": {                   +
       "Node Type": "Seq Scan",  +
       "Relation Name": "test_t",+
       "Alias": "test_t",        +
       "Startup Cost": 0.00,     +
       "Total Cost": 17.29,      +
       "Plan Rows": 729,         +
       "Plan Width": 82          +
     }                           +
   }                             +
 ]
(1 row)

MogDB=# explain format=json select * from test_t;
            QUERY PLAN
----------------------------------
 [                               +
   {                             +
     "Plan": {                   +
       "Node Type": "Seq Scan",  +
       "Relation Name": "test_t",+
       "Alias": "test_t",        +
       "Startup Cost": 0.00,     +
       "Total Cost": 17.29,      +
       "Plan Rows": 729,         +
       "Plan Width": 82          +
     }                           +
   }                             +
 ]
(1 row)

-- 5. If the columns of the where clause in a query are indexed, different execution plans may be displayed when the conditions are different, or the amount of data, etc. is different
MogDB=# create index idx_test_t_c1 on test_t(c1);
CREATE INDEX
MogDB=# insert into test_t values(generate_series(1, 200), 'hello MogDB');
INSERT 0 200
MogDB=# explain select c1, c2 from test_t where c1=100;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Bitmap Heap Scan on test_t  (cost=4.28..12.74 rows=4 width=82)
   Recheck Cond: (c1 = 100)
   ->  Bitmap Index Scan on idx_test_t_c1  (cost=0.00..4.28 rows=4 width=0)
         Index Cond: (c1 = 100)
(4 rows)

-- 6. You can specify whether or not to show overhead with the costs option.
MogDB=# explain (costs false) select * from test_t where c1=100;
                QUERY PLAN
------------------------------------------
 Bitmap Heap Scan on test_t
   Recheck Cond: (c1 = 100)
   ->  Bitmap Index Scan on idx_test_t_c1
         Index Cond: (c1 = 100)
(4 rows)

-- 7. In a B-compatible database, explain and desc (describe) are equivalent and can be used to view table structure information.
MogDB=# explain test_t;
 Field |         Type          | Null | Key | Default | Extra
-------+-----------------------+------+-----+---------+-------
 c1    | integer               | YES  | MUL | NULL    |
 c2    | character varying(30) | YES  |     | NULL    |
(2 rows)

Helpful Links

ANALYZE | ANALYSE, DESCRIBE