背景
PostgreSQL 提供了 EXPLAIN 命令用于 SQL 语句的性能分析。它能够输出 SQL 对应的查询计划，以及在执行过程中的具体耗时、资源消耗等信息，可用于排查 SQL 的性能瓶颈。
EXPLAIN 命令原先只适用于单机执行的 SQL 性能分析。PolarDB-PG 的 ePQ 弹性跨机并行查询扩展了 EXPLAIN 的功能，使其可以打印 ePQ 的跨机并行执行计划，还能够统计 ePQ 执行计划在各个算子上的执行时间、数据扫描量、内存使用量等信息，并以统一的视角返回给客户端。
功能介绍
执行计划查看
ePQ 的执行计划是分片的。每个计划分片（Slice）由计算节点上的虚拟执行单元（Segment）启动的一组进程（Gang）负责执行，完成 SQL 的一部分计算。ePQ 在执行计划中引入了 Motion 算子，用于在执行不同计划分片的进程组之间进行数据传递。因此，Motion 算子就是计划分片的边界。
ePQ 中总共引入了三种 Motion 算子：
PX Coordinator：源端数据发送到同一个目标端（汇聚）PX Broadcast：源端数据发送到每一个目标端（广播）PX Hash：源端数据经过哈希计算后发送到某一个目标端（重分布）
以一个简单查询作为例子：
=> 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)
以上执行计划以 Motion 算子为界，被分为了两个分片：一个是接收最终结果的分片 slice0，一个是扫描数据的分片slice1。对于 slice1 这个计划分片，ePQ 将使用六个执行单元（segments: 6）分别启动一个进程来执行，这六个进程各自负责扫描表的一部分数据（Partial Seq Scan），通过 Motion 算子将六个进程的数据汇聚到一个目标端（PX Coordinator 6:1），传递给 Limit 算子。
如果查询逐渐复杂，则执行计划中的计划分片和 Motion 算子会越来越多：
=> 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)
以上执行计划中总共有三个计划分片。将会有六个进程（segments: 6）负责执行 slice2 分片，分别扫描表的一部分数据，然后通过 Motion 算子（PX Hash 6:6）将数据重分布到另外六个（segments: 6）负责执行 slice1 分片的进程上，各自完成排序（Sort）和聚合（GroupAggregate），最终通过 Motion 算子（PX Coordinator 6:1）将数据汇聚到结果分片 slice0。
olarDB-PG 的 ePQ 弹性跨机并行查询特性提供了精细的粒度控制方法，可以合理使用集群内的计算资源。在最大程度利用闲置计算资源进行并行查询，提升资源利用率的同时，避免了对其它业务负载产生影响：
- ePQ 可以动态调整集群中参与并行查询的计算节点范围，避免使用负载较高的计算节点
- ePQ 支持为每条查询动态调整在计算节点上的并行度，避免 ePQ 并行查询进程对计算资源的消耗影响到相同节点上的其它进程
计算节点范围选择
参数 polar_px_nodes 指定了参与 ePQ 的计算节点范围，默认值为空，表示所有只读节点都参与 ePQ 并行查询：
=> SHOW polar_px_nodes;
polar_px_nodes
----------------
(1 row)
如果希望读写节点也参与 ePQ 并行，则可以设置如下参数：
SET polar_px_use_primary TO ON;
如果部分只读节点负载较高，则可以通过修改 polar_px_nodes 参数设置仅特定几个而非所有只读节点参与 ePQ 并行查询。参数 polar_px_nodes 的合法格式是一个以英文逗号分隔的节点名称列表。获取节点名称需要安装 polar_monitor 插件：
CREATE EXTENSION IF NOT EXISTS polar_monitor;
通过 polar_monitor 插件提供的集群拓扑视图，可以查询到集群中所有计算节点的名称：
=> SELECT name,slot_name,type FROM polar_cluster_info;
name | slot_name | type
-------+-----------+---------
node0 | | Primary
node1 | standby1 | Standby
node2 | replica1 | Replica
node3 | replica2 | Replica
(4 rows)
其中：
Primary表示读写节点Replica表示只读节点Standby表示备库节点
通用的最佳实践是使用负载较低的只读节点参与 ePQ 并行查询：
=> SET polar_px_nodes = 'node2,node3';
=> SHOW polar_px_nodes;
polar_px_nodes
----------------
node2,node3
(1 row)
并行度控制
参数 polar_px_dop_per_node 用于设置当前会话中的 ePQ 查询在每个计算节点上的执行单元（Segment）数量，每个执行单元会为其需要执行的每一个计划分片（Slice）启动一个进程。
该参数默认值为 3，通用最佳实践值为当前计算节点 CPU 核心数的一半。如果计算节点的 CPU 负载较高，可以酌情递减该参数，控制计算节点的 CPU 占用率至 80% 以下；如果查询性能不佳时，可以酌情递增该参数，也需要保持计算节点的 CPU 水位不高于 80%。否则可能会拖慢其它的后台进程。
并行度计算方法示例
创建一张表：
CREATE TABLE test(id INT);
假设集群内有两个只读节点，polar_px_nodes 为空，此时 ePQ 将使用集群内的所有只读节点参与并行查询；参数 polar_px_dop_per_node 的值为 3，表示每个计算节点上将会有三个执行单元。执行计划如下：
=> SHOW polar_px_nodes;
polar_px_nodes
----------------
(1 row)
=> SHOW polar_px_dop_per_node;
polar_px_dop_per_node
-----------------------
3
(1 row)
=> EXPLAIN SELECT * FROM test;
QUERY PLAN
-------------------------------------------------------------------------------
PX Coordinator 6:1 (slice1; segments: 6) (cost=0.00..431.00 rows=1 width=4)
-> Partial Seq Scan on test (cost=0.00..431.00 rows=1 width=4)
Optimizer: PolarDB PX Optimizer
(3 rows)
从执行计划中可以看出，两个只读节点上总计有六个执行单元（segments: 6）将会执行这个计划中唯一的计划分片 slice1。这意味着总计会有六个进程并行执行当前查询。
此时，调整 polar_px_dop_per_node 为 4，再次执行查询，两个只读节点上总计会有八个执行单元参与当前查询。由于执行计划中只有一个计划分片 slice1，这意味着总计会有八个进程并行执行当前查询：
=> SET polar_px_dop_per_node TO 4;
SET
=> EXPLAIN SELECT * FROM test;
QUERY PLAN
-------------------------------------------------------------------------------
PX Coordinator 8:1 (slice1; segments: 8) (cost=0.00..431.00 rows=1 width=4)
-> Partial Seq Scan on test (cost=0.00..431.00 rows=1 width=4)
Optimizer: PolarDB PX Optimizer
(3 rows)
此时，如果设置 polar_px_use_primary 参数，让读写节点也参与查询，那么读写节点上也将会有四个执行单元参与 ePQ 并行执行，集群内总计 12 个进程参与并行执行：
=> SET polar_px_use_primary TO ON;
SET
=> EXPLAIN SELECT * FROM test;
QUERY PLAN
---------------------------------------------------------------------------------
PX Coordinator 12:1 (slice1; segments: 12) (cost=0.00..431.00 rows=1 width=4)
-> Partial Seq Scan on test (cost=0.00..431.00 rows=1 width=4)
Optimizer: PolarDB PX Optimizer
(3 rows)
在使用 PostgreSQL 时，如果想要在一张表中查询符合某个条件的行，默认情况下需要扫描整张表的数据，然后对每一行数据依次判断过滤条件。如果符合条件的行数非常少，而表的数据总量非常大，这显然是一个非常低效的操作。与阅读书籍类似，想要阅读某个特定的章节时，读者通常会通过书籍开头处的索引查询到对应章节的页码，然后直接从指定的页码开始阅读；在数据库中，通常会对被频繁查找的列创建索引，以避免进行开销极大的全表扫描：通过索引可以精确定位到被查找的数据位于哪些数据页面上。
PostgreSQL 支持创建多种类型的索引，其中使用得最多的是 B-Tree 索引，也是 PostgreSQL 默认创建的索引类型。在一张数据量较大的表上创建索引是一件非常耗时的事，因为其中涉及到的工作包含：
- 顺序扫描表中的每一行数据
- 根据要创建索引的列值（Scan Key）顺序，对每行数据在表中的物理位置进行排序
- 构建索引元组，按 B-Tree 的结构组织并写入索引页面
PostgreSQL 支持并行（多进程扫描/排序）和并发（不阻塞 DML）创建索引，但只能在创建索引的过程中使用单个计算节点的资源。
PolarDB-PG 的 ePQ 弹性跨机并行查询特性支持对 B-Tree 类型的索引创建进行加速。ePQ 能够利用多个计算节点的 I/O 带宽并行扫描全表数据，并利用多个计算节点的 CPU 和内存资源对每行数据在表中的物理位置按索引列值进行排序，构建索引元组。最终，将有序的索引元组归并到创建索引的进程中，写入索引页面，完成索引的创建。
ePQ 支持创建 B-Tree 索引并行加速
使用方法
数据准备
创建一张包含三个列，数据量为 1000000 行的表：
CREATE TABLE t (id INT, age INT, msg TEXT);
INSERT INTO t
SELECT
random() * 1000000,
random() * 10000,
md5(random()::text)
FROM generate_series(1, 1000000);
创建索引
使用 ePQ 创建索引需要以下三个步骤：
- 设置参数
polar_enable_px为ON，打开 ePQ 的开关 - 按需设置参数
polar_px_dop_per_node调整查询并行度 - 在创建索引时显式声明
px_build属性为ON
SET polar_enable_px TO ON;
SET polar_px_dop_per_node TO 8;
CREATE INDEX t_idx1 ON t(id, msg) WITH(px_build = ON);
在创建索引的过程中，数据库会对正在创建索引的表施加 ShareLock 锁。这个级别的锁将会阻塞其它进程对表的 DML 操作（INSERT / UPDATE / DELETE）。
并发创建索引
类似地，ePQ 支持并发创建索引，只需要在 CREATE INDEX 后加上 CONCURRENTLY 关键字即可：
SET polar_enable_px TO ON;
SET polar_px_dop_per_node TO 8;
CREATE INDEX CONCURRENTLY t_idx2 ON t(id, msg) WITH(px_build = ON);
在创建索引的过程中，数据库会对正在创建索引的表施加 ShareUpdateExclusiveLock 锁。这个级别的锁将不会阻塞其它进程对表的 DML 操作。
使用限制
ePQ 加速创建索引暂不支持以下场景：
- 创建
UNIQUE索引 - 创建索引时附带
INCLUDING列 - 创建索引时指定
TABLESPACE - 创建索引时带有
WHERE而成为部分索引（Partial Index）
ePQ 支持创建/刷新物化视图并行加速和批量写入
背景
物化视图 (Materialized View) 是一个包含查询结果的数据库对象。与普通的视图不同，物化视图不仅保存视图的定义，还保存了 创建物化视图 时的数据副本。当物化视图的数据与视图定义中的数据不一致时，可以进行 物化视图刷新 (Refresh) 保持物化视图中的数据与视图定义一致。物化视图本质上是对视图定义中的查询做预计算，以便于在查询时复用。
CREATE TABLE AS 语法用于将一个查询所对应的数据构建为一个新的表，其表结构与查询的输出列完全相同。
SELECT INTO 语法用于建立一张新表，并将查询所对应的数据写入表中，而不是将查询到的数据返回给客户端。其表结构与查询的输出列完全相同。
功能原理介绍
对于物化视图的创建和刷新，以及 CREATE TABLE AS / SELECT INTO 语法，由于在数据库层面需要完成的工作步骤十分相似，因此 PostgreSQL 内核使用同一套代码逻辑来处理这几种语法。内核执行过程中的主要步骤包含：
- 数据扫描：执行视图定义或
CREATE TABLE AS/SELECT INTO语法中定义的查询，扫描符合查询条件的数据 - 数据写入：将上述步骤中扫描到的数据写入到一个新的物化视图 / 表中
PolarDB for PostgreSQL 对上述两个步骤分别引入了 ePQ 并行扫描和批量数据写入的优化。在需要扫描或写入的数据量较大时，能够显著提升上述 DDL 语法的性能，缩短执行时间：
- ePQ 并行扫描：通过 ePQ 功能，利用多个计算节点的 I/O 带宽和计算资源并行执行视图定义中的查询，提升计算资源和带宽的利用率
- 批量写入：不再将扫描到的每一个元组依次写入表或物化视图，而是在内存中攒够一定数量的元组后，一次性批量写入表或物化视图中，减少记录 WAL 日志的开销，降低对页面的锁定频率
使用说明
ePQ 并行扫描
将以下参数设置为 ON 即可启用 ePQ 并行扫描来加速上述语法中的查询过程，目前其默认值为 ON。该参数生效的前置条件是 ePQ 特性的总开关 polar_enable_px 被打开。
SET polar_px_enable_create_table_as = ON;
由于 ePQ 特性的限制，该优化不支持 CREATE TABLE AS ... WITH OIDS 语法。对于该语法的处理流程中将会回退使用 PostgreSQL 内置优化器为 DDL 定义中的查询生成执行计划，并通过 PostgreSQL 的单机执行器完成查询。
批量写入
将以下参数设置为 ON 即可启用批量写入来加速上述语法中的写入过程，目前其默认值为 ON。
SET polar_enable_create_table_as_bulk_insert = ON; 
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
