GaussDB(DWS)调优分享：delete inlist改写优化

GaussDB(DWS)对查询时inlist过长的场景进行了优化，可以将inlist的过滤转化为join操作提高性能，由参数qrw_inlist2join_optmode控制，详情参考：https://bbs.huaweicloud.com/blogs/285431。

但是该优化仅针对查询select场景生效，对delete场景无效，delete场景示例如下：

postgres=# set qrw_inlist2join_optmode = rule_base;
SET
postgres=# explain verbose delete from student where sno in(1,2,3,4,5);
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
  id |              operation               | E-rows | E-distinct | E-memory | E-width | E-costs 
 ----+--------------------------------------+--------+------------+----------+---------+---------
   1 | ->  Streaming (type: GATHER)         |      1 |            |          |      14 | 5.38    
   2 |    ->  Delete on public.student      |      5 |            |          |      14 | 5.23    
   3 |       ->  Seq Scan on public.student |      5 |            | 1MB      |      14 | 1.23    
 
         Predicate Information (identified by plan id)         
 --------------------------------------------------------------
   3 --Seq Scan on public.student
         Filter: (student.sno = ANY ('{1,2,3,4,5}'::integer[]))
 
 Targetlist Information (identified by plan id)
 ----------------------------------------------
   1 --Streaming (type: GATHER)
         Node/s: All datanodes
   3 --Seq Scan on public.student
         Output: sno, ctid, xc_node_id
 
   ====== Query Summary =====   
 -------------------------------
 System available mem: 4710400KB
 Query Max mem: 4710400KB
 Query estimated mem: 1024KB
(23 rows)

当student表数据量很大，in的列表很长时，该语句性能很差，可以通过如下改写方法，手动改写为join的方式，提高delete性能：

上述查询改写为：

delete from student USING (
    WITH val(id) AS(
        VALUES (1), (2), (3), (4), (5)
    )
    SELECT * FROM val
) tmp 
where student.sno = tmp.id;

改写后计划如下：

                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
  id |                 operation                 | E-rows | E-distinct | E-memory | E-width | E-costs 
 ----+-------------------------------------------+--------+------------+----------+---------+---------
   1 | ->  Streaming (type: GATHER)              |      1 |            |          |      18 | 5.52    
   2 |    ->  Delete on public.student           |      5 |            |          |      18 | 5.36    
   3 |       ->  Hash Join (4,5)                 |      5 |            | 1MB      |      18 | 1.36    
   4 |          ->  Seq Scan on public.student   |     57 | 14         | 1MB      |      14 | 1.14    
   5 |          ->  Hash                         |     20 | 5          | 16MB     |       4 | 0.11    
   6 |             ->  Values Scan on "*VALUES*" |     20 |            | 1MB      |       4 | 0.06    
 
     Predicate Information (identified by plan id)    
 -----------------------------------------------------
   3 --Hash Join (4,5)
         Hash Cond: (student.sno = "*VALUES*".column1)
 
                  Targetlist Information (identified by plan id)                  
 ---------------------------------------------------------------------------------
   1 --Streaming (type: GATHER)
         Node/s: All datanodes
   3 --Hash Join (4,5)
         Output: student.sno, student.ctid, student.xc_node_id, "*VALUES*".column1
   4 --Seq Scan on public.student
         Output: student.sno, student.ctid, student.xc_node_id
   5 --Hash
         Output: "*VALUES*".column1
   6 --Values Scan on "*VALUES*"
         Output: "*VALUES*".column1
 
   ====== Query Summary =====   
 -------------------------------
 System available mem: 4710400KB
 Query Max mem: 4710400KB
 Query estimated mem: 3328KB
(32 rows)

可以看到原先的单表inlist条件被转化成了join操作，该方式对于inlist过长的场景可以极大提升效率。