欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

postgresql子查询优化(提升子查询)

程序员文章站 2023-01-02 14:04:10
问题背景 在开发项目过程中,客户要求使用gbase8s数据库(基于informix),简单的分页页面响应很慢。排查发现分页sql是先查询出数据在外面套一层后再取多少条,如果去掉嵌套的一层,直接获取则很快。日常使用中postgresql并没有这样的操作也很快,这是为什么呢? 说明 在数据库实现早期,查 ......

问题背景

在开发项目过程中,客户要求使用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_tjaj 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_tjaj  (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_tjaj 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_tjaj  (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_tjaj 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_tjaj  (cost=0.44..32374439.85 rows=17507700 width=879)
(2 rows)

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

postgresql子查询优化

子查询可分为三类:一、([not]in/all/any/some),二、([not]exists),三、其他子查询(sjp子查询 选择、投影、连接)

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

db_jcxxzypt=#  explain select * from t_jcxxzy_tjaj aj ,(select * from t_jcxxzy_ajdsr) 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_ajdsr  (cost=0.56..8.57 rows=1 width=219)
         index cond: (c_ajbm = '1301020400000120090101'::bpchar)
   ->  seq scan on t_jcxxzy_tjaj 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_tjaj aj where aj.c_ajbm in (select dsr.c_ajbm from t_jcxxzy_ajdsr dsr);
转化为: select * from t_jcxxzy_tjaj aj join t_jcxxzy_ajdsr 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_tjaj.c_ajbm = t_jcxxzy_ajdsr.c_ajbm)
   ->  seq scan on t_jcxxzy_tjaj  (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_ajdsr  (cost=0.56..237458.59 rows=6817202 wi
dth=23)
(5 rows)
--in等价于=any
hash semi join表示执行的是两张表的hash半连接,
原始sql中没有(t_jcxxzy_tjaj.c_ajbm = t_jcxxzy_ajdsr.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_tjaj aj where c_ajbm in (select c_ajbm from t_jcxxzy_ajdsr dsr  where aj.d_slrq='2001-06-14') ;
                                                        query plan                                                      
-------------------------------------------------------------------------
 seq scan on t_jcxxzy_tjaj 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_ajdsr 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_tjaj where c_ajbm not in (select c_ajbm from db_jcxx.t_jcxxzy_ajdsr);
                                                      query plan                                                     
-------------------------------------------------------------------------
 seq scan on t_jcxxzy_tjaj  (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_ajdsr  (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_tjaj aj where  exists (select c_ajbm from t_jcxxzy_ajdsr 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_tjaj 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_ajdsr 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_tjaj aj where  exists (select c_ajbm from t_jcxxzy_ajdsr 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_tjaj 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_ajdsr dsr  (cost=0.56..237458.59 rows=6817202 widt
h=0)
(4 rows)
--exists子链接
db_jcxxzypt=# explain
db_jcxxzypt-# select * from t_jcxxzy_tjaj aj where  exists (select c_ajbm from t_jcxxzy_ajdsr 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_ajdsr dsr  (cost=0.56..4.58 rows=1 width=0)
           index cond: (c_ajbm = '1101120300000120030101'::bpchar)
   ->  seq scan on t_jcxxzy_tjaj aj  (cost=0.00..1077034.00 rows=17507700 width=879)
(6 rows)
子查询只执行了一次,作为aj表的参数。
--not exists子链接
db_jcxxzypt=# explain
db_jcxxzypt-# select * from t_jcxxzy_tjaj aj where not exists (select c_ajbm from t_jcxxzy_ajdsr 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_ajdsr dsr  (cost=0.00..281210.02 rows=6817202 width=0)
   ->  seq scan on t_jcxxzy_tjaj 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_tjaj where c_ajbm >all(select c_ajbm from db_jcxx.t_jcxxzy_ajdsr);
                                                      query plan                                            
-------------------------------------------------------------------------
 seq scan on t_jcxxzy_tjaj  (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_ajdsr  (cost=0.56..237458.59 rows=6817202 
width=23)
(5 rows)
​
db_jcxxzypt=#  explain select * from db_jcxx.t_jcxxzy_tjaj where c_ajbm =all(select c_ajbm from db_jcxx.t_jcxxzy_ajdsr);
                                                      query plan                                            
          
-------------------------------------------------------------------------
 seq scan on t_jcxxzy_tjaj  (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_ajdsr  (cost=0.56..237458.59 rows=6817202 
width=23)
(5 rows)
​
db_jcxxzypt=#  explain select * from db_jcxx.t_jcxxzy_tjaj where c_ajbm <all(select c_ajbm from db_jcxx.t_jcxxzy_ajdsr);
                                                      query plan                                                   
-------------------------------------------------------------------------
 seq scan on t_jcxxzy_tjaj  (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_ajdsr  (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_tjaj where c_ajbm  >some(select c_ajbm from db_jcxx.t_jcxxzy_ajdsr);
                                                 query plan                                                 
 
-------------------------------------------------------------------------
-
 nested loop semi join  (cost=0.56..11316607.35 rows=5835900 width=879)
   ->  seq scan on t_jcxxzy_tjaj  (cost=0.00..1077034.00 rows=17507700 width=879)
   ->  index only scan using idx_tajdsr_cajbm on t_jcxxzy_ajdsr  (cost=0.56..64266.97 rows=2272401 width=23)
         index cond: (c_ajbm < t_jcxxzy_tjaj.c_ajbm)
(4 rows)
​
db_jcxxzypt=#explain select * from db_jcxx.t_jcxxzy_tjaj where c_ajbm  =some(select c_ajbm from db_jcxx.t_jcxxzy_ajdsr);
                                                     query plan                                             
        
-------------------------------------------------------------------------
 hash semi join  (cost=362618.61..5537768.07 rows=7957409 width=879)
   hash cond: (t_jcxxzy_tjaj.c_ajbm = t_jcxxzy_ajdsr.c_ajbm)
   ->  seq scan on t_jcxxzy_tjaj  (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_ajdsr  (cost=0.56..237458.59 rows=6817202 wi
dth=23)
(5 rows)
​
db_jcxxzypt=#explain select * from db_jcxx.t_jcxxzy_tjaj where c_ajbm  <some(select c_ajbm from db_jcxx.t_jcxxzy_ajdsr);
                                                 query plan                                                 
-------------------------------------------------------------------------
​
 nested loop semi join  (cost=0.56..11316607.35 rows=5835900 width=879)
   ->  seq scan on t_jcxxzy_tjaj  (cost=0.00..1077034.00 rows=17507700 width=879)
   ->  index only scan using idx_tajdsr_cajbm on t_jcxxzy_ajdsr  (cost=0.56..64266.97 rows=2272401 width=23)
         index cond: (c_ajbm > t_jcxxzy_tjaj.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_tjaj aj ,(select * from t_jcxxzy_ajdsr 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_ajdsr  (cost=0.00..281210.02 rows=6817202 width=219)
   ->  seq scan on t_jcxxzy_tjaj aj  (cost=0.00..1077034.00 rows=17507700 width=879)
(6 rows)
​
time: 0.958 ms

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

对于被上拉的子查询,abase把子查询的关系并入主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效率高

 

相关子查询和非相关子查询

相关子查询子查询的执行依赖于外层父查询的一些属性值。子查询因依赖于父查询的参数,当父查询的参数改变时,子查询需要根据新参数值重新执行(查询优化器对相关子查询进行优化有一定意义),如:

select * from t_jcxxzy_tjaj aj where c_ajbm in (select c_ajbm from t_jcxxzy_ajdsr dsr where dsr.c_ajbm = aj.c_ajbm)/* 子查询语句中存在父查询的列 */

非相关子查询子查询的执行,不依赖于外层父查询的任何属性值。这样子查询具有独立性,可独自求解,形成一个子查询计划先于外层的查询求解,如:

select * from t_jcxxzy_tjaj aj where c_ajbm in (select c_ajbm from t_jcxxzy_ajdsr dsr where dsr.c_xzdm = '150622')/* 子查询语句中不存在父查询的属性 */

 

结束语

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

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

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

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

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

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

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

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