PostgreSQL常用优化技巧示例介绍

数据库技术
369
0
0
2023-02-20
分库分表经典15连问
上一篇
MariaDB 连接查询
下一篇
标签   PostgreSQL
目录
  • 1、标量子查询与filter
  • 2、视图合并
  • 3、谓词推入

1、标量子查询与filter

当一个查询在select和from之间，那么这种子查询就是标量子查询。实际应用中，很多人在写SQL时为了方便会写一堆标量子查询的SQL，在表数据不大时，一般并不会有什么影响，但当数据量较大时，往往会对性能造成巨大影响。

因为标量子查询类似于一个天然的嵌套循环，而且驱动表固定为主表。如下所示：

bill=# explain select empno,ename,sal,deptno,
bill-# (select d.dname from dept d where d.deptno = e.deptno) as dname
bill-# from emp e;
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on emp e  (cost=0.00..15.84 rows=14 width=64)
   SubPlan 1
     ->  Seq Scan on dept d  (cost=0.00..1.05 rows=1 width=9)
           Filter: (deptno = e.deptno)
(4 rows)

对于上面的SQL，emp表每输出一行数据，都要去dept表中全表扫描一遍。

而我们都知道，嵌套循环的被驱动表的连接列必须包含在索引中，同理，标量子查询的表的连接列也必须包含在索引中。但是我们在实际写SQL时还是要避免使用标量子查询，否则主表返回大量数据时，子表得被多次遍历，从而对SQL性能产生巨大影响。

那么对于标量子查询的SQL我们该怎么优化呢？最常用的就是改写成外连接，这样对于PostgreSQL的优化器而言可以根据实际情况去选择表的连接方式。这里需要注意的是，不能将标量子查询改成内连接，我们前面的例子中也可以看到，标量子查询实际是一个传值的过程，当主表传值给子表时，如果没有相应的值则会显示NULL，而如果使用内连接的话这部分数据就丢失了。

因此，上面的标量子查询可以改写成：

可以看到，优化器根据实际情况选择了更合适的hash join。

bill=# explain select e.empno,e.ename,e.sal,e.deptno,d.dname
bill-# from emp e
bill-# left join dept d on (d.deptno = e.deptno);
                            QUERY PLAN
-------------------------------------------------------------------
 Hash Left Join  (cost=1.09..2.31 rows=14 width=27)
   Hash Cond: (e.deptno = d.deptno)
   ->  Seq Scan on emp e  (cost=0.00..1.14 rows=14 width=18)
   ->  Hash  (cost=1.04..1.04 rows=4 width=13)
         ->  Seq Scan on dept d  (cost=0.00..1.04 rows=4 width=13)
(5 rows)

当主表连接列是外键，而子表的连接列是主键时，使用内连接也可以，因为外键自然不会存在NULL值。

bill=# explain select e.empno,e.ename,e.sal,e.deptno,d.dname
bill-# from emp e
bill-# inner join dept d on (d.deptno = e.deptno);
                            QUERY PLAN
-------------------------------------------------------------------
 Hash Join  (cost=1.09..2.31 rows=14 width=27)
   Hash Cond: (e.deptno = d.deptno)
   ->  Seq Scan on emp e  (cost=0.00..1.14 rows=14 width=18)
   ->  Hash  (cost=1.04..1.04 rows=4 width=13)
         ->  Seq Scan on dept d  (cost=0.00..1.04 rows=4 width=13)
(5 rows)

除了标量子查询外，往往filter也会产生类似的情况，因为在filter中驱动表也会被固定住，那么优化器可能会选择低效的执行计划。而对于PostgreSQL而言本身也不支持hint功能，如果错误的执行计划被固定，那么往往只能去改写SQL。

这里说明下下filter，在PostgreSQL中filter主要有2种情况，一种是我们常见的where后面过滤数据的，这种一般不会产生什么性能问题，例如：

bill=# explain select * from t where id < 10;
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on t  (cost=0.00..16925.00 rows=100 width=4)
   Filter: (id < 10)
(2 rows)

而另一种就是filter中是一些表的连接条件，这种呢便是我们前面说的情况，往往需要去关注的，例如：

bill=# explain select  exists (select 1 from t where t.id=n.id) from n;
                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on n  (cost=0.00..169250145.00 rows=10000 width=1)
   SubPlan 1
     ->  Seq Scan on t  (cost=0.00..16925.00 rows=1 width=0)
           Filter: (id = n.id)
(4 rows)

那么哪些写法会容易产生filter呢？在PostgreSQL中当使用exists或者not exists时，或者子查询中有固话子查询的关键词，如union、union all、cube、rollup、limit等，那么执行计划往往容易产生filter。

因此上面的SQL我们用in去替换exists进行改写：

bill=# explain select id in (select id from t) from n;
                               QUERY PLAN
-------------------------------------------------------------------------
 Seq Scan on n  (cost=0.00..129160170.00 rows=10000 width=1)
   SubPlan 1
     ->  Materialize  (cost=0.00..23332.00 rows=1000000 width=4)
           ->  Seq Scan on t  (cost=0.00..14425.00 rows=1000000 width=4)
(4 rows)

除此之外，在PostgreSQL中我们更推荐使用= any的方式去改写该类SQL：

bill=# explain select id = any(array(select id from t)) from n;
                            QUERY PLAN
-------------------------------------------------------------------
 Seq Scan on n  (cost=14425.00..14695.00 rows=10000 width=1)
   InitPlan 1 (returns $0)
     ->  Seq Scan on t  (cost=0.00..14425.00 rows=1000000 width=4)
(3 rows)

当然这并不是说in的写法就一定比exists要好，只是相较于exists更不容易产生filter。这是为什么呢？因为如果子查询中包含我们上面提到的固化关键字时，子查询会被固化为一个整体，当采用exists写法时，如果子查询中有主表的连接列，那么便只能是主表通过连接列给子查询中的表传值，因此会选择filter。而使用in的写法，即使子查询被固化，但如果没有主表连接列的字段，那么便不会选择filter。

2、视图合并

不知道大家有没有遇到过类似下面的情况：

select xxx from () t1, () t2 where t1.id = t2.id;

明明t1和t2两个子查询单独执行都很快，但是放到一起速度却变得特别慢，这种情况往往就是视图合并所导致的。

例如下面的SQL：

我们按照SQL中的顺序来看应该是emp和dept两表先进行关联，然后再去和salgrade表关联。但执行计划中的顺序却变成了emp和salgrade表先关联，最后才去关联dept表。

这说明发生了视图合并，即视图/子查询中的内容被拆开了。

bill=# explain select a.*,c.grade
bill-#     from (select ename,sal,a.deptno,b.dname
bill(#         from emp a,dept b
bill(#         where a.deptno = b.deptno) a,
bill-#         salgrade c
bill-#     where a.sal between c.losal and c.hisal;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Hash Join  (cost=1.09..4.56 rows=8 width=27)
   Hash Cond: (a.deptno = b.deptno)
   ->  Nested Loop  (cost=0.00..3.43 rows=8 width=18)
         Join Filter: ((a.sal >= c.losal) AND (a.sal <= c.hisal))
         ->  Seq Scan on emp a  (cost=0.00..1.14 rows=14 width=14)
         ->  Materialize  (cost=0.00..1.07 rows=5 width=12)
               ->  Seq Scan on salgrade c  (cost=0.00..1.05 rows=5 width=12)
   ->  Hash  (cost=1.04..1.04 rows=4 width=13)
         ->  Seq Scan on dept b  (cost=0.00..1.04 rows=4 width=13)
(9 rows)

从上面的例子可以看出，视图合并一般产生性能问题都是因为发生视图合并后表的连接顺序变化导致的。不过一般优化器这么做是为了帮我们选择更合适的表连接顺序，而当优化器选择了错误的连接顺序时，我们就有必要对SQL进行改写了。

由于PostgreSQL中我们无法使用hint来让优化器禁止视图合并，所以我们便需要了解一些SQL改写的技巧。

和前面的filter一样，当我们将视图/子查询固化后，那么便不能进行视图合并。因此上面的SQL我们可以改写为：

加上group by后，子查询被固化，视图没有发生合并，emp和dept表先进行关联了。

bill=# explain select a.*,c.grade
bill-#     from (select ename,sal,a.deptno,b.dname
bill(#         from emp a,dept b
bill(#         where a.deptno = b.deptno group by ename,sal,a.deptno,b.dname) a,
bill-#         salgrade c
bill-#     where a.sal between c.losal and c.hisal;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Nested Loop  (cost=2.45..5.02 rows=8 width=27)
   Join Filter: ((a.sal >= c.losal) AND (a.sal <= c.hisal))
   ->  HashAggregate  (cost=2.45..2.59 rows=14 width=23)
         Group Key: a.ename, a.sal, a.deptno, b.dname
         ->  Hash Join  (cost=1.09..2.31 rows=14 width=23)
               Hash Cond: (a.deptno = b.deptno)
               ->  Seq Scan on emp a  (cost=0.00..1.14 rows=14 width=14)
               ->  Hash  (cost=1.04..1.04 rows=4 width=13)
                     ->  Seq Scan on dept b  (cost=0.00..1.04 rows=4 width=13)
   ->  Materialize  (cost=0.00..1.07 rows=5 width=12)
         ->  Seq Scan on salgrade c  (cost=0.00..1.05 rows=5 width=12)
(11 rows)

3、谓词推入

说完视图合并，我们再来看看你视图不能合并时会出现的一种情况——谓词推入。即对于那些不能合并的视图，并且有谓词进行过滤，CBO会将谓词过滤条件推入到视图内，为了尽早的过滤掉无用的数据，从而提升性能。

从CBO的角度来看，进行谓词推入自然是好的，因为可以提前过滤掉不需要的数据。但是如果推入的谓词是连接列的，那么可能导致表的join产生变化，SQL性能变得更差。

如下SQL所示：

外层的谓词d.deptno between c.losal and c.hisal推入到了视图里面。

bill=# create or replace view v1 as select ename,sal,a.deptno,b.dname
bill-#         from emp a,dept b
bill-#         where a.deptno = b.deptno;
CREATE VIEW
bill=# explain select d.*,c.grade from v1 d,salgrade c
bill-#     where d.deptno between c.losal and c.hisal;
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Hash Join  (cost=1.09..4.56 rows=8 width=27)
   Hash Cond: (a.deptno = b.deptno)
   ->  Nested Loop  (cost=0.00..3.43 rows=8 width=18)
         Join Filter: ((a.deptno >= c.losal) AND (a.deptno <= c.hisal))
         ->  Seq Scan on emp a  (cost=0.00..1.14 rows=14 width=14)
         ->  Materialize  (cost=0.00..1.07 rows=5 width=12)
               ->  Seq Scan on salgrade c  (cost=0.00..1.05 rows=5 width=12)
   ->  Hash  (cost=1.04..1.04 rows=4 width=13)
         ->  Seq Scan on dept b  (cost=0.00..1.04 rows=4 width=13)
(9 rows)

那我们该如何防止谓词内推呢？在Oracle中可以通过关闭连接列的谓词推入的隐藏参数_push_join_predicate来实现，那么在PostgreSQL中又该如何实现呢？

和上面类似，我们可以将视图固化来避免这种情况，但一般来说不建议将视图固化，因为大部分情况谓词推入大多数对性能是有好处的。例如当我们在视图中使用limit时会导致谓词无法推入，因此一般也不建议在视图中使用limit，为什么呢？因为如果谓词进行推入的话，limit取到的值可能就不同了，会对结果集产生影响，所以自然不能推入了，因为优化器的任何等价转换都是在不改变SQL结果的情况下才能进行的。

分库分表经典15连问
上一篇
MariaDB 连接查询
下一篇