作者介绍

张飞雄，北京华宇信息技术有限公司高级工程师，DBA，热衷开源技术，擅长SQL优化。

问题背景

在开发项目过程中，客户要求使用gbase8s数据库(基于informix)，简单的分页页面响应很慢。排查发现分页sql是先查询出数据在外面套一层后再取多少条，如果去掉嵌套的一层，直接获取则很快。日常使用中postgresql并没有这样的操作也很快，这是为什么呢？

说明

在数据库实现早期，查询优化器对子查询一般采用嵌套执行的方式，即父查询中的每一行，都要执行一次子查询，这样子查询会执行很多次，效率非常低。

本篇主要讲postgresql针对子查询的优化。

项目中使用子查询的地方非常多，如何写出高效的sql，掌握子查询的优化是非常有必要的。

执行计划对比（gbase8s vs postgresql）:

gbase8s慢sql执行计划：

--gbase8s执行计划

SET EXPLAIN ON ; SET EXPLAIN FILE TO '/home/gbasedbt/sqexplain.out' ;select skip 0 first 15 * from (select * from T_SZGL_JDRY order by T_SZGL_JDRY.updatetime desc)Estimated Cost: 3207Estimated # of Rows Returned: 61721) gbasedbt.t_szgl_jdry: INDEX PATH (1) Index Name: gbasedbt.i_t_szgl_jdry_updatetimeIndex Keys: updatetime (Reverse) (Serial, fragments: ALL)QUERY: (OPTIMIZATION TIMESTAMP: 12-21-2017 03:20:43)------select skip 0 first 15 * from (select * from T_SZGL_JDRY order by T_SZGL_JDRY.updatetime desc)Estimated Cost: 232Estimated # of Rows Returned: 61721) (Temp Table For Collection Subquery): SEQUENTIAL SCANQuery statistics:-----------------The final cost of the plan is reduced because of the FIRST n specification in the query.Table map :---------------------------- Internal name Table name---------------------------- t1 t_szgl_jdry t2 (Temp Table For Collection Subquery)type table rows_prod est_rows rows_scan time est_cost-------------------------------------------------------------------scan t1 6173 6172 6173 00:00.05 3207

--查询执行用 222 ms,15行受影响

gbase8s修改后执行计划

select skip 0 first 15 * from T_SZGL_JDRY order by T_SZGL_JDRY.updatetime descEstimated Cost: 7Estimated # of Rows Returned: 61721) gbasedbt.t_szgl_jdry: INDEX PATH (1) Index Name: gbasedbt.i_t_szgl_jdry_updatetimeIndex Keys: updatetime (Reverse) (Serial, fragments: ALL)Query statistics:-----------------The final cost of the plan is reduced because of the FIRST n specification in the query.Table map :---------------------------- Internal name Table name---------------------------- t1 t_szgl_jdrytype table rows_prod est_rows rows_scan time est_cost-------------------------------------------------------------------scan t1 15 6172 15 00:00.00 8QUERY: (OPTIMIZATION TIMESTAMP: 12-21-2017 03:23:25)------select 1 from sysusersEstimated Cost: 2Estimated # of Rows Returned: 11) gbasedbt.sysusers: SEQUENTIAL SCAN...

--查询执行用 18 ms,15行受影响

第一个执行计划中 (1) (Temp Table For Collection Subquery): SEQUENTIAL SCAN)可以看出是将子查询的结果查询出来后，在这个基础上获取了15条记录

对比postgresql执行计划

--分页执行计划-不嵌套

db_jcxxzypt=# explain select * from db_jcxx.t_jcxxzy order by d_slrq limit 15 offset 0; QUERY PLAN

Limit  (cost=0.44..28.17 rows=15 width=879)   ->  Index Scan using idx_ttjaj_dslrq on t_jcxxzy  (cost=0.44..32374439.85 rows=17507700 width=879)(2 rows)

--子查询执行计划-嵌套一层

db_jcxxzypt=# explain db_jcxxzypt-# select * from (db_jcxxzypt(# select * from db_jcxx.t_jcxxzy order by d_slrqdb_jcxxzypt(# )tab1 limit 15 offset 0;                                                QUERY PLAN

Limit  (cost=0.44..28.32 rows=15 width=879)   ->  Index Scan using idx_ttjaj_dslrq on t_jcxxzy  (cost=0.44..32374439.85 rows=17507700 width=879)(2 rows)

-子查询执行计划-嵌套两层

db_jcxxzypt=# explain db_jcxxzypt-# select * from (db_jcxxzypt(# select * from (db_jcxxzypt(# select * from db_jcxx.t_jcxxzy order by d_slrqdb_jcxxzypt(# )tab1 )tab2 limit 15 offset 0;                                                QUERY PLAN

Limit  (cost=0.44..28.32 rows=15 width=879)   ->  Index Scan using idx_ttjaj_dslrq on t_jcxxzy  (cost=0.44..32374439.85 rows=17507700 width=879)(2 rows)

postgresql的子查询即使嵌套多层，执行计划还是和未嵌套一样。原因就是postgresql在重写sql的阶段上拉子查询（提升子查询），把子查询合并到父查询中。

postgresql子查询优化

子查询可分为三类:

1. ([not]in/all/any/some),

2. ([not]exists),

3. 其他子查询(sjp子查询选择、投影、连接)

子查询可以出现在目标列、form子句、where子句、join/on子句、group by子句、having子句、orderby子句等位置。

db_jcxxzypt=#  explain select * from t_jcxxzy aj ,(select * from t_jcxxzy_ds) dsr where  dsr.c_ajbm = '1301020400000120090101';                                      QUERY PLAN

Nested Loop  (cost=0.56..1252119.58 rows=17507700 width=1098)   ->  Index Scan using idx_tajdsr_cajbm on t_jcxxzy_ds  (cost=0.56..8.57 rows=1 width=219)         Index Cond: (c_ajbm = '1301020400000120090101'::bpchar)   ->  Seq Scan on t_jcxxzy aj  (cost=0.00..1077034.00 rows=17507700 width=879)(4 rows)
Time: 1.101 ms

postgresql子链接([not]in,[not]exists,all,some,any)

子查询和子链接区别：子查询是不在表达式中的子句，子链接在表达式中的子句

--in子链接

(1).db_jcxxzypt=# explain select * from t_jcxxzy aj where aj.c_ajbm in (select dsr.c_ajbm from t_jcxxzy_ds dsr);转化为：select * from t_jcxxzy aj join t_jcxxzy_ds dsr aj.c_ajbm = dsr.c_ajbm;                                                     QUERY PLAN

Hash Semi Join  (cost=362618.61..5537768.07 rows=7957409 width=879)   Hash Cond: (t_jcxxzy.c_ajbm = t_jcxxzy_ds.c_ajbm)   ->  Seq Scan on t_jcxxzy  (cost=0.00..1077034.00 rows=17507700 width=879)   ->  Hash  (cost=237458.59..237458.59 rows=6817202 width=23)         ->  Index Only Scan using idx_tajdsr_cajbm on t_jcxxzy_ds  (cost=0.56..237458.59 rows=6817202 width=23)(5 rows)

--in等价于=anyhash semi join表示执行的是两张表的hash半连接，原始sql中没有(t_jcxxzy.c_ajbm = t_jcxxzy_ds.c_ajbm),表明此in子查询被优化，优化后采用hash semi join算法。(2).相关子查询--当加入条件where aj.d_slrq='2001-06-14'后不能提升子链接，如果把where aj.d_slrq ='2001-06-14'放到父查询是支持子链接优化的

db_jcxxzypt=# explaindb_jcxxzypt-# select * from t_jcxxzy aj where c_ajbm in (select c_ajbm from t_jcxxzy_ds dsr where aj.d_slrq='2001-06-14') ; QUERY PLAN

Seq Scan on t_jcxxzy aj  (cost=0.00..2227874766580.75 rows=8753850 width=879)   Filter: (SubPlan 1)   SubPlan 1     ->  Result  (cost=0.56..237458.59 rows=6817202 width=23)           One-Time Filter: (aj.d_slrq = '2001-06-14'::date)           ->  Index Only Scan using idx_tajdsr_cajbm on t_jcxxzy_ds dsr  (cost=0.56..237458.59 rows=6817202 width=23)(6 rows (3).
 -- not in不能提升子链接 db_jcxxzypt=#  explain select * from db_jcxx.t_jcxxzy where c_ajbm not in (select c_ajbm from db_jcxx.t_jcxxzy_ds);                                                      QUERY PLAN

Seq Scan on t_jcxxzy  (cost=0.56..2875921362927.06 rows=8753850 width=879)   Filter: (NOT (SubPlan 1))   SubPlan 1     ->  Materialize  (cost=0.56..311489.60 rows=6817202 width=23)           ->  Index Only Scan using idx_tajdsr_cajbm on t_jcxxzy_ds  (cost=0.56..237458.59 rows=6817202width=23)(5 rows)

--not in与<>all含义相同

in子句存在不被优化的可能、当in子句中包含了主查询的表字段，和主查询有相关性时不能提升子链接。

exists子链接

--exists子链接

db_jcxxzypt=# explaindb_jcxxzypt-# select * from t_jcxxzy aj where exists (select c_ajbm from t_jcxxzy_ds dsr where aj.c_ajbm = dsr.c_ajbm); QUERY PLAN

Hash Semi Join  (cost=362618.61..5537768.07 rows=7957409 width=879)   Hash Cond: (aj.c_ajbm = dsr.c_ajbm)   ->  Seq Scan on t_jcxxzy aj  (cost=0.00..1077034.00 rows=17507700 width=879)   ->  Hash  (cost=237458.59..237458.59 rows=6817202 width=23)         ->  Index Only Scan using idx_tajdsr_cajbm on t_jcxxzy_ds dsr  (cost=0.56..237458.59 rows=6817202 width=23)(5 rows)-- 当加入where aj.c_xzdm = '150622'条件在子链接时，仍然支持上拉db_jcxxzypt=# explaindb_jcxxzypt-# select * from t_jcxxzy aj where  exists (select c_ajbm from t_jcxxzy_ds dsr where aj.c_xzdm = '150622');                                                   QUERY PLAN

Nested Loop Semi Join  (cost=0.56..1361779.20 rows=5436 width=879)   ->  Seq Scan on t_jcxxzy aj  (cost=0.00..1120803.25 rows=5436 width=879)         Filter: ((c_xzdm)::text = '150622'::text)   ->  Index Only Scan using idx_tajdsr_cajbm on t_jcxxzy_ds dsr  (cost=0.56..237458.59 rows=6817202 width=0)(4 rows)

--exists子链接

db_jcxxzypt=# explaindb_jcxxzypt-# select * from t_jcxxzy aj where exists (select c_ajbm from t_jcxxzy_ds dsr where dsr.c_ajbm='1101120300000120030101')db_jcxxzypt-# ; QUERY PLAN

Result  (cost=4.58..1077038.57 rows=17507700 width=879)   One-Time Filter: $0   InitPlan 1 (returns $0)     ->  Index Only Scan using idx_tajdsr_cajbm on t_jcxxzy_ds dsr  (cost=0.56..4.58 rows=1 width=0)           Index Cond: (c_ajbm = '1101120300000120030101'::bpchar)   ->  Seq Scan on t_jcxxzy aj  (cost=0.00..1077034.00 rows=17507700 width=879)(6 rows)

子查询只执行了一次，作为aj表的参数。

--not exists子链接

db_jcxxzypt=# explaindb_jcxxzypt-# select * from t_jcxxzy aj where not exists (select c_ajbm from t_jcxxzy_ds dsr); QUERY PLAN

Result  (cost=0.04..1077034.04 rows=17507700 width=879)   One-Time Filter: (NOT $0)   InitPlan 1 (returns $0)     ->  Seq Scan on t_jcxxzy_ds dsr  (cost=0.00..281210.02 rows=6817202 width=0)   ->  Seq Scan on t_jcxxzy aj  (cost=0.00..1077034.00 rows=17507700 width=879)(5 rows)

从执行计划上看，not exists子查询并没有被消除，子查询只是执行了一次，将结果作为aj表的参数。

in和exists都存在不被优化的可能，对于in和exists的选择，当父查询结果集小于子查询结果集则选择exists，如果父查询结果集大于子查询结果集选择in。

所有的all子链接都不支持上拉

db_jcxxzypt=#  explain select * from db_jcxx.t_jcxxzy where c_ajbm >all(select c_ajbm from db_jcxx.t_jcxxzy_ds);                                                      QUERY PLAN

Seq Scan on t_jcxxzy  (cost=0.56..2875921362927.06 rows=8753850 width=879)   Filter: (SubPlan 1)   SubPlan 1     ->  Materialize  (cost=0.56..311489.60 rows=6817202 width=23)           ->  Index Only Scan using idx_tajdsr_cajbm on t_jcxxzy_ds  (cost=0.56..237458.59 rows=6817202width=23)(5 rows)
db_jcxxzypt=#  explain select * from db_jcxx.t_jcxxzy where c_ajbm =all(select c_ajbm from db_jcxx.t_jcxxzy_ds);                                                      QUERY PLAN

Seq Scan on t_jcxxzy  (cost=0.56..2875921362927.06 rows=8753850 width=879)   Filter: (SubPlan 1)   SubPlan 1     ->  Materialize  (cost=0.56..311489.60 rows=6817202 width=23)           ->  Index Only Scan using idx_tajdsr_cajbm on t_jcxxzy_ds  (cost=0.56..237458.59 rows=6817202width=23)(5 rows)
db_jcxxzypt=#  explain select * from db_jcxx.t_jcxxzy where c_ajbm <all(select c_ajbm from db_jcxx.t_jcxxzy_ds);                                                      QUERY PLAN

Seq Scan on t_jcxxzy  (cost=0.56..2875921362927.06 rows=8753850 width=879)   Filter: (SubPlan 1)   SubPlan 1     ->  Materialize  (cost=0.56..311489.60 rows=6817202 width=23)           ->  Index Only Scan using idx_tajdsr_cajbm on t_jcxxzy_ds  (cost=0.56..237458.59 rows=6817202width=23)(5 rows)

关于all的查询都都是以子查询的形式，不会上拉

some/any

--some和any是等效的

db_jcxxzypt=#explain select * from db_jcxx.t_jcxxzy where c_ajbm  >some(select c_ajbm from db_jcxx.t_jcxxzy_ds);                                                 QUERY PLAN

Nested Loop Semi Join  (cost=0.56..11316607.35 rows=5835900 width=879)   ->  Seq Scan on t_jcxxzy  (cost=0.00..1077034.00 rows=17507700 width=879)   ->  Index Only Scan using idx_tajdsr_cajbm on t_jcxxzy_ds  (cost=0.56..64266.97 rows=2272401 width=23)         Index Cond: (c_ajbm < t_jcxxzy.c_ajbm)(4 rows)
db_jcxxzypt=#explain select * from db_jcxx.t_jcxxzy where c_ajbm  =some(select c_ajbm from db_jcxx.t_jcxxzy_ds);                                                     QUERY PLAN

Hash Semi Join  (cost=362618.61..5537768.07 rows=7957409 width=879)   Hash Cond: (t_jcxxzy.c_ajbm = t_jcxxzy_ds.c_ajbm)   ->  Seq Scan on t_jcxxzy  (cost=0.00..1077034.00 rows=17507700 width=879)   ->  Hash  (cost=237458.59..237458.59 rows=6817202 width=23)         ->  Index Only Scan using idx_tajdsr_cajbm on t_jcxxzy_ds  (cost=0.56..237458.59 rows=6817202 width=23)(5 rows)
db_jcxxzypt=#explain select * from db_jcxx.t_jcxxzy where c_ajbm  <some(select c_ajbm from db_jcxx.t_jcxxzy_ds);                                                 QUERY PLAN

Nested Loop Semi Join  (cost=0.56..11316607.35 rows=5835900 width=879)   ->  Seq Scan on t_jcxxzy  (cost=0.00..1077034.00 rows=17507700 width=879)   ->  Index Only Scan using idx_tajdsr_cajbm on t_jcxxzy_ds  (cost=0.56..64266.97 rows=2272401 width=23)         Index Cond: (c_ajbm > t_jcxxzy.c_ajbm)(4 rows)

--some中未出现子查询，dsr表都被上拉到父查询中，与aj表进行嵌套半连接和hash半连接

这些查询中all是完全不支持上拉子子链接的，而in和exists存在不被上拉的可能。

不可上拉的子查询

不支持带有with子句的格式，集合操作、聚集函数(aggregates、group、distinct)、cte、having、limit/offset等子句格式

db_jcxxzypt=# explain select * from t_jcxxzy aj ,(select * from t_jcxxzy_ds limit 10) dsr where  dsr.c_ajbm = '1301020400000120090101';                                         QUERY PLAN

Nested Loop  (cost=0.00..1252111.54 rows=17507700 width=1098)   ->  Subquery Scan on dsr  (cost=0.00..0.54 rows=1 width=219)         Filter: (dsr.c_ajbm = '1301020400000120090101'::bpchar)         ->  Limit  (cost=0.00..0.41 rows=10 width=219)               ->  Seq Scan on t_jcxxzy_ds  (cost=0.00..281210.02 rows=6817202 width=219)   ->  Seq Scan on t_jcxxzy aj  (cost=0.00..1077034.00 rows=17507700 width=879)(6 rows)
Time: 0.958 ms

上拉子查询后，父级的多个表之间的连接顺序是怎么样的呢？会有什么变化吗？

对于被上拉的子查询，postgresql把子查询的关系并入主from-list中，这样关系的个数会增加，按照多表连接顺序算法就会产生更多的连接路径比如A、B、C三张表的关联就有{A,B}、{A,C}、{B,A}、{B,C}、{C,A}、{C,B}六种连接方式

join与子查询固化或rewrite

join或子查询的优化，属于优化器优化JOIN的范畴。当用户的QUERY涉及到多个JOIN对象，或者涉及到多个子查询时，优化器可以选择是否改变当前的SQL，产生更多的plan选择更优的执行计划。postgresql.conf文件中：#from_collapse_limit = 8当from列表的对象少于from_collapse_limit时，优化器可以将子查询提升到上层进行JOIN，从而可能选择到更优的执行计划。 #join_collapse_limit = 8 # 1 disables collapsing of explicit # JOIN clauses当使用显示的JOIN时（除了full join），例如a join b join c join d，优化器可以重排JOIN的顺序，以产生更多的PLAN选择更优的执行计划。如果join_collapse_limit=1，则不重排，使用SQL写法提供的顺序。如果用户要固化JOIN顺序，请使用显示的JOIN，同时将join_collapse_limit设置为1。如果用户不打算提升子查询，同样的，将from_collapse_limit 设置为1即可。

等价改写

子查询中没有group by子句，也没有聚集函数，则可使用下面的等价转换

val>all(select...)  to val>max(select...)val<all(select...) to val<min(select...)val>any(select...) to val>min(select...)val<any(select...) to val<max(select...)val>=all(select...) to val>=max(select...)val<=all(select...) to val<=min(select...)val>=any(select...) to val>=min(select...)val<=any(select...) to val<=max(select...)

通常，聚集函数min(),max()的执行效率要比any、all效率高

结束语

1.postgresql子查询的优化思路，子查询不用执行多次

2.优化器可以根据统计信息来选择不同的连接方法和不同的连接顺序

3.子查询中的连接条件，过滤条件分别变成了父查询的连接条件、过滤条件、优化器可以对这些条件进行下推、提高执行效率

4.将子查询优化为表连接后，子查询只需要执行一次、而优化器可以根据统计信息来选择不同的连接方式和连接顺序、子查询的连接条件和过滤条件分别变成父查询的条件。

5.这些查询中all是完全不支持上拉子子链接的，in和exists存在不被优化的可能

6.not exists虽然没有被上拉，但是被优化为只执行一次，相对于not in稍好

7.可使用等价改写的方式优化

8.可根据配置文件，固化子查询，以及表的连接顺序

ITPub博客

PostgreSQL 子查询优化分享