ToB企服应用市场:ToB评测及商务社交产业平台

标题: FQS：一种神奇的数仓查询优化技术 [打印本页]
作者: 商道如狼道    时间: 2024-2-17 20:27
标题: FQS：一种神奇的数仓查询优化技术
本文分享自华为云社区《根据执行计划优化SQL【绽放吧！GaussDB(DWS)云原生数仓】》，作者：西岭雪山。
引言

如果您刚接触DWS那一定会好奇想要知道"REMOTE_FQS_QUERY" 到底代表什么意思？我们看官网的描述是代表这执行计划已经CN直接将原语句下发到DN，各DN单独执行，并将执行结果在CN上进行汇总。且不需要做过多的调整了，真的是这样吗？
FQS计划，完全下推

两表JOIN，且其连接条件为各表的分布列，在关闭stream算子的情况下，CN会直接将该语句发送至各DN执行，最后结果在CN汇总。
  1. SET enable_stream_operator=off;
  3. SET explain_perf_mode=normal;
  5. EXPLAIN (VERBOSE on,COSTS off) SELECT * FROM tt01,tt02 WHERE tt01.c1=tt02.c2;
  7. QUERY PLAN
  9. -------------------------------------------------------------------------------------------------------------------
  11. Data Node Scan on "__REMOTE_FQS_QUERY__"
  13. Output: tt01.c1, tt01.c2, tt02.c1, tt02.c2
  15. Node/s: All datanodes
  17. Remote query: SELECT tt01.c1, tt01.c2, tt02.c1, tt02.c2 FROM dbadmin.tt01, dbadmin.tt02 WHERE tt01.c1 = tt02.c2
  19. (4 rows)
复制代码
像上面的执行计划只显示了Data Node Scan on "__REMOTE_FQS_QUERY__"，这样的执行计划太过粗糙，不知道内部是如何执行的，是否走了索引等更为详细的信息。
下面我们建表进行验证
  1. create table t5 (bh varchar(300),bh2 varchar(300),c_name varchar(300),c_info varchar(300))distribute by hash(bh);
  3. insert into t4 select uuid_generate_v1(), uuid_generate_v1(),'测试','sdfffffffffffffffsdf' from generate_series(1,50000);
  5. insert into t4 select * from t4;
  7. --1、没有索引的情况下：
  9. postgres=# explain analyze select * from t4 where bh2 = '652e4e0e-ba60-0400-25b5-4ee5e490fffe';
  11. QUERY PLAN
  13. -----------------------------------------------------------------------------------------------------------------------------
  15. id | operation | A-time | A-rows | E-rows | Peak Memory | A-width | E-width | E-costs
  17. ----+----------------------------------------------+---------+--------+--------+-------------+---------+---------+---------
  19. 1 | -> Data Node Scan on "__REMOTE_FQS_QUERY__" | 256.364 | 32 | 0 | 56KB | | 0 | 0.00
  23. ====== Query Summary =====
  25. -----------------------------------------
  27. Coordinator executor start time: 0.055 ms
  29. Coordinator executor run time: 256.410 ms
  31. Coordinator executor end time: 0.010 ms
  33. Planner runtime: 0.145 ms
  35. Query Id: 73746443917091633
  37. Total runtime: 256.557 ms
  39. (12 rows)
  41. Time: 259.051 ms
  43. --2、添加索引，并添加hint indexscan
  45. postgres=# create index i_t4 on t4(bh2);
  47. CREATE INDEX
  49. Time: 3328.258 ms
  51. postgres=# explain analyze select /*+ indexscan(t4 i_t4) */ * from t4 where bh2 = '652e4e0e-ba60-0400-25b5-4ee5e490fffe';
  53. QUERY PLAN
  55. ----------------------------------------------------------------------------------------------------------------------------
  57. id | operation | A-time | A-rows | E-rows | Peak Memory | A-width | E-width | E-costs
  59. ----+----------------------------------------------+--------+--------+--------+-------------+---------+---------+---------
  61. 1 | -> Data Node Scan on "__REMOTE_FQS_QUERY__" | 2.269 | 32 | 0 | 56KB | | 0 | 0.00
  65. ====== Query Summary =====
  67. -----------------------------------------
  69. Coordinator executor start time: 0.027 ms
  71. Coordinator executor run time: 2.298 ms
  73. Coordinator executor end time: 0.009 ms
  75. Planner runtime: 0.074 ms
  77. Query Id: 73746443917091930
  79. Total runtime: 2.401 ms
  81. (12 rows)
复制代码
可以看到没有创建索引的时候执行计划和创建索引的执行计划完全一样，但是执行的时间是259.051ms和2.401ms，相差非常明显，很可能第二个执行计划已经走索引了，但是执行计划一样，这对于优化人员不够直观。
即使在执行计划中加入了 /*+ indexscan(t4 i_t4) */，但并没有打印出是否走了索引，执行计划过于简洁，并且pg_stat_all_indexes中业务表的所有统计信息都是0，也没发判断。
CPUTime

对于上面的时间区别也可以用CPU耗时对比，在执行计划中加入CPU的耗时：
  1. --没有索引的执行计划
  3. postgres=# explain (analyze,buffers,verbose,cpu,nodes )select * from t4 where bh2 = '652e4e0e-ba60-0400-25b5-4ee5e490fffe';
  5. QUERY PLAN
  7. ---------------------------------------------------------------------------------------------------------------------------
  9. Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0) (actual time=244.096..244.108 rows=32 loops=1)
  11. Output: t4.bh, t4.bh2, t4.c_name, t4.c_info
  13. Node/s: All datanodes
  15. Remote query: SELECT bh, bh2, c_name, c_info FROM sa.t4 WHERE bh2::text = '652e4e0e-ba60-0400-25b5-4ee5e490fffe'::text
  17. (CPU: ex c/r=762829, ex row=32, ex cyc=24410534, inc cyc=24410534)
  19. Total runtime: 244.306 ms
  21. (6 rows)
  23. --创建索引后的执行计划
  25. postgres=# explain (analyze,buffers,verbose,cpu,nodes )select * from t4 where bh2 = '652e4e0e-ba60-0400-25b5-4ee5e490fffe';
  27. QUERY PLAN
  29. --------------------------------------------------------------------------------------------------------------------------
  31. Data Node Scan on "__REMOTE_FQS_QUERY__" (cost=0.00..0.00 rows=0 width=0) (actual time=1.035..2.148 rows=32 loops=1)
  33. Output: t4.bh, t4.bh2, t4.c_name, t4.c_info
  35. Node/s: All datanodes
  37. Remote query: SELECT bh, bh2, c_name, c_info FROM sa.t4 WHERE bh2::text = '652e4e0e-ba60-0400-25b5-4ee5e490fffe'::text
  39. (CPU: ex c/r=6698, ex row=32, ex cyc=214354, inc cyc=214354)
  41. Total runtime: 2.242 ms
  43. (6 rows)
复制代码
对比执行计划可以看到是一样的。
其中cyc代表的是CPU的周期数，ex cyc表示的是当前算子的周期数，不包含其子节点；inc cyc是包含子节点的周期数；ex row是当前算子输出的数据行数；ex c/r则是ex cyc/ex row得到的每条数据所用的平均周期数。
cpu平均周期对比：没索引：762829，创建索引后：6698，大约是一百多倍。
查看详细计划

__REMOTE_FQS_QUERY__是直接将语句发送给了nodedata，所以cn节点不生成执行计划，所以没法看到是否走索引，如果我们将enable_fast_query_shipping关闭，就能在cn上面生成执行计划，可以看到是否走了索引。
  1. --关闭fast_query
  3. postgres=# set enable_fast_query_shipping to off;
  5. postgres=# set explain_perf_mode=normal;
  7. --走索引的执行计划
  9. postgres=# explain analyze select * from t4 where bh2 = '652e4e0e-ba60-0400-25b5-4ee5e490fffe';
  11. QUERY PLAN
  13. ------------------------------------------------------------------------------------------------------------------------------
  15. Streaming (type: GATHER) (cost=4.95..51.75 rows=31 width=102) (actual time=1.695..2.263 rows=32 loops=1)
  17. Node/s: All datanodes
  19. -> Bitmap Heap Scan on t4 (cost=4.33..43.75 rows=31 width=102) (actual time=[0.040,0.040]..[0.057,0.153], rows=32)
  21. Recheck Cond: ((bh2)::text = '652e4e0e-ba60-0400-25b5-4ee5e490fffe'::text)
  23. -> Bitmap Index Scan on i_t4 (cost=0.00..4.33 rows=31 width=0) (actual time=[0.035,0.035]..[0.042,0.042], rows=32)
  25. Index Cond: ((bh2)::text = '652e4e0e-ba60-0400-25b5-4ee5e490fffe'::text)
  27. Total runtime: 2.569 ms
  29. (7 rows)
  31. Time: 5.226 ms
  33. --删除索引后的全表扫描
  35. postgres=# explain analyze select * from t4 where bh2 = '652e4e0e-ba60-0400-25b5-4ee5e490fffe';
  37. QUERY PLAN
  39. -------------------------------------------------------------------------------------------------------------------------
  41. Streaming (type: GATHER) (cost=0.62..31755.34 rows=31 width=102) (actual time=294.661..294.814 rows=32 loops=1)
  43. Node/s: All datanodes
  45. -> Seq Scan on t4 (cost=0.00..31747.34 rows=31 width=102) (actual time=[0.084,258.294]..[280.141,293.190], rows=32)
  47. Filter: ((bh2)::text = '652e4e0e-ba60-0400-25b5-4ee5e490fffe'::text)
  49. Rows Removed by Filter: 3199968
  51. Total runtime: 295.154 ms
  53. (6 rows)
  55. Time: 297.348 ms
复制代码
使用enable_fast_query_shipping控制是否使用分布式框架，以此来查看具体的执行计划，针对优化SQL有帮助。
仅凭 "REMOTE_FQS_QUERY"是没法判断有没有走索引，还需要进一步验证。
小小的缺陷：即使SQL走了索引，统计信息表pg_stat_all_indexes和pg_stat_all_table中的index_scan索引扫描次数都是0。
分布键类型影响

常见的fqs一般单表简单查询，以及多表连接且关联键是同类型分布键。
当查询中有函数，多表关联关联键字段类型不同，分布键类型不同，以及非等值情况都可能造成不下推。
下面举例分布键类型不一样
  1. --t1和t2表结构完全一样，分布键都是hash(id)
  3. postgres=# \d+ t1
  5. Table "sa.t1"
  7. Column | Type | Modifiers | Storage | Stats target | Description
  9. --------+------------------------+-----------+----------+--------------+-------------
  11. id | character varying(300) | | extended | |
  13. c_name | character varying(300) | | extended | |
  15. c_info | character varying(300) | | extended | |
  17. Indexes:
  19. "i_t1" btree (id) TABLESPACE pg_default
  21. "i_t1_id" btree (id) TABLESPACE pg_default
  23. Has OIDs: no
  25. Distribute By: HASH(id)
  27. Location Nodes: ALL DATANODES
  29. Options: orientation=row, compression=no
  31. --可以下推，执行计划显示FQS
  33. postgres=# explain select * from t1,t2 where t1.id=t2.id;
  35. QUERY PLAN
  37. ----------------------------------------------------------------------------------
  39. id | operation | E-rows | E-width | E-costs
  41. ----+----------------------------------------------+--------+---------+---------
  43. 1 | -> Data Node Scan on "__REMOTE_FQS_QUERY__" | 0 | 0 | 0.00
  45. (3 rows)
  47. --修改其中一个表的分布键为随机分布roundrobin
  49. postgres=# alter table t1 distribute by roundrobin;
  51. ALTER TABLE
  53. postgres=# explain select * from t1,t2 where t1.id=t2.id;
  55. QUERY PLAN
  57. ------------------------------------------------------------------------------------------------
  59. id | operation | E-rows | E-memory | E-width | E-costs
  61. ----+-----------------------------------------+----------+--------------+---------+-----------
  63. 1 | -> Streaming (type: GATHER) | 13021186 | | 60 | 159866.51
  65. 2 | -> Hash Join (3,5) | 13021186 | 1MB | 60 | 159449.88
  67. 3 | -> Streaming(type: REDISTRIBUTE) | 1600000 | 2MB | 30 | 53357.30
  69. 4 | -> Seq Scan on t1 | 1600000 | 1MB | 30 | 9357.33
  71. 5 | -> Hash | 1599999 | 48MB(4435MB) | 30 | 9355.33
  73. 6 | -> Seq Scan on t2 | 1600000 | 1MB | 30 | 9355.33
  77. RunTime Analyze Information
  79. ----------------------------------
  81. "sa.t1" runtime: 219.368ms
  83. "sa.t2" runtime: 184.141ms
  87. Predicate Information (identified by plan id)
  89. --------------------------------------------------
  91. 2 --Hash Join (3,5)
  93. Hash Cond: ((t1.id)::text = (t2.id)::text)
  97. ====== Query Summary =====
  99. -------------------------------
  101. System available mem: 4546560KB
  103. Query Max mem: 4546560KB
  105. Query estimated mem: 131072KB
  107. (24 rows)
  109. --将t2表修改为随机分布，结果是查询时两个表都需要重分布
  111. postgres=# alter table t2 distribute by roundrobin;
  113. ALTER TABLE
  115. postgres=# explain select * from t1,t2 where t1.id=t2.id;
  117. QUERY PLAN
  119. ---------------------------------------------------------------------------------------------------
  121. id | operation | E-rows | E-memory | E-width | E-costs
  123. ----+--------------------------------------------+----------+--------------+---------+-----------
  125. 1 | -> Streaming (type: GATHER) | 12804286 | | 60 | 203041.85
  127. 2 | -> Hash Join (3,5) | 12804286 | 1MB | 60 | 202625.22
  129. 3 | -> Streaming(type: REDISTRIBUTE) | 1600000 | 2MB | 30 | 53357.30
  131. 4 | -> Seq Scan on t2 | 1600000 | 1MB | 30 | 9357.33
  133. 5 | -> Hash | 1599999 | 68MB(4433MB) | 30 | 53357.30
  135. 6 | -> Streaming(type: REDISTRIBUTE) | 1600000 | 2MB | 30 | 53357.30
  137. 7 | -> Seq Scan on t1 | 1600000 | 1MB | 30 | 9357.33
  141. RunTime Analyze Information
  143. ----------------------------------
  145. "sa.t2" runtime: 203.933ms
  149. Predicate Information (identified by plan id)
  151. --------------------------------------------------
  153. 2 --Hash Join (3,5)
  155. Hash Cond: ((t2.id)::text = (t1.id)::text)
  159. ====== Query Summary =====
  161. -------------------------------
  163. System available mem: 4546560KB
  165. Query Max mem: 4546560KB
  167. Query estimated mem: 131072KB
  169. (24 rows)
复制代码
当t1表是随机分布的时候连表查询，t1表会要做重分布，t2也是随机分布的时候，连表查询也需要做重分布。随机分布的情况下是没法完全下推的。
replication模式就不演示了，因为replication是所有dn都有一份数据，所以数据量是dn数量*表数据量，每个节点都有一份完整的数据，肯定是可以下推的。
将t1和t2都改成hash分布，然后关联建选择一个非分布列，这很明显的是没法直接完全下推的：
  1. postgres=# alter table t1 distribute by hash(id);
  3. ALTER TABLE
  5. postgres=# alter table t2 distribute by hash(id);
  7. ALTER TABLE
  9. --关联建加入c_name
  11. postgres=# explain select * from t1,t2 where t1.id=t2.c_name;
  13. QUERY PLAN
  15. ---------------------------------------------------------------------------------------------------------------------
  17. id | operation | E-rows | E-memory | E-width | E-costs
  19. ----+--------------------------------------------------------------+----------+--------------+---------+-----------
  21. 1 | -> Streaming (type: GATHER) | 12621020 | | 61 | 182863.95
  23. 2 | -> Hash Join (3,5) | 12621020 | 1MB | 61 | 182447.32
  25. 3 | -> Streaming(type: PART REDISTRIBUTE PART ROUNDROBIN) | 1600000 | 2MB | 30 | 54688.64
  27. 4 | -> Seq Scan on t2 | 1600000 | 1MB | 30 | 9355.33
  29. 5 | -> Hash | 1599999 | 48MB(4433MB) | 31 | 32355.32
  31. 6 | -> Streaming(type: PART LOCAL PART BROADCAST) | 1600000 | 2MB | 31 | 32355.32
  33. 7 | -> Seq Scan on t1 | 1600000 | 1MB | 31 | 9355.33
  35. -- 如果将t1改成replication
  37. postgres=# alter table t1 distribute by replication ;
  39. ALTER TABLE
  41. postgres=# explain select * from t1,t2 where t1.id=t2.id;
  43. QUERY PLAN
  45. ----------------------------------------------------------------------------------
  47. id | operation | E-rows | E-width | E-costs
  49. ----+----------------------------------------------+--------+---------+---------
  51. 1 | -> Data Node Scan on "__REMOTE_FQS_QUERY__" | 0 | 0 | 0.00
  53. (3 rows)
  55. --可以看到t1是复制表，t2是hash表也可以完全下推
  57. --再将t2改为随机分布，关联查询会是怎样呢？
  59. postgres=# alter table t2 distribute by replication;
  61. ALTER TABLE
  63. postgres=# explain select * from t1,t2 where t1.id=t2.id;
  65. QUERY PLAN
  67. ----------------------------------------------------------------------------------
  69. id | operation | E-rows | E-width | E-costs
  71. ----+----------------------------------------------+--------+---------+---------
  73. 1 | -> Data Node Scan on "__REMOTE_FQS_QUERY__" | 0 | 0 | 0.00
  75. (3 rows)
复制代码
当关联建中有非分布键的时候是没法完全下推的，如果将其中一个表改成复制表（每个dn都有数据），无论另外一张表是如何分布都是可以完全下推。但是复制表只适合小表
常见非FQS

总的来说，FQS是一种性能优化工具，适用于许多查询，但并非所有查询都适合。数据库查询优化通常涉及权衡，需要根据具体查询和性能需求来选择合适的执行方式。可以通过观察执行计划和性能测试来确定是否应使用FQS。
总结

1、在DWS中，FQS（Fast Query Shipping）是一种查询优化技术，允许将查询转发到数据节点以在数据节点上执行，从而减少数据传输和提高查询性能。
2、DWS中当前主要存在三类计划：
3、仅凭 "REMOTE_FQS_QUERY"是没法判断有没有走索引，还需要进一步验证，使用enable_fast_query_shipping控制是否使用分布式框架，以此来查看具体的执行计划，针对优化SQL有帮助。
4、当使用随机分布的时候由于数据是随机分布的所以在进行关联查询的时候该表基本都需要进行重分布，代价较高。
5、replication模式由于各个节点都有一份数据，所以都可以完全下推，使用replication模式适合查询频繁的小表。
6、分布键和非分布键关联也不能完全下推，这是比较常见的情况，所以在进行表设计的时候分布键字段类型一致，join的列最好。
7、小小的缺陷：即使SQL走了索引，统计信息表pg_stat_all_indexes和pg_stat_all_table中的index_scan索引扫描次数都是0。
8、应该尽量保证执行计划是fqs，在fqs的基础上如果还能继续优化就可以使用enable_fast_query_shipping关闭完全下推，查看执行计划针对性的优化。
 
点击关注，第一时间了解华为云新鲜技术~
 
 

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4