本文介绍在不同操作场景下使用云原生数据仓库AnalyticDB PostgreSQL版时的一些具体建议。选择合适的操作实践将有效地帮助您提高AnalyticDB PostgreSQL的性能。

收集统计信息

AnalyticDB PostgreSQL的优化器在进行查询优化时，会根据统计信息进行查询代价估算和优化。如果参与查询的表没有收集过统计信息或统计信息过旧，系统将按照默认值或老旧的统计信息进行优化，往往无法生成最优执行计划。所以，建议在大批量数据加载完成，或者有较多数据（超过20%）更新后，进行统计信息收集。

采用ANALYZE命令收集统计信息时，可以对所有表收集、对某个表的所有列收集或对表的指定列收集。对于大部分用户，建议采用对所有表收集或对表的所有列收集的方式。如果想对统计信息收集环节做精细化控制，可以采用对表的指定列收集方式，针对关联（JOIN）的条件列、过滤条件列、有索引的列进行统计信息收集。

示例

  • 收集所有表的统计信息示例（推荐数据大批量入库后使用）： 
    ANALYZE;
  • 收集表t的所有列的统计信息示例（推荐某个表插入/更新/删除较多数据后使用）： 
    ANALYZE t;
  • 收集表t的a列的统计信息示例： 
    ANALYZE t(a);

两种优化器的选择

AnalyticDB PostgreSQL有两个SQL优化器可供选择，两个优化器在不同的场景下，各有优势。

  • Legacy优化器

    Legacy优化器的SQL优化耗时较短，适合高并发的简单查询场景（3表以内关联），或者高并发的数据写入或更新场景（INSERT/UPDATE/DELTE）。

  • ORCA优化器

    ORCA优化器为面向复杂SQL语句的优化器，会遍历更多执行路径，制定最优执行计划，但SQL优化过程相对耗时稍长。建议对复杂查询（3表以上关联为主的场景）为主的 ETL场景和报表场景采用ORCA优化器。此外，ORCA优化器具有相关子查询的解关联优化及动态分区裁剪优化等能力，含有相关子查询的语句及含有带参数化过滤条件的分区表的语句建议使用ORCA优化器。

Session会话级设置方式：

-- 使用Legacy优化器
set optimizer = off;
-- 使用ORCA优化器
set optimizer = on;

查看当前的优化器的方式：

show optimizer;
-- 值为on：表示当前优化器为ORCA优化器
-- 值为off：表示当前优化器为Legacy优化器
说明
  • AnalyticDB PostgreSQL 4.3版的默认优化器为Legacy优化器，AnalyticDB PostgreSQL 6.0版的默认优化器为ORCA优化器。
  • 实例级别设置请提交工单联系技术支持进行修改。

使用索引加速查询

当查询中有等值过滤条件或范围过滤条件，且过滤后数据量较少时，可考虑在条件列上建立索引，提升数据扫描的速度。AnalyticDB PostgreSQL目前支持3种索引：

  • BTree：适用于唯一值较多的数据列，列上有过滤条件或Join条件，或为排序列。
  • Bitmap：适用于唯一值较少的数据列，查询中该列上有多个过滤条件。
  • Gist：适用于地理位置、范围数据类型、图像特征值数据、几何类数据等。

示例

无索引时，带条件的表数据获取采用全表扫描再进行过滤的方式：

postgres=# EXPLAIN SELECT * FROM t WHERE b = 1;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
   ->  Table Scan on t  (cost=0.00..431.00 rows=1 width=16)
         Filter: b = 1
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(5 rows)

使用如下的语句在t表的b列上建立BTree索引：

postgres=# CREATE INDEX i_t_b ON t USING btree (b);
CREATE INDEX

有索引时，带条件的表数据获取采用索引方式：

postgres=# EXPLAIN SELECT * FROM t WHERE b = 1;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..2.00 rows=1 width=16)
   ->  Index Scan using i_t_b on t  (cost=0.00..2.00 rows=1 width=16)
         Index Cond: b = 1
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(5 rows)

查看执行计划

执行计划是数据库运行SQL的步骤，相当于算法。查看查询的执行计划有助于分析查询的执行过程，分析慢SQL的瓶颈点，帮助我们明确优化方向。可以通过在查询前加explain关键字，查看查询的执行计划，此时只显示查询的执行计划，而不会执行该语句。也可以在查询前加explain analyze关键字，其会运行该语句，收集查询时真实的执行信息，并在查询计划上显示出来。

  • explain示例：

    postgres=# EXPLAIN SELECT a, b FROM t;
                                  QUERY PLAN
------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..4.00 rows=100 width=8)
   ->  Seq Scan on t  (cost=0.00..4.00 rows=34 width=8)
 Optimizer status: legacy query optimizer
(3 rows)

  • explain analyze示例：

    postgres=# EXPLAIN ANALYZE SELECT a, b FROM t;
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..4.00 rows=100 width=8)
   Rows out:  100 rows at destination with 2.728 ms to first row, 2.838 ms to end, start offset by 0.418 ms.
   ->  Seq Scan on t  (cost=0.00..4.00 rows=34 width=8)
         Rows out:  Avg 33.3 rows x 3 workers.  Max 37 rows (seg2) with 0.088 ms to first row, 0.107 ms to end, start offset by 2.887 ms.
 Slice statistics:
   (slice0)    Executor memory: 131K bytes.
   (slice1)    Executor memory: 163K bytes avg x 3 workers, 163K bytes max (seg0).
 Statement statistics:
   Memory used: 128000K bytes
 Optimizer status: legacy query optimizer
 Total runtime: 3.739 ms
(11 rows)

执行计划由一系列的算子及其信息按照执行逻辑顺序有机组合在一起，并按此以流水线方式执行，进行数据处理。

算子种类：

  • 数据扫描算子：Seq Scan、Table Scan、Index Scan、Bitmap Scan等。
  • 连接算子：Hash Join、Nested Loop、Merge Join
  • 聚集算子：Hash Aggregate、Group Aggregate
  • 分布式算子：Redistribute Motion、Broadcast Motion、Gather Motion
  • 其他算子：Hash、Sort、Limit、Append等
postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.b = t2.b;
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice3; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.b = t2.b
         ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
               Hash Key: t1.b
               ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
                     Hash Key: t2.b
                     ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(12 rows)

在上述执行计划中，进行了如下的计算过程：

  1. Table Scan算子对表t1和t2进行表扫描操作。
  2. Redistribute Motion算子分别按照t1.b和t2.b的Hash值对t1表和t2表进行了数据重分布（Redistribute Motion），将数据重新分配到了各个节点上，以便于进行Join计算。
  3. Hash算子在t2表上建了一个用于Join的Hash表。
  4. Hash Join算子对两个表的数据做了Join计算。
  5. Gather Motion算子将计算结果传输到前端总控节点，进而传输到客户端。

执行计划的整体结构如上所述，具体执行计划会随查询语句的不同发生变化。

消除分布式（Motion）算子提升性能

在进行连接或聚集操作时，AnalyticDB PostgreSQL会根据数据分布情况添加分布式算子，对数据进行重分布（Redistribute Motion）或广播（Broadcast Motion）。分布式算子会占用大量的网络资源。如果能够通过建表和业务逻辑进行分布式算子的规避，则能够提升数据库查询性能。

基本原理

如果表定义时设置的分布键与业务逻辑并不匹配，则需对分布键进行调整，尽可能减少查询中的分布式算子。

示例

SELECT * FROM t1, t2 WHERE t1.a=t2.a;

其中，t1表的分布键为t1.a。

  • 如果t2表的分布列是t2.b，会出现t2表的重分布。 
    postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice2; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.a = t2.a
         ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
                     Hash Key: t2.a
                     ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(10 rows)
  • 如果t2表的分布列是t2.a，则无需重分布就可以直接Join。 
    postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.a = t2.a
         ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(8 rows)

优化关联（JOIN）列的数据类型

Join的条件列数据类型应一致，避免因隐式/显式数据类型转换带来数据需要重分布的问题。

  • 显式数据类型转换

    显式数据类型转换是指在SQL语句中，对Join条件列的数据类型进行强制类型转换。比如表t的a列是int类型，但是在Join条件中将其转换为numeric类型（即有t.a::numeric的转换）。

    数据进行类型转换后，其hash函数/hash值会发生变化，SQL语句中应尽量避免在Join条件列上进行类型转换。

    下面例子表明在数据类型转换后，会导致数据的重分布：

    --无数据类型转换
postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.a = t2.a
         ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(8 rows)

--有强制数据类型转换
postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a::numeric;
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice3; segments: 3)  (cost=0.00..862.00 rows=1 width=32)
   ->  Hash Join  (cost=0.00..862.00 rows=1 width=32)
         Hash Cond: t1.a::numeric = t2.a::numeric
         ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
               Hash Key: t1.a::numeric
               ->  Table Scan on t1  (cost=0.00..431.00 rows=1 width=16)
         ->  Hash  (cost=431.00..431.00 rows=1 width=16)
               ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=0.00..431.00 rows=1 width=16)
                     Hash Key: t2.a::numeric
                     ->  Table Scan on t2  (cost=0.00..431.00 rows=1 width=16)
 Settings:  optimizer=on
 Optimizer status: PQO version 1.609
(12 rows)
  • 隐式数据类型转换

    隐式数据类型转换是指在Join条件两边数据类型不完全一致，会导致数据库对其中一列的数据类型进行转换。

    数据库对其中一列进行数据类型转换后，可能会引发原始数据类型与新数据类型的Hash函数/Hash值不一致，需要进行重分布。所以，在表设计阶段，参与Join的两个表，Join条件的类型应尽可能统一，避免因为数据类型不同而导致需要额外重分布数据的问题。

    下面的例子中，t1.a为timestamp without time zone类型，t2.a为timestamp with time zone类型，他们的Hash函数不一致，在Join时需要进行重分布后再进行Join。

    postgres=# CREATE TABLE t1 (a timestamp without time zone);
CREATE TABLE
postgres=# CREATE TABLE t2 (a timestamp with time zone);
CREATE TABLE
postgres=#
postgres=# EXPLAIN SELECT * FROM t1, t2 WHERE t1.a=t2.a;
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice2; segments: 3)  (cost=0.04..0.11 rows=4 width=16)
   ->  Nested Loop  (cost=0.04..0.11 rows=2 width=16)
         Join Filter: t1.a = t2.a
         ->  Seq Scan on t1  (cost=0.00..0.00 rows=1 width=8)
         ->  Materialize  (cost=0.04..0.07 rows=1 width=8)
               ->  Broadcast Motion 3:3  (slice1; segments: 3)  (cost=0.00..0.04 rows=1 width=8)
                     ->  Seq Scan on t2  (cost=0.00..0.00 rows=1 width=8)
(7 rows)

数据倾斜的检查和处理

如果出现查询异常缓慢，或者资源利用率不均匀的情况，则需要确认是否出现了数据倾斜。

可以通过如下方式检查数据是否发生倾斜：检查某个表在各个节点上的数据分布计数，如果各节点上的数据分布明显不均匀，则需要对该表的分布键进行调整。

postgres=# SELECT gp_segment_id, count(1) FROM t1 GROUP BY 1 ORDER BY 2 DESC;
 gp_segment_id | count
---------------+-------
             0 | 16415
             2 |    37
             1 |    32
(3 rows)

如果发生倾斜，建议重新合理定义分布键，更改分布键有两种方式：

  • 重新建表：新建表时调整分布键。
  • 直接更改表的分布键：ALTER TABLE t1 SET DISTRIBUTED BY (b);

查看正在运行的语句状态

数据库中正在并发执行的语句过多，会导致系统资源不足，查询执行缓慢。

通过pg_stat_activity视图查看数据库的运行状况，该视图将列出系统中所有正在并发执行的语句，可以通过观察query_start字段（该查询的执行开始时间）来判断某查询是否有执行时长上的异常。

例如：

postgres=# SELECT * FROM pg_stat_activity;
 datid | datname  | procpid | sess_id | usesysid |  usename   |          current_query          | waiting |          query_start          |         backend_start         | client_addr | client_port | application_name |          xact_start           | waiting_reason
-------+----------+---------+---------+----------+------------+---------------------------------+---------+-------------------------------+-------------------------------+-------------+-------------+------------------+-------------------------------+----------------
 10902 | postgres |   53666 |       7 |       10 | yineng.cyn | select * from pg_stat_activity; | f       | 2019-05-13 20:27:12.058656+08 | 2019-05-13 20:16:14.179612+08 |             |          -1 | psql             | 2019-05-13 20:27:12.058656+08 |
 10902 | postgres |   54158 |       9 |       10 | yineng.cyn | select * from t t1, t t2;       | f       | 2019-05-13 20:26:28.138641+08 | 2019-05-13 20:17:40.368623+08 |             |          -1 | psql             | 2019-05-13 20:26:28.138641+08 |
(2 rows)

其中，比较关键的字段为：

  • procpid：执行该查询的Master进程号。
  • usename：执行该查询的用户名。
  • current_query：查询文本。
  • waiting：查询是否处于等待状态。
  • query_start：查询执行开始时间。
  • backend_start：执行该查询的进程启动时间。
  • xact_start：该查询所在事务开始时间。
  • waiting_reason：查询等待的原因。

此外，可以通过在current_query列添加current_query != '<IDLE>'条件，查看正在运行的SQL的信息： 

SELECT * FROM pg_stat_activity WHERE current_query != '<IDLE>';

查看耗时最长的5条语句：

SELECT current_timestamp - query_start as runtime
, datname
, usename
, current_query
FROM pg_stat_activity
WHERE current_query != '<IDLE>'
ORDER BY runtime DESC
LIMIT 5;

判断当前锁的状况

如果数据库中的对象被加锁，并且长时间没有释放，则可能导致其他查询一直处于等锁状态，会影响其他查询的正常执行。通过如下语句查看数据库中被锁的表：

SELECT pgl.locktype AS locktype
, pgl.database AS database
, pgc.relname AS relname
, pgl.relation AS relation
, pgl.transaction AS transaction
, pgl.pid AS pid
, pgl.mode AS mode
, pgl.granted AS granted
, pgsa.current_query AS query
FROM pg_locks pgl
JOIN pg_class pgc ON pgl.relation = pgc.oid
JOIN pg_stat_activity pgsa ON pgl.pid = pgsa.procpid
ORDER BY pgc.relname;

如果检查出某个查询被hang住，其原因为在等锁，可查看查询涉及的表被加锁的情况，如有必要可采用如下方式进行人工干预：

  • 取消进行中的查询，如果该pid中无查询，为IDLE状态，则执行不起作用。另外取消这个query需要花费一定的时间来做清理和事务的回滚。 
    SELECT pg_cancel_backend(pid);
    说明 pg_cancel_backend对pg_stat_activity.current_query为<IDLE>状态的session不起作用，可以用pg_terminate_backend来清理。
  • 中断session，如果有未提交事务，也会被回滚掉。 
    SELECT pg_terminate_backend(pid);

使用 Nest Loop JOIN 提升性能

在实例缺省状态下，AnalyticDB PostgreSQL没有启用Nested Loop JOIN（嵌套连接）。对于只涉及或返回少部分数据的查询，性能可能不是最优的。

例如下面的SQL语句：

SELECT *
FROM t1 join t2 on t1.c1 = t2.c1
WHERE t1.c2 >= '230769548' and t1.c2 < '230769549'
LIMIT 100;

其特点是t1和t2表都比较大，t1上的选择条件（t1.c2 >= '230769548' and t1.c2 < '23432442'）过滤了绝大多数数据记录，且有LIMIT子句，所以查询实际上只涉及总数据量中的一小部分。这种情况下，使用Nested Loop连接方式是较优的。

要使用Nested Loop连接，需要执行一下SET命令，如下所示：

show enable_nestloop ;
 enable_nestloop
-----------------
 off
SET enable_nestloop = on ;
show enable_nestloop ;
 enable_nestloop
-----------------
 on
explain SELECT * FROM t1 join t2 on t1.c1 = t2.c1 WHERE t1.c2 >= '230769548' and t1.c2 < '23432442' LIMIT 100;
                                            QUERY PLAN
-----------------------------------------------------------------------------------------------
 Limit  (cost=0.26..16.31 rows=1 width=18608)
   ->  Nested Loop  (cost=0.26..16.31 rows=1 width=18608)
         ->  Index Scan using t1 on c2  (cost=0.12..8.14 rows=1 width=12026)
               Filter: ((c2 >= '230769548'::bpchar) AND (c2 < '230769549'::bpchar))
         ->  Index Scan using t2 on c1  (cost=0.14..8.15 rows=1 width=6582)
               Index Cond: ((c1)::text = (T1.c1)::text)

可以发现，t1和t2两张表是使用Nested Loop连接的，从而获得了较优的性能。