PostgreSQL EXPLAIN ANALYZE使用和检查SQL性能问题

helenbi

已于 2024-06-05 10:41:47 修改

阅读量7.4k

点赞数 1

分类专栏： PostgreSQL 文章标签：数据库 postgresql database

于 2022-03-30 12:09:55 首次发布

本文链接：https://blog.csdn.net/helenbi/article/details/123841896

版权

PostgreSQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

一、PostgreSQL explain介绍

PG的explain工作原理是基于表和索引的统计信息，这些统计信息主要存储在系统表pg_statistic，也可以通过视图pg_stats来查看统计信息，包括表的列，行数，distinct值、null值等。统计信息一般是在vacuum或者analyze的时候进行更新。

下面是一个简单的例子：

通过explain可以查看SQL查询的计划以及基表的扫描方式、基表的估计行数和估计cost以及多表的连接顺序、连接方式和连接的估计行数和估计cost。上面的例子表a和表b都是使用顺序扫描，估计的行数分别是1和99999，两者通过嵌套循环进行连接，连接后估计行数是99999。

explain的语法结构：

EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
where option can be one of:
ANALYZE [ boolean ]
VERBOSE [ boolean ]
COSTS [ boolean ]
SETTINGS [ boolean ]
BUFFERS [ boolean ]
TIMING [ boolean ]
SUMMARY [ boolean ]
FORMAT { TEXT | XML | JSON | YAML }

可以通过使用EXPLAIN的ANALYZE选项来检查优化器估计值的准确性。通过使用ANALYZE选项，EXPLAIN会实际执行该查询，然后显示真实的行计数和在每个计划结点中累计的真实运行时间，还会有一个普通EXPLAIN显示的估计值。

EXPLAIN的BUFFERS选项可以和ANALYZE一起使用来得到运行时shared buffer读写次数， BUFFERS提供的信息帮助我们标识查询的哪些部分是对 I/O 最敏感的。

二、通过explain查找问题

1. 定位比较糟糕的统计信息

通过explain analyze查看估计rows和实际执行的rows的差别，比如下面的例子中，实际执行的行数要远小于估计的行数，因此说明现有的统计信息已经过时，需要使用analyze进行更新以得到更准确的统计值。统计信息更新后，估计行数和实际执行行数比较接近。估计行数的多少决定了表的扫描方式以及多表的连接方式。所以优化器是根据统计信息给出查询计划的。

比如下面的例子中，统计信息更正前使用顺序扫描，更正后使用索引扫描。