KingbaseES 查询优化消除SubPlan

说明：

日常业务系统在使用SQL语句进行查询时，开发人员容易将sql查询的子查询放到select语句中进行使用，会造成sql性能的下降。

数据准备：

test=# 
test=# select count(1) from student;
 count 
-------
   499
(1 行记录)

test=# select count(1) from course; 
 count 
-------
     4
(1 行记录)

test=# select count(1) from SCORE; 
 count 
-------
   506
(1 行记录)

示例1：

 test=# explain (verbose, analyze, buffers)   select student.sno , student.SNAME ,score.SNO ,SCORE.SCORE ,
        (select CNAME from course where course.cno = score.cno) CNAME  from student  left join SCORE on true;
                                                                        QUERY PLAN                                                                       
 --------------------------------------------------------------------------------------------------------------------------------------------------------
  Nested Loop Left Join  (cost=0.00..2064859.00 rows=252494 width=99) (actual time=0.020..365.552 rows=252494 loops=1)
    Output: student.sno, student.sname, score.sno, score.score, (SubPlan 1)
    Buffers: shared hit=505066
    ->  Seq Scan on public.student  (cost=0.00..11.99 rows=499 width=11) (actual time=0.004..0.086 rows=499 loops=1)
          Output: student.sno, student.sname, student.gender, student.phone, student.id_no, student.city, student.reg_date, student.job, student.company
          Buffers: shared hit=7
    ->  Materialize  (cost=0.00..78.59 rows=506 width=15) (actual time=0.000..0.027 rows=506 loops=499)
          Output: score.sno, score.score, score.cno
          Buffers: shared hit=71
          ->  Seq Scan on public.score  (cost=0.00..76.06 rows=506 width=15) (actual time=0.004..0.121 rows=506 loops=1)
                Output: score.sno, score.score, score.cno
                Buffers: shared hit=71
    SubPlan 1
      ->  Index Scan using cno_pk on public.course  (cost=0.15..8.17 rows=1 width=78) (actual time=0.001..0.001 rows=1 loops=252494)
            Output: course.cname
            Index Cond: (course.cno = score.cno)
            Buffers: shared hit=504988
  Planning Time: 0.229 ms
  Execution Time: 374.383 ms
 (19 行记录)

提升查询：

test=# explain (verbose, analyze, buffers)   select student.sno , student.SNAME ,score.SNO ,SCORE.SCORE ,course.CNAME CNAME
       from student  left join SCORE on true left join course on  course.cno = score.cno;
                                                                       QUERY PLAN                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=20.35..3267.18 rows=252494 width=99) (actual time=0.026..42.468 rows=252494 loops=1)
   Output: student.sno, student.sname, score.sno, score.score, course.cname
   Buffers: shared hit=79
   ->  Seq Scan on public.student  (cost=0.00..11.99 rows=499 width=11) (actual time=0.007..0.102 rows=499 loops=1)
         Output: student.sno, student.sname, student.gender, student.phone, student.id_no, student.city, student.reg_date, student.job, student.company
         Buffers: shared hit=7
   ->  Materialize  (cost=20.35..100.28 rows=506 width=88) (actual time=0.000..0.024 rows=506 loops=499)
         Output: score.sno, score.score, course.cname
         Buffers: shared hit=72
         ->  Hash Left Join  (cost=20.35..97.75 rows=506 width=88) (actual time=0.016..0.264 rows=506 loops=1)
               Output: score.sno, score.score, course.cname
               Inner Unique: true
               Hash Cond: (score.cno = course.cno)
               Buffers: shared hit=72
               ->  Seq Scan on public.score  (cost=0.00..76.06 rows=506 width=15) (actual time=0.002..0.098 rows=506 loops=1)
                     Output: score.sno, score.cno, score.ino, score.exam_date, score.score, score.certificate
                     Buffers: shared hit=71
               ->  Hash  (cost=14.60..14.60 rows=460 width=90) (actual time=0.008..0.009 rows=4 loops=1)
                     Output: course.cname, course.cno
                     Buckets: 1024  Batches: 1  Memory Usage: 9kB
                     Buffers: shared hit=1
                     ->  Seq Scan on public.course  (cost=0.00..14.60 rows=460 width=90) (actual time=0.004..0.005 rows=4 loops=1)
                           Output: course.cname, course.cno
                           Buffers: shared hit=1
 Planning Time: 0.106 ms
 Execution Time: 52.229 ms
(26 行记录)

示例2：

test=# explain (verbose, analyze, buffers) select score.cno, 
       (select course.cname  from course where course.cno = score.cno ) sno from score  ;
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on public.score  (cost=0.00..4207.55 rows=506 width=83) (actual time=0.024..0.709 rows=506 loops=1)
   Output: score.cno, (SubPlan 1)
   Buffers: shared hit=1083
   SubPlan 1
     ->  Index Scan using cno_pk on public.course  (cost=0.15..8.17 rows=1 width=78) (actual time=0.001..0.001 rows=1 loops=506)
           Output: course.cname
           Index Cond: (course.cno = score.cno)
           Buffers: shared hit=1012
 Planning Time: 0.069 ms
 Execution Time: 0.746 ms
(10 行记录)

提升查询：

test=# explain (verbose, analyze, buffers) select score.cno, course.cname 
       from score ,LATERAL(select course.cname from course where  course.cno = score.cno)  course ;
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=20.35..97.75 rows=506 width=83) (actual time=0.019..0.236 rows=506 loops=1)
   Output: score.cno, course.cname
   Inner Unique: true
   Hash Cond: (score.cno = course.cno)
   Buffers: shared hit=72
   ->  Seq Scan on public.score  (cost=0.00..76.06 rows=506 width=5) (actual time=0.005..0.087 rows=506 loops=1)
         Output: score.sno, score.cno, score.ino, score.exam_date, score.score, score.certificate
         Buffers: shared hit=71
   ->  Hash  (cost=14.60..14.60 rows=460 width=90) (actual time=0.008..0.009 rows=4 loops=1)
         Output: course.cname, course.cno
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         Buffers: shared hit=1
         ->  Seq Scan on public.course  (cost=0.00..14.60 rows=460 width=90) (actual time=0.004..0.005 rows=4 loops=1)
               Output: course.cname, course.cno
               Buffers: shared hit=1
 Planning Time: 0.125 ms
 Execution Time: 0.271 ms
(17 行记录)

结论：

SQL查询语句SELECT中出现的子查询会随着查询结果条数进行Loop循环，每条进行一次数据的匹配查询，会造成查询性能的下降，
建议在写业务查询SQL时，将SELECT子查询进行提升到FROM中，减少扫描次数。