PolarDB:Query and analyze ePQ execution plans

Background information

PostgreSQL provides the EXPLAIN command that you can use to analyze the performance of SQL statements. The command output is the query plan of an SQL statement, including information such as the time and resources consumed for execution. You can use the command to troubleshoot SQL performance bottlenecks.

However, the EXPLAIN command is applicable only to performance analysis of SQL statements that are executed on a single node. The ePQ feature of PolarDB for PostgreSQL extends the capabilities of EXPLAIN so that it can be used to query the execution plans of multi-node parallel queries that are executed by using the ePQ feature. You can also use the command to collect statistics such as the execution duration, data scan volume, and memory usage of ePQ execution plans on each operator. Such information is provided to users in a unified manner.

Prerequisites

How it works

The query coordinator (QC) of ePQ communicates with workers by using the Y protocol of libpq.

• The QC sends the EXPLAIN ANALYZE command to the workers.

• Each worker collects statistics such as the resource usage and execution duration of the local process.

• After the execution of a slice is complete, each worker sends the statistics to the QC.

• The QC waits until all workers complete their executions, performs statistical calculation, and then generates the output.

Feature description

Query an execution plan

Execution plans of ePQ are sliced. Each slice is executed by a group of processes, which is called a gang. The processes are started by segments on a compute node to complete part of SQL calculation. ePQ introduces Motion operators to execution plans for data transfer between the gangs that execute different slices. Therefore, Motion operators serve as the boundary for slices.

ePQ introduces the following three Motion operators:

• PX Coordinator: aggregates source data to the same destination.

• PX Broadcast: broadcasts source data to each destination.

• PX Hash: hashes source data and sends the data to a destination for redistribution.

Examples

• The following example shows the execution plan of a simple query:

  CREATE TABLE t (id INT);
  SET polar_enable_px TO ON;
  EXPLAIN (COSTS OFF) SELECT * FROM t LIMIT 1;
                     QUERY PLAN
  -------------------------------------------------
   Limit
     ->  PX Coordinator 6:1  (slice1; segments: 6)
           ->  Partial Seq Scan on t
   Optimizer: PolarDB PX Optimizer
  (4 rows)

  The preceding execution plan is divided into two slices by a Motion operator. slice0 is used to receive the final result and slice1 is used to scan data.

  ePQ uses six segments (segments: 6), each of which starts a process to execute slice1. Each of the six processes scans a portion of the data in the table (Partial Seq Scan), and the data of the six processes is aggregated by the Motion operator to the same destination (PX Coordinator 6:1), which is the Limit operator.

• The number of slices and Motion operators in an execution plan increases with the query complexity. The following example shows the execution plan of a complex query:

  CREATE TABLE t1 (a INT, b INT, c INT);
  SET polar_enable_px TO ON;
  EXPLAIN (COSTS OFF) SELECT SUM(b) FROM t1 GROUP BY a LIMIT 1;
                           QUERY PLAN
  ------------------------------------------------------------
   Limit
     ->  PX Coordinator 6:1  (slice1; segments: 6)
           ->  GroupAggregate
                 Group Key: a
                 ->  Sort
                       Sort Key: a
                       ->  PX Hash 6:6  (slice2; segments: 6)
                             Hash Key: a
                             ->  Partial Seq Scan on t1
   Optimizer: PolarDB PX Optimizer
  (10 rows)

  The preceding execution plan has three slices. Six processes (segments: 6) are started to execute slice2. Each of the six processes scans a portion of the data in the table. Then, a Motion operator (PX Hash 6:6) redistributes the data to six other processes (segments: 6) that are used to execute slice1. Each of the six processes completes the sort (Sort) and aggregate (GroupAggregate) operations. Finally, another Motion operation (PX Coordinator 6:1) aggregates the data to slice0.

Analyze an execution plan

You can add the ANALYZE option to the EXPLAIN command to actually execute a query and collect various statistics during the execution. In this case, the execution plan is not only displayed but also executed. In an execution plan of ePQ, an operator is executed by a group of processes. The EXPLAIN ANALYZE command of ePQ can collect statistics on all processes that execute the same operator.

The following operator-level statistics are collected:

• Operator execution duration: the longest execution duration among all processes that execute the operator.

• Total scanned rows by an operator: the sum of the rows scanned by all processes that execute the operator.

• Operator execution loops: the sum of the loops of all processes that execute the operator.

• Operator resource usage: the sum of the resource usage of all processes that execute the operator.

In addition, the EXPLAIN ANALYZE command of ePQ can collect statistics on each process that executes an operator. This allows you to determine whether data skew occurs during the execution. The following process-level statistics are collected:

• Memory usage of each process

• Execution duration of each process

• Number of rows processed by each process

Example

1. Create a table and insert data into the table.

   CREATE TABLE t2 (a INT, b INT, c VARCHAR(20));
   INSERT INTO t2 SELECT i, i*2, to_char(i, 'FM00000') FROM generate_series(1, 100000) i;

2. Set the relevant parameters and run the EXPLAIN ANALYZE command.

   SET polar_enable_px TO ON;
   SET polar_px_enable_explain_all_stat TO ON;
   SET polar_px_explain_memory_verbosity TO detail;
   EXPLAIN (COSTS OFF, ANALYZE) SELECT * FROM t2;
                                           QUERY PLAN
   -------------------------------------------------------------------------------------------
    PX Coordinator 6:1  (slice1; segments: 6) (actual time=0.816..54.225 rows=100000 loops=1)
      Executor Memory: 9kB  Workers: 1  Max: 9kB (worker -1)
      ->  Partial Seq Scan on t2 (actual time=0.052..24.732 rows=94720 loops=1)
            Executor Memory: 326kB  Workers: 6  Max: 145kB (worker 1)
            allstat:
                worker:0, first_time:7.396(ms), total_time:25(ms), total_num:94720
                worker:1, first_time:7.396(ms), total_time:2.819(ms), total_num:5280
                worker:2, first_time:7.393(ms), total_time:0.074(ms), total_num:0
                worker:3, first_time:7.400(ms), total_time:0.078(ms), total_num:0
                worker:4, first_time:7.402(ms), total_time:0.086(ms), total_num:0
                worker:5, first_time:7.399(ms), total_time:0.098(ms), total_num:0
            Dynamic Pages Per Worker: [512,29]
    Planning Time: 9.768 ms
    Optimizer: PolarDB PX Optimizer
      (slice0)    Executor memory: 38K bytes.
      (slice1)    Executor memory: 68K bytes avg x 6 workers, 164K bytes max (seg1).
    Execution Time: 65.572 ms
   (17 rows)

   In the preceding execution plan:

   • The Executor Memory section for each operator displays the total memory usage of all processes that execute the operator, the number of processes, and the ID of the process with the highest memory usage.

   • The allstat section displays the preparation time (first_time), execution duration (total_time), and number of processed tuples (total_num) for each process that executes the operator.