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中，减少扫描次数。
KINGBASE研究院
