首页 > 资讯 > 数据库 >PostgreSQL常用优化技巧示例介绍

846

分享到

PostgreSQL常用优化技巧示例介绍

2024-04-02 19:04:59 846人浏览八月长安

摘要

目录1、标量子查询与filter2、视图合并3、谓词推入1、标量子查询与filter 当一个查询在select和from之间，那么这种子查询就是标量子查询。实际应用中，很多人在写sql时为了方便会写一堆标量子查

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结果的情况下才能进行的。

到此这篇关于PostgreSQL常用优化技巧示例介绍的文章就介绍到这了,更多相关PostgreSQL优化技巧内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

您可能感兴趣的文档:

--结束END--

本文标题: PostgreSQL常用优化技巧示例介绍

本文链接: https://lsjlt.com/news/34069.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

回答

如何调试操作系统的错误？
操作系统

2023-11-15发布

回答

操作系统中的I/O系统是如何实现的？
操作系统

2023-11-15发布

回答

如何实现操作系统的内存管理？
操作系统

2023-11-15发布

回答

什么是虚拟内存，它对操作系统有什么影响？
操作系统

2023-11-15发布

回答

ASP中的MVC架构和WebForms架构有什么区别和使用场景？
ASP.NET

2023-11-15发布

回答

ASP中的数据验证和数据校验有什么不同？
ASP.NET

2023-11-15发布

回答

ASP中的ADO对象和DAO对象有什么区别和使用方法？
ASP.NET

2023-11-15发布

回答

Node.js中的包管理器NPM是什么？如何使用它进行依赖管理？
node.js

2023-11-15发布

回答

Vue.js中的动态组件是什么？如何使用它来动态渲染组件？
VUE

2023-11-15发布

回答

如何使用Vue.js实现懒加载和预加载？
VUE

2023-11-15发布

PostgreSQL常用优化技巧示例介绍

目录

1、标量子查询与filter

2、视图合并

3、谓词推入

PostgreSQL常用优化技巧示例介绍

优化Python中len函数的性能技巧介绍

Razor常用语法介绍及示例

帝国CMS的采集常用小技巧介绍

PostgreSQL中的查询优化技巧有哪些

DIV CSS常用优化技巧有哪些

HiveSQL常用（下篇：使用技巧与优化）

常用Windows 8操作小技巧集合详细介绍

Mysql优化技巧之Limit查询的示例分析

常用SQL语句优化技巧有哪些

Windows 8技巧：Windows 8中FlipView的使用技巧介绍

Windows 8技巧：Windows 8常用鼠标事件分析介绍

交互式可视化js库gojs使用介绍及技巧

Android常用布局使用技巧示例讲解

Win8系统的个性化锁屏技巧的介绍

PostgreSQL中查询优化的示例分析

常见的HTML优化技巧有哪些

Java序列化和反序列化示例介绍

XP桌面文字技巧使用介绍

PHP gPRC 实战技巧：解决常见问题和优化技巧

关于SQL建表语句使用详解

HBase在大数据审计与合规性追踪中的应用

MySQL与HBase在大数据金融分析中的性能与可扩展性对比

HBase的Region Server之间的网络通信优化

HBase在大数据监控与告警系统中的实时数据处理能力

MySQL与HBase在大数据安全策略中的实现与对比

HBase的分布式事务处理在复杂业务场景中的应用

MySQL与HBase在混合存储架构中的整合策略

HBase如何支持高效的二级索引查询

MySQL与HBase在物联网数据收集与处理中的协作模式