PG中文社区 /
mdi-home
首页
社区新闻
中文文档
加入ACE
相关资料
mdi-chevron-down
{{ item.text }}
登录
mdi-home
首页
mdi-chat-processing
社区新闻
mdi-book-open-variant
中文文档
mdi-account-multiple-check
加入ACE
mdi-file-multiple-outline
相关资料
mdi-blank
{{item.text}}
mdi-exit-to-app
退出账号
首页
-->
社区新闻
-->
强人随笔
PostgreSQL 子查询优化分享
原作者:张飞雄 创作时间:2019-07-08 23:54:58+08
wangliyun 发布于2019-07-09 08:25:58
评论: 0
浏览: 6027
顶: 464
踩: 510
### 作者介绍 #### 张飞雄,北京华宇信息技术有限公司高级工程师,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: 3207 Estimated # of Rows Returned: 6172 1) gbasedbt.t_szgl_jdry: INDEX PATH (1) Index Name: gbasedbt.i_t_szgl_jdry_updatetime Index 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: 232 Estimated # of Rows Returned: 6172 1) (Temp Table For Collection Subquery): SEQUENTIAL SCAN 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_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 desc Estimated Cost: 7 Estimated # of Rows Returned: 6172 1) gbasedbt.t_szgl_jdry: INDEX PATH (1) Index Name: gbasedbt.i_t_szgl_jdry_updatetime Index 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_jdry type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 15 6172 15 00:00.00 8 QUERY: (OPTIMIZATION TIMESTAMP: 12-21-2017 03:23:25) ------ select 1 from sysusers Estimated Cost: 2 Estimated # of Rows Returned: 1 1) 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_slrq db_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_slrq db_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 wi dth=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=# explain db_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=6817 202 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=6817202 width=23) (5 rows) ``` #### --not in与<>all含义相同 #### in子句存在不被优化的可能、当in子句中包含了主查询的表字段,和主查询有相关性时不能提升子链接。 ### exists子链接 #### --exists子链接 ``` db_jcxxzypt=# explain db_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=681720 2 width=23) (5 rows) ``` #### -- 当加入where aj.c_xzdm = '150622'条件在子链接时,仍然支持上拉 ``` db_jcxxzypt=# explain db_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 widt h=0) (4 rows) ``` #### --exists子链接 ``` db_jcxxzypt=# explain db_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=# explain db_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=6817202 width=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=6817202 width=23) (5 rows) ``` ``` db_jcxxzypt=# explain select * from db_jcxx.t_jcxxzy where c_ajbm
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=6817202 width=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 wi dth=23) (5 rows) ``` ``` db_jcxxzypt=#explain select * from db_jcxx.t_jcxxzy where c_ajbm
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
any(select...) to val>min(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效率高 ### 相关子查询和非相关子查询 #### 相关子查询子查询的执行依赖于外层父查询的一些属性值。子查询因依赖于父查询的参数,当父查询的参数改变时,子查询需要根据新参数值重新执行(查询优化器对相关子查询进行优化有一定意义),如: #### select * from tjcxxzytjaj aj where cajbm in (select cajbm from tjcxxzyajdsr dsr where dsr.cajbm = aj.cajbm)/* 子查询语句中存在父查询的列 */ #### 非相关子查询子查询的执行,不依赖于外层父查询的任何属性值。这样子查询具有独立性,可独自求解,形成一个子查询计划先于外层的查询求解,如: #### select * from tjcxxzytjaj aj where cajbm in (select cajbm from tjcxxzyajdsr dsr where dsr.c_xzdm = '150622')/* 子查询语句中不存在父查询的属性 */ ### 结束语 #### 1.postgresql子查询的优化思路,子查询不用执行多次 #### 2.优化器可以根据统计信息来选择不同的连接方法和不同的连接顺序 #### 3.子查询中的连接条件,过滤条件分别变成了父查询的连接条件、过滤条件、优化器可以对这些条件进行下推、提高执行效率 #### 4.将子查询优化为表连接后,子查询只需要执行一次、而优化器可以根据统计信息来选择不同的连接方式和连接顺序、子查询的连接条件和过滤条件分别变成父查询的条件。 #### 5.这些查询中all是完全不支持上拉子子链接的,in和exists存在不被优化的可能 #### 6.not exists虽然没有被上拉,但是被优化为只执行一次,相对于not in稍好 #### 7.可使用等价改写的方式优化 #### 8.可根据配置文件,固化子查询,以及表的连接顺序 ![CENTER_PostgreSQL_Community](/images/news/2016/pg_bot_banner.jpg)
评论:0
浏览: 6027
顶: 464
踩: 510
评论:
请在
登录
后发表评论,否则无法保存。
发表评论:
您还没有登录,请您登录后再发表评论
加入我们
QQ群1:5276420
QQ群2:3336901
QQ群3:254622631
文档群:150657323
文档翻译平台:
按此访问
社区邮件列表:
按此订阅
商业支持
成都文武信息技术有限公司
杭州乘数科技有限公司
阿里云
华为云
青云(北京优帆科技有限公司)
扫码关注
加入我们
QQ群1:5276420
QQ群2:3336901
QQ群3:254622631
文档群:150657323
文档翻译平台:
按此访问
社区邮件列表:
按此订阅
商业支持
成都文武信息技术有限公司
杭州乘数科技有限公司
阿里云
华为云
青云(北京优帆科技有限公司)
扫码关注
© PostgreSQL中文社区 ... (自2010年起)