query_dop不为1时limit子句严重性能问题

【标题描述】: query_dop不为1时limit子句严重性能问题
【测试类型：SQL功能/存储功能/接口功能/工具功能/性能/并发/压力长稳/故障注入/安全/资料/编码规范】
【测试版本：3.0.0】

问题描述
问题：设置query_dop=4的情况下，limit子句需要等所有结果查询汇总完才能过滤，严重影响性能。
从查询计划看limit需要等并发扫描汇总后才发生

dbpaasstoredb=# set query_dop=1;
SET
dbpaasstoredb=# explain select * from db2_topsql_metrics limit 1;
                                                QUERY PLAN                                                 
-----------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.97 rows=1 width=4849)
   ->  Partition Iterator  (cost=0.00..51433577.01 rows=53291301 width=4849)
         Iterations: 3
         ->  Partitioned Seq Scan on db2_topsql_metrics  (cost=0.00..51433577.01 rows=53291301 width=4849)
               Selected Partitions:  1..3
(5 rows)

dbpaasstoredb=# set query_dop=4;
SET
dbpaasstoredb=# explain select * from db2_topsql_metrics limit 1;
 id |                        operation                        |  E-rows  | E-width |   E-costs   
----+---------------------------------------------------------+----------+---------+-------------
  1 | ->  Limit                                               |        1 |    4849 | 1.20
  2 |    ->  Streaming(type: LOCAL GATHER dop: 1/4)           | 53291727 |    4849 | 64121119.18
  3 |       ->  Partition Iterator                            | 53291727 |    4849 | 12861497.07
  4 |          ->  Partitioned Seq Scan on db2_topsql_metrics | 53291727 |    4849 | 12861497.07
(4 rows)

  Predicate Information (identified by plan id)   
--------------------------------------------------
   3 --Partition Iterator
         Iterations: 3
   4 --Partitioned Seq Scan on db2_topsql_metrics
         Selected Partitions:  1..3
(4 rows)

【操作系统和硬件信息】(查询命令: cat /etc/system-release, uname -a):

【测试环境】(单机/1主x备x级联备):

【被测功能】:

【测试类型】:

【数据库版本】(查询命令: gaussdb –V):

【预置条件】:

【操作步骤】(请填写详细的操作步骤):

xxx
xxx

【预期输出】:

【实际输出】:

【原因分析】:

这个问题的根因
问题推断过程
还有哪些原因可能造成类似现象
该问题是否有临时规避措施
问题解决方案
预计修复问题时间

【日志信息】(请附上日志文件、截图、coredump信息):

【测试代码】:

Hey @anikikong, Welcome to openGauss Community.
All of the projects in openGauss Community are maintained by @opengauss-bot.
That means the developers can comment below every pull request or issue to trigger Bot Commands.
Please follow instructions at Here to find the details.

通过实测发现，limit场景下实际 Streaming 算子处只有 1 行结果，实际运行时间为 15ms，但是结果显示出来花了 774ms，有700多ms的时间暂时不支持花费在哪，需要继续定位
输入图片说明

请继续分析啊，这个问题很好重现

输入图片说明

3.0.1版本回归通过

openGauss=# \timing on
Timing is on.
openGauss=# \d+ test
                                   Table "public.test"
 Column |            Type             | Modifiers | Storage  | Stats target | Description
--------+-----------------------------+-----------+----------+--------------+-------------
 c1     | integer                     |           | plain    |              |
 c2     | integer                     |           | plain    |              |
 c3     | character varying(100)      |           | extended |              |
 c4     | character(100)              |           | extended |              |
 c5     | timestamp without time zone |           | plain    |              |
Indexes:
    "index_1_test" UNIQUE, btree (c1) TABLESPACE pg_default
    "index_2_test" UNIQUE, btree (c1, c2, c3) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

openGauss=# show query_dop;
 query_dop
-----------
 1
(1 row)

Time: 0.399 ms
openGauss=# explain analyze select * from test limit 1;
                                                   QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..0.03 rows=1 width=122) (actual time=0.015..0.015 rows=1 loops=1)
   ->  Seq Scan on test  (cost=0.00..143012.03 rows=4589603 width=122) (actual time=0.014..0.014 rows=1 loops=1)
 Total runtime: 0.120 ms
(3 rows)

Time: 0.785 ms
openGauss=# set query_dop to 4;
SET
Time: 0.284 ms
openGauss=# explain analyze select * from test limit 1;
 id |                   operation                   |    A-time     | A-rows | E-rows  |  Peak Memory  | A-width | E-width | E-cos
ts
----+-----------------------------------------------+---------------+--------+---------+---------------+---------+---------+------
----
  1 | ->  Limit                                     | [4.964,4.964] |      1 |       1 | [2KB,2KB]     |         |     122 | 0.03
  2 |    ->  Streaming(type: LOCAL GATHER dop: 1/4) | [4.961,4.961] |      1 |       1 | [105KB,105KB] |         |     122 | 0.03
  3 |       ->  Limit                               | [0.194,0.194] |      1 |       1 | [2KB,2KB]     |         |     122 | 0.01
  4 |          ->  Seq Scan on test                 | [0.193,0.193] |      1 | 4589603 | [21KB,21KB]   |         |     122 | 38753
.01
(4 rows)

                    User Define Profiling
--------------------------------------------------------------
 Segment Id: 1  Track name: Datanode build connection
  (actual time=[0.000, 0.000], calls=[1, 1])
 Plan Node id: 1  Track name: Datanode start up stream thread
  (actual time=[0.829, 0.829], calls=[1, 1])
(4 rows)

        ====== Query Summary =====
-------------------------------------------
 Coordinator executor start time: 1.326 ms
 Coordinator executor run time: 4.973 ms
 Coordinator executor end time: 0.104 ms
 Planner runtime: 0.171 ms
 Plan size: 0 byte
 Query Id: 281474976717866
 Total runtime: 6.422 ms
(7 rows)

Time: 16.260 ms

GVP openGauss / openGauss-server

内容风险标识

评论 (7)

GVPopenGauss / openGauss-server

内容风险标识