测试类型

SQL功能

测试版本

7.0.0-RC3

问题描述

资源池化CI：FORWARD_OLDEST_XMIN Case0003 sql执行时间过长，与以往差距过大

操作系统和硬件信息

NAME="openEuler"
VERSION="20.03 (LTS)"
ID="openEuler"
VERSION_ID="20.03"
PRETTY_NAME="openEuler 20.03 (LTS)"
ANSI_COLOR="0;31"

测试环境

资源池化

被测功能

预置条件

操作步骤

gsql -d tpcc -p 19327 -r -c "(select o_w_id, o_d_id, sum(o_ol_cnt) from bmsql_oorder
group by o_w_id, o_d_id) except (select ol_w_id, ol_d_id, count(ol_o_id) from bmsql_order_line
group by ol_w_id, ol_d_id);"

预期输出

45分钟内执行完毕

实际输出

无法在45min内执行完成用例

日志信息

以往截图

[pkq@openGauss151 ~]$ source /data/ss_env/ENVFILE; gsql -d tpcc -p 19327 -r  -c "explain (select o_w_id, o_d_id, sum(o_ol_cnt) from bmsql_oorder
>                                                      group by o_w_id, o_d_id) except (select ol_w_id, ol_d_id, count(ol_o_id) from bmsql_order_line
>                                                      group by ol_w_id, ol_d_id);"
                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 HashSetOp Except  (cost=40528.26..517306.10 rows=500 width=20)
   ->  Append  (cost=40528.26..517298.60 rows=1000 width=20)
         ->  Subquery Scan on "*SELECT* 1"  (cost=40528.26..40538.26 rows=500 width=20)
               ->  HashAggregate  (cost=40528.26..40533.26 rows=500 width=20)
                     Group By Key: bmsql_oorder.o_w_id, bmsql_oorder.o_d_id
                     ->  Seq Scan on bmsql_oorder  (cost=0.00..28656.29 rows=1582929 width=12)
         ->  Subquery Scan on "*SELECT* 2"  (cost=476750.34..476760.34 rows=500 width=20)
               ->  HashAggregate  (cost=476750.34..476755.34 rows=500 width=20)
                     Group By Key: bmsql_order_line.ol_w_id, bmsql_order_line.ol_d_id
                     ->  Seq Scan on bmsql_order_line  (cost=0.00..356946.48 rows=15973848 width=12)
(10 rows)

[pkq@openGauss151 ~]$ source /data/ss_env/ENVFILE; gsql -d tpcc -p 19327 -r  -c "(select o_w_id, o_d_id, sum(o_ol_cnt) from bmsql_oorder
                                                     group by o_w_id, o_d_id) except (select ol_w_id, ol_d_id, count(ol_o_id) from bmsql_order_line
                                                     group by ol_w_id, ol_d_id);"
 o_w_id | o_d_id | sum 
--------+--------+-----
(0 rows)

[pkq@openGauss151 ~]$ gsql -d tpcc -p 19327
gsql ((openGauss 7.0.0-RC3 build 632209be) compiled at 2026-07-07 00:02:39 commit 1000 last mr 9389 release on aarch64-unknown-linux-gnu, compiled by g++ (GCC) 10.3.1, 64-bit)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

tpcc=# \d bmsql_oorder
\d bmsql_order_line              Table "public.bmsql_oorder"
    Column    |            Type             | Modifiers 
--------------+-----------------------------+-----------
 o_w_id       | integer                     | not null
 o_d_id       | integer                     | not null
 o_id         | integer                     | not null
 o_c_id       | integer                     | 
 o_carrier_id | integer                     | 
 o_ol_cnt     | integer                     | 
 o_all_local  | integer                     | 
 o_entry_d    | timestamp without time zone | 
Indexes:
    "bmsql_oorder_pkey" PRIMARY KEY, btree (o_w_id, o_d_id, o_id) TABLESPACE pg_default
    "bmsql_oorder_idx1" UNIQUE, btree (o_w_id, o_d_id, o_carrier_id, o_id) TABLESPACE pg_default
Foreign-key constraints:
    "o_customer_fkey" FOREIGN KEY (o_w_id, o_d_id, o_c_id) REFERENCES bmsql_customer(c_w_id, c_d_id, c_id)
Referenced by:
    TABLE "bmsql_new_order" CONSTRAINT "no_order_fkey" FOREIGN KEY (no_w_id, no_d_id, no_o_id) REFERENCES bmsql_oorder(o_w_id, o_d_id, o_id)
    TABLE "bmsql_order_line" CONSTRAINT "ol_order_fkey" FOREIGN KEY (ol_w_id, ol_d_id, ol_o_id) REFERENCES bmsql_oorder(o_w_id, o_d_id, o_id)

tpcc=# 
             Table "public.bmsql_order_line"
     Column     |            Type             | Modifiers 
----------------+-----------------------------+-----------
 ol_w_id        | integer                     | not null
 ol_d_id        | integer                     | not null
 ol_o_id        | integer                     | not null
 ol_number      | integer                     | not null
 ol_i_id        | integer                     | not null
 ol_delivery_d  | timestamp without time zone | 
 ol_amount      | numeric(6,2)                | 
 ol_supply_w_id | integer                     | 
 ol_quantity    | integer                     | 
 ol_dist_info   | character(24)               | 
Indexes:
    "bmsql_order_line_pkey" PRIMARY KEY, btree (ol_w_id, ol_d_id, ol_o_id, ol_number) TABLESPACE pg_default
Foreign-key constraints:
    "ol_order_fkey" FOREIGN KEY (ol_w_id, ol_d_id, ol_o_id) REFERENCES bmsql_oorder(o_w_id, o_d_id, o_id)
    "ol_stock_fkey" FOREIGN KEY (ol_supply_w_id, ol_i_id) REFERENCES bmsql_stock(s_w_id, s_i_id)

提单组织

内部开发

测试代码