TDSQL PostgreSQL版优化 SQL 语句-实践教程-文档中心-腾讯云

查看是否为分布键查询
postgres=# explain select * from tbase_1 where f1=1;        
                                   QUERY PLAN                                   
\\--------------------------------------------------------------------------------
 Remote Fast Query Execution  (cost=0.00..0.00 rows=0 width=0)
   Node/s: dn001, dn002
   ->  Gather  (cost=1000.00..7827.20 rows=1 width=14)
         Workers Planned: 2
         ->  Parallel Seq Scan on tbase_1  (cost=0.00..6827.10 rows=1 width=14)
               Filter: (f1 = 1)
(6 rows) 
﻿
postgres=# explain select * from tbase_1 where f2=1;
                                   QUERY PLAN                                   
\\--------------------------------------------------------------------------------
 Remote Fast Query Execution  (cost=0.00..0.00 rows=0 width=0)
   Node/s: dn001
   ->  Gather  (cost=1000.00..7827.20 rows=1 width=14)
         Workers Planned: 2
         ->  Parallel Seq Scan on tbase_1  (cost=0.00..6827.10 rows=1 width=14)
               Filter: (f2 = 1)
(6 rows)
如上，第一个查询为非分布键查询，需要发往所有节点，这样最慢的节点决定了整个业务的速度，需要保持所有节点的响应性能一致，如第二个查询所示，业务设计查询时尽可能带上分布键。
查看是否使用索引
postgres=# create index tbase_2_f2_idx on tbase_2(f2); 
CREATE INDEX
postgres=# explain select * from tbase_2 where f2=1;
                                     QUERY PLAN                                      
\\-------------------------------------------------------------------------------------
 Remote Fast Query Execution  (cost=0.00..0.00 rows=0 width=0)
   Node/s: dn001, dn002
   ->  Index Scan using tbase_2_f2_idx on tbase_2  (cost=0.42..4.44 rows=1 width=14)
         Index Cond: (f2 = 1)
(4 rows)
﻿
postgres=# explain select * from tbase_2 where f3='1';
                                   QUERY PLAN                                   
\\--------------------------------------------------------------------------------
 Remote Fast Query Execution  (cost=0.00..0.00 rows=0 width=0)
   Node/s: dn001, dn002
   ->  Gather  (cost=1000.00..7827.20 rows=1 width=14)
         Workers Planned: 2
         ->  Parallel Seq Scan on tbase_2  (cost=0.00..6827.10 rows=1 width=14)
               Filter: (f3 = '1'::text)
(6 rows)
﻿
postgres=# 
第一个查询使用了索引，第二个没有使用索引，通常情况下，使用索引可以加速查询速度，但索引也会增加更新的开销。
查看是否为分布 key join
postgres=# explain  select tbase_1.* from tbase_1,tbase_2 where tbase_1.f1=tbase_2.f1 ;           
                                           QUERY PLAN                                           
\\------------------------------------------------------------------------------------------------
 Remote Subquery Scan on all (dn001,dn002)  (cost=29.80..186.32 rows=3872 width=40)
   ->  Hash Join  (cost=29.80..186.32 rows=3872 width=40)
         Hash Cond: (tbase_1.f1 = tbase_2.f1)
         ->  Remote Subquery Scan on all (dn001,dn002)  (cost=100.00..158.40 rows=880 width=40)
               Distribute results by S: f1
               ->  Seq Scan on tbase_1  (cost=0.00..18.80 rows=880 width=40)
         ->  Hash  (cost=18.80..18.80 rows=880 width=4)
               ->  Seq Scan on tbase_2  (cost=0.00..18.80 rows=880 width=4)
(8 rows) 
﻿
postgres=# explain  select tbase_1.* from tbase_1,tbase_2 where tbase_1.f2=tbase_2.f1 ;   
                                   QUERY PLAN                                    
\\---------------------------------------------------------------------------------
 Remote Fast Query Execution  (cost=0.00..0.00 rows=0 width=0)
   Node/s: dn001, dn002
   ->  Hash Join  (cost=18904.69..46257.08 rows=500564 width=14)
         Hash Cond: (tbase_1.f2 = tbase_2.f1)
         ->  Seq Scan on tbase_1  (cost=0.00..9225.64 rows=500564 width=14)
         ->  Hash  (cost=9225.64..9225.64 rows=500564 width=4)
               ->  Seq Scan on tbase_2  (cost=0.00..9225.64 rows=500564 width=4)
(7 rows)
第一个查询需要数据重分布，而第二个不需要，分布键 join 查询性能会更高。
查看 join 发生的节点
postgres=#  explain  select tbase_1.* from tbase_1,tbase_2 where tbase_1.f1=tbase_2.f1 ;    
                                          QUERY PLAN                                           
\\-----------------------------------------------------------------------------------------------
 Hash Join  (cost=29.80..186.32 rows=3872 width=40)
   Hash Cond: (tbase_1.f1 = tbase_2.f1)
   ->  Remote Subquery Scan on all (dn001,dn002)  (cost=100.00..158.40 rows=880 width=40)
         ->  Seq Scan on tbase_1  (cost=0.00..18.80 rows=880 width=40)
   ->  Hash  (cost=126.72..126.72 rows=880 width=4)
         ->  Remote Subquery Scan on all (dn001,dn002)  (cost=100.00..126.72 rows=880 width=4)
               ->  Seq Scan on tbase_2  (cost=0.00..18.80 rows=880 width=4)
(7 rows)
﻿
postgres=# set prefer_olap to on;
SET
postgres=# explain  select tbase_1.* from tbase_1,tbase_2 where tbase_1.f1=tbase_2.f1 ;  
                                           QUERY PLAN                                           
\\------------------------------------------------------------------------------------------------
 Remote Subquery Scan on all (dn001,dn002)  (cost=29.80..186.32 rows=3872 width=40)
   ->  Hash Join  (cost=29.80..186.32 rows=3872 width=40)
         Hash Cond: (tbase_1.f1 = tbase_2.f1)
         ->  Remote Subquery Scan on all (dn001,dn002)  (cost=100.00..158.40 rows=880 width=40)
               Distribute results by S: f1
               ->  Seq Scan on tbase_1  (cost=0.00..18.80 rows=880 width=40)
         ->  Hash  (cost=18.80..18.80 rows=880 width=4)
               ->  Seq Scan on tbase_2  (cost=0.00..18.80 rows=880 width=4)
(8 rows)
第一个 join 在 cn 节点执行，第二个在 dn 上重分布后再 join，业务设计上，一般 OLTP 类业务在 cn 上进行少数据量 join ，性能会更好。
查看并行的 worker 数
postgres=# explain select count(1) from tbase_1;
                                      QUERY PLAN                                       
\\---------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=118.81..118.83 rows=1 width=8)
   ->  Remote Subquery Scan on all (dn001,dn002)  (cost=118.80..118.81 rows=1 width=0)
         ->  Partial Aggregate  (cost=18.80..18.81 rows=1 width=8)
               ->  Seq Scan on tbase_1  (cost=0.00..18.80 rows=880 width=0)
(4 rows)
﻿
postgres=# analyze tbase_1;
ANALYZE
postgres=# explain select count(1) from tbase_1;
                                             QUERY PLAN                                             
\\----------------------------------------------------------------------------------------------------
 Parallel Finalize Aggregate  (cost=14728.45..14728.46 rows=1 width=8)
   ->  Parallel Remote Subquery Scan on all (dn001,dn002)  (cost=14728.33..14728.45 rows=1 width=0)
         ->  Gather  (cost=14628.33..14628.44 rows=1 width=8)
               Workers Planned: 2
               ->  Partial Aggregate  (cost=13628.33..13628.34 rows=1 width=8)
                     ->  Parallel Seq Scan on tbase_1  (cost=0.00..12586.67 rows=416667 width=0)
(6 rows)
上面第一个查询没走并行，第二个查询 analyze 后走并行才是正确的，建议大数据量更新再执行 analyze。
执行计划
查看执行计划
EXPLAIN 语法如下：
EXPLAIN [ ( 参数 [, ...] ) ]  SQL_clause 
or
EXPLAIN [ ANALYZE ] [ VERBOSE ]  SQL_clause 
参数说明：
ANALYZE: 执行命令并且显示实际的运行时间和其他统计信息。这个参数默认被设置为 FALSE。
VERBOSE: 显示关于执行计划的附加信息。
具体包括计划树中每个结点的输出列列表、模式限定的表和函数名称、在表达式中使用范围表别名标记变量，并输出显示统计信息的每个触发器的名称。这个参数默认被设置为 FALSE。
COSTS: 每一个计划结点的估计启动和总代价，以及估计的行数和每行的宽度。这个参数默认被设置为 TRUE。
SETTINGS: 有关配置参数的信息。具体包括影响查询计划的选项，其值与内置默认值不同。此参数默认为 FALSE。
BUFFERS: 缓冲区使用的信息。具体包括共享块命中、读取、标记为脏和写入的次数、本地块命中、读取、标记为脏和写入的次数、以及临时块读取和写入的次数。一次命中表示避免了一次读取，因为需要的块已经在缓存中找到了。共享块包含着来自于常规表和索引的数据，本地块包含着来自于临时表和索引的数据，而临时块包含着在排序、哈希等情况中使用的短期工作数据。脏块的数量表示被这个查询改变的之前未被修改块的数量，而写入块的数量表示这个后台在查询处理期间从缓存中替换出去的脏块的数量。为一个较高层结点显示的块数包括它的所有子结点所用到的块数。在文本格式中，只会打印非零值。只有当 ANALYZE 也被启用时，这个参数才能使用。它的默认被设置为 ALSE。
WAL: 有关 WAL 记录生成的信息。 具体包括记录数、整页图像数（fpi）和生成的 WAL 字节数量。 在文本格式中，仅打印非零值。 此参数只能在同时启用ANALYZE 时使用。 它默认为 FALSE。
TIMING: 在输出中包括实际启动时间以及在每个结点中花掉的时间。反复读取系统时钟的负荷在某些系统上会显著地拖慢查询，因此在只需要实际的行计数而不是实际时间时，把这个参数设置为 FALSE 可能会有用。即便用这个选项关闭结点层的计时，整个语句的运行时间也总是会被度量。只有当 ANALYZE 也被启用时，这个参数才能使用。它的默认被设置为 TRUE。
SUMMARY: 在查询计划之后包含摘要信息（例如，总计的时间信息）。当使用 ANALYZE 时默认包含摘要信息。不使用 ANALYZE 时可以使用此选项仅启用摘要信息。 使用 EXPLAIN EXECUTE 中的计划时间包括从缓存中获取计划所需的时间 以及重新计划所需的时间（如有必要）。
FORMAT: 指定输出格式，可以是 TEXT、XML、JSON 或者 YAML。非文本输出包含和文本输出格式相同的信息。这个参数默认被设置为 TEXT。
解读执行计划
使用 EXPLAIN 命令可以查看 TDSQL PG 优化器为每个查询生成的具体执行计划。EXPLAIN 的输出是一个节点树。 其中每一行对应一个数据库执行算子， 显示算子的节点类型和优化器为执行这个节点预估的开销。	
通过以下示例来解读执行计划：
create table t3(f1 int, f2 int) distribute by hash(f1);
create table t4(f1 int, f2 int) distrinute by hash(f1);
说明：
distribute by hash 表示对 t3，t4数据按 f1哈希值进行分布到不同节点 dn 上。
查看 SQL 的执行计划explain select * from t3, t4 where t3.f1=t4.f2; 可以得到如下输出结果
                                QUERY PLAN
--------------------------------------------------------------------------------
Remote Subquery Scan on all (datanode_1,datanode_2) (cost=120.19..222.56 rows=4556  width=16)
  -> Hash Join (cost=120.19..222.56 rows=4556 width=16) 
      Hash Cond:(t4.f2 = t3.f1)
      -> Remote Subquery Scan on all (datanode_1,datanode_2) (cost=100.00..120.53  rows=675 width=8) 
          Distribute results by H: f2 
          -> Seq Scan on t4 (cost=0.00..11.75 rows=675 width=8)
      -> Hash (cost=11.75..11.75 rows=675 width=8) 
          -> Seq Scan on t3 (cost=0.00..11.75 rows=675 width=8)
(8rows)
﻿
执行计划树里的每个节点代指一个执行算子。每个节点的 EXPLAIN 输出（如下所示）格式为算子名称（算子开销预估）。
  -> Hash Join(cost=120.19..222.56 rows=4556 width=16) 
      Hash Cond:(t4.f2 = t3.f1)
括号中的数字从左到右依次是:
启动代价: 这是该算子在读取第一条元组前的开销预估。比方说索引扫描算子的启动代价就是读取目标表的索引页，读取到第一个元组的开销。以上示例中的哈希关联(Hash Join)的启动代价是120.19。
算子总代价：这是该算子从执行到结束的总代价。以上示例中的哈希关联(Hash Join)的总代价是222.56。
算子输出的总行数。 以上示例中的哈希关联(Hash Join)的预估输出行数是4556。
算子输出行的行宽（字节数）。 以上示例中的哈希关联(Hash Join)的预估行宽是16字节。
有些算子在 EXPLAIN 的输出里除了上述的基本通用信息外， 还有算子的一些特定信息。 比方说上面的哈希关联算子的 EXAPLIN 输出还打印了关联条件。
在 SQL 执行计划输出结果中，通常最底层节点是表扫描节点，扫描节点返回表中的原数据行。 不同的表有不同的扫描节点类型，如顺序扫描，索引扫描和位图索引扫描。最底层的扫描节点也可能是也有非表列源，如 VALUES 子句并设置 FROM 返回，它们有自己的扫描类型。 
如果查询需要关联，聚合，排序或其他操作，会在扫描节点之上增加节点执行这些操作。这些操作通常都有多种方法，因此在这些位置也有可能出现不同的执行节点类型。
分布式计划中通常会有个特殊的算子（“Remote Subquery Scan”）。这个算子实现了分布式架构的核心数据 shuffle 的功能。 这个算子有几种不同的形态， 分别对应分布式架构下不同的数据 shuffle 功能:
（1）数据收集形态 - 作用是 CN 从 DN 收集数据。
（2）数据重分布形态 - 作用是 CN 根据选定的列按照特定的规则把数据重分布到所有的 DN。 
数据重分布的规则有: 
Redistribute by Hash: 把指定的列按照 hash redistribute。 这种情况下， 执行计划的输出里会在“Remote Subquery Scan”的那一行的下面一行打印 - “Distribute results by H：列名”。 如下所示。 其中"Remote Subquery Scan on all"后面括号里显示的是该算子在哪些数据节点上执行。
-> Remote Subquery Scan on all (datanode_1,datanode_2) (cost=100.00..120.53  rows=675 width=8) 
     Distribute results by H: f2                          
     -> Seq Scan on t4 (cost=0.00..11.75 rows=675 width=8)
Redistribute by Shard: 把指定的列按照 shard redistribute。 这种情况下， 执行计划的输出里会在“Remote Subquery Scan”的那一行的下面一行打印 - “Distribute results by S：列名”
Broadcast - 把指定的表广播到所有的数据节点上。 这种情况下， 执行计划输出里在“Remote Subquery Scan”的那一行下面没有额外的重分布信息。
在分布式架构中， 当 SQL 可以完全下推到各个 DN 上计算，中间计算结果不需要重新分布到其他 DN 上的时候， 执行计划中会有个分布式快速执行算子 - “Remote Fast Query Execution”， 如下示例。在下面这个例子里，两表关联的列都是分布健。这种情况下，各个 DN 上各自独立执行 SQL，CN 只需要把 DN 返回的结果直接返回即可，无需额外的计算。
explain select * from t3, t4 where t3.f1=t4.f1;
 QUERY PLAN
------------------------------------------------------------------
Remote Fast Query Execution (cost=0.00..0.00 rows=0 width=0) 
 Node/s:datanode_1, datanode_2
 ->Merge Join (cost=187.38..330.81 rows=9112 width=16)
   Merge Cond:(t3.f1=t4.f1)
   -> Sort (cost=93.69..97.07 rows=1350 width=8)
      Sort Key: t3.f1
      -> Seq Scan on t3 (cost=0.00..23.50 rows=1350 width=8) 
   -> Sort (cost=93.69..97.07 rows=1350 width=8)
      Sort Key: t4.f1
      -> Seq Scan on t4 (cost=0.00..23.50 rows=1350 width=8)
(10rows)
EXPLAIN 输出的就是一个用户可视化的查询计划树，可以告诉我们执行了哪些节点（操作），并且每个节点（操作）的代价预估是什么样的，指的是一个 SQL执行的代价是多少，而不是具体的时间。
评估的行数不是执行和扫描查询节点的数量，是节点返回的数量。一般会少于扫描数量，因为有 WHERE 条件会过滤掉一些数据。理想情况顶级行数评估近似于实际返回的数量。
代价预估计算是依赖于规划器的设置参数。需要知道的是：上级节点的消耗代价包括其子节点的消耗代价。下面是默认情况下，对数据操作的消耗评估基础：
seq_page_cost=1.0                    #measured on an arbitrary scale
random_page_cost=4.0                #same scale as above，如果是SSD这个代价因子可以酌情调整
cpu_tuple_cost=0.01                  #same scale as above
cpu_index_tuple_cost=0.005            #same scale as above
举例说明如下：
tdsql=# create table test(id int, info text, crt_time timestamp);
CREATE TABLE
tdsql=# insert into test select generate_series(1, 10000), md5(cast(random() as text)), now();
INSERT 0 10000
tdsql=# analyze test;
ANALYZE
tdsql=# explain select * from test;
                          QUERY PLAN
---------------------------------------------------------------
 Remote Fast Query Execution  (cost=0.00..0.00 rows=0 width=0)
   Node/s: dn001, dn002
   ->  Seq Scan on test  (cost=0.00..98.39 rows=5039 width=19)
(3 rows)
﻿
---test表做全表扫描的消耗评估情况
--test表的数据分布情况：
tdsqlselect xc_node_id,count(*) from test group by 1;
 xc_node_id | count
------------+-------
  -17499968 |  4961
 2142761564 |  5039
(2 rows)
﻿
--test表的总数据块级行数统计：
tdsql=# select relpages, reltuples from pg_class where relname = 'test';
 relpages | reltuples
----------+-----------
       95 |     10000
(1 row)
﻿
--test表在DN001和DN002上的数据块和行数统计：
tdsql=# execute direct on (dn001)  'select relpages, reltuples from pg_class where relname = ''test''';
relpages | reltuples
----------+-----------
       48 |      5039
(1 row)
﻿
tdsql=# execute direct on (dn002)  'select relpages, reltuples from pg_class where relname = ''test''';
 relpages | reltuples
----------+-----------
       47 |      4961
(1 row)
tdsql=# explain select * from test;
                          QUERY PLAN
---------------------------------------------------------------
 Remote Fast Query Execution  (cost=0.00..0.00 rows=0 width=0)
   Node/s: dn001, dn002
   ->  Seq Scan on test  (cost=0.00..98.39 rows=5039 width=19)
(3 rows)
﻿
表 test 有10000条数据分布在95个块，其中 DN001节点上有48块，评估消耗是（磁盘页*seq_page_cost）+（扫描行*cpu_tuple_cost）。默认seq_page_cost是1.0，cpu_tuple_cost 是0.01，所以评估值是：(48 * 1.0) + (5039 * 0.01) = 98.39，和执行计划吻合。
执行计划一般都是以下几种的组合：
（1）表扫描：分为全表扫描、索引扫描、索引扫描还分为 bitmap 扫描。
（2）表连接：nested loop、hash join、merge join。
（3）聚合运算：count、min、max 等聚合操作。
（4）排序、分组：order by、group by。
因此我们需要关注这几类节点的执行消耗统计情况，一般分析原则如下：
从里到外：
外层的消耗是在里层消耗的基础上进行评估的，越在里层的执行计划树节点，越早执行，外层的节点都依赖里层的结果。因此我们需要从里层入手去消除cost 较高的节点。里层节点很容易被循环调用，里层的节点一般都是被驱动对象，如果表连接方式不正确，很容易产生循环调用，例如应该走 hash loop 的走了 nested loop。
从大到小：从 cost 较大的节点入手。
消除重分布：当两个表进行连接时，连接键不都是分片键时，会走重分布。在当前的 V5版本中，DN 重分布会需要将本节点数据发送到其他所有 DN 节点，代价非常高。重分布关键词：Distribute results by。
避免分布式事务：如果 SQL 不带分片键，那么就会被发送到所有 DN 去执行。select 语句则就涉及 CN 收取多个 DN 的结果，insert、update、delete 则会涉及分布式事务，代价比带分片键高很多。
优化 SQL 语句

本页目录：

查看是否为分布键查询

查看是否使用索引

查看是否为分布 key join

查看 join 发生的节点

查看并行的 worker 数

执行计划

查看执行计划

解读执行计划
