译者简介
晏杰宏，任职于上海新炬网络信息技术股份有限公司，Oracle DBA。负责数据库、中间件、大数据等基础软件建设、优化和业务保障工作。具有10年以上电信与银行企业一线/二线数据库运维管理经验。目前专注研究云计算、开源软件和国产化数据库等领域技术研究。
校对者简介
朱君鹏，博士研究生。主要研究方向为数据库管理系统，尤其是内存数据库、事务处理系统、软硬件协同设计、日志系统。个人github https://github.com/EthanZhu-DB
postgres=# explain SELECT * FROM org where 'aa'::text IN (SELECT jsonb_array_elements(info -> 'dept') ->> 'name');QUERY PLAN-------------------------------------------------------------------------Seq Scan on org (cost=0.00..719572.55 rows=249996 width=1169)Filter: (SubPlan 1)SubPlan 1-> Result (cost=0.00..2.27 rows=100 width=32)-> ProjectSet (cost=0.00..0.52 rows=100 width=32)-> Result (cost=0.00..0.01 rows=1 width=0)
他们知道自己已经创建了一个索引，并好奇为什么没有使用该索引。我们要收集的下一个数据点是关于索引本身的信息，结果发现他们是这样创建索引的
CREATE INDEX idx_org_dept ON org ((info -> 'dept'::text) ->> 'name'::text));
注意到什么了吗？他们的查询将info->“dept”包装在一个名为jsonb_array_elements（）的函数中，这导致查询计划认为不应该使用索引。修复很简单，在对客户的查询进行了相当快的调整之后，我们能够让达到客户需求。客户将其查询更改为以下内容后，索引开始被扫描：
postgres=# SELECT * FROM org where 'aa'::text IN (info -> 'dept' ->> 'name');postgres=# explain SELECT * FROM organization where 'aa'::text IN (info -> 'dept' ->> 'name');QUERY PLAN----------------------------------------------------------------------------------------------Index Scan using idx_org_dept on org (cost=0.42..8.44 rows=1 width=1169)Index Cond: ('aa'::text = ((info -> 'dept'::text) ->> 'name'::text))(2 rows)
如我们所见，在您的故障排除库中使用EXPLAIN是非常宝贵的。
什么是EXPLAIN？
基于成本的方法
对于查询规划器来说，磁盘上的所有数据基本上是相同的。要确定到达特定数据块的最快方式，需要对执行完整表扫描、合并两个表以及将数据返回给用户的其他操作所需的时间进行一些估计。PostgreSQL通过为每个执行任务分配成本来实现这一点，这些值是从postgresql.conf文件（请参阅以*_cos结尾或以enable_*开头的参数）。当查询被发送到数据库时，查询计划器计算不同执行策略的累计成本，并选择最理想的计划（可能不一定是成本最低的计划）。
bash $ pgbench -i && psql<...>postgres=# EXPLAIN SELECT * FROM pgbench_accounts a JOIN pgbench_branches b ON (a.bid=b.bid) WHERE a.aid < 100000;QUERY PLAN--------------------------------------------------------------------------------Nested Loop (cost=0.00..4141.00 rows=99999 width=461)Join Filter: (a.bid = b.bid)-> Seq Scan on pgbench_branches b (cost=0.00..1.01 rows=1 width=364)-> Seq Scan on pgbench_accounts a (cost=0.00..2890.00 rows=99999 width=97)Filter: (aid < 100000)(5 rows)
在这里，我们看到在pgbench_accounts上的Seq扫描花费了2890来执行任务。这个价值从何而来？如果我们查看一些设置并进行计算，我们会发现：
cost = ( #blocks * seq_page_cost ) + ( #records * cpu_tuple_cost ) + ( #records * cpu_filter_cost )postgres=# select pg_relation_size('pgbench_accounts');pg_relation_size------------------13434880block_size = 8192 (8kB, typical OS)#blocks = 1640 (relation_size block_size)#records = 100000seq_page_cost = 1 (default)cpu_tuple_cost = 0.01 (default)cpu_filter_cost = 0.0025 (default)cost = ( 1640 * 1 ) + ( 100000 * 0.01 ) + ( 100000 * 0.0025 ) = 2890
如我们所见，成本直接基于查询计划器可以处理的一些内部统计数据。
关于统计分析的注意事项
查询计划器根据存储在pg_statistic中的统计信息来计算成本（不要看那里——那里没有任何人类可读的东西）。如果您想查看表和行统计信息，请尝试查看pg_stats）。如果这些内部统计信息中的任何一个是关闭的（例如，膨胀的表或太多的连接导致遗传查询优化器启动），那么可能会选择一个次优计划，从而导致查询性能较差。有错误的统计数据并不一定是个问题——统计数据并不总是实时更新的，而且很大程度上依赖于PostgreSQL的内部维护。因此，必须定期进行数据库维护——这意味着经常进行清空和分析。如果没有良好的统计数据，你可能会得到这样的结果：
postgres=# EXPLAIN SELECT * FROM pgbench_history WHERE aid < 100;QUERY PLAN-----------------------------------------------------------------------Seq Scan on pgbench_history (cost=0.00..2346.00 rows=35360 width=50)Filter: (aid < 100)
在上面的例子中，数据库进行了大量的活动，并且统计数据不准确。使用ANALYZE（不是VACUUM ANALYZE or EXPLAIN ANALYZE，而是普通的ANALYZE），统计信息是固定的，并且查询计划器现在选择索引扫描：
postgres=# EXPLAIN SELECT * FROM pgbench_history WHERE aid < 100;QUERY PLAN----------------------------------------------------------------------Index Scan using foo on pgbench_history (cost=0.42..579.09 rows=153 width=50)Index Cond: (aid < 100)
EXPLAIN ANALYZE有何帮助？
当EXPLAIN被添加到查询之前时，将打印查询计划，但不会运行查询。我们不知道存储在数据库中的统计数据是否正确，也不知道某些操作是否需要昂贵的I/O而不是完全在内存中运行。与ANALYZE一起使用时，查询实际上是运行的，查询计划以及一些底层活动会被打印出来。 如果我们查看上面的第一个查询并运行EXPLAIN ANALYZE而不是普通的EXPLAIN，我们得到：
postgres=# EXPLAIN ANALYZE SELECT * FROM pgbench_accounts a JOIN pgbench_branches b ON (a.bid=b.bid) WHERE a.aid < 100000;QUERY PLAN-------------------------------------------------------------------------------------------------------------Nested Loop (cost=0.00..4141.00 rows=99999 width=461) (actual time=0.039..56.582 rows=99999 loops=1)Join Filter: (a.bid = b.bid)-> Seq Scan on pgbench_branches b (cost=0.00..1.01 rows=1 width=364) (actual time=0.025..0.026 rows=1 loops=1)-> Seq Scan on pgbench_accounts a (cost=0.00..2890.00 rows=99999 width=97) (actual time=0.008..25.752 rows=99999 loops=1)Filter: (aid < 100000)Rows Removed by Filter: 1Planning Time: 0.306 msExecution Time: 61.031 ms(8 rows)
您会注意到这里有更多的信息——实际时间和行，以及计划和执行时间。如果我们添加缓冲区，比如EXPLAIN（ANALYZE，BUFFERS），我们甚至可以在输出中获得缓存命中/未命中统计信息：
postgres=# EXPLAIN (BUFFERS, ANALYZE) SELECT * FROM pgbench_accounts a JOIN pgbench_branches b ON (a.bid=b.bid) WHERE a.aid < 100000;QUERY PLAN-------------------------------------------------------------------------------------------------------------Nested Loop (cost=0.00..4141.00 rows=99999 width=461) (actual time=0.039..56.582 rows=99999 loops=1)Join Filter: (a.bid = b.bid)Buffers: shared hit=3 read=1638-> Seq Scan on pgbench_branches b (cost=0.00..1.01 rows=1 width=364) (actual time=0.025..0.026 rows=1 loops=1)Buffers: shared hit=1-> Seq Scan on pgbench_accounts a (cost=0.00..2890.00 rows=99999 width=97) (actual time=0.008..25.752 rows=99999 loops=1)Filter: (aid < 100000)Rows Removed by Filter: 1Buffers: shared hit=2 read=1638Planning Time: 0.306 msExecution Time: 61.031 ms(8 rows)
很快，您会发现EXPLAIN对于希望了解其数据库性能情况的人而言可能是一个有用的工具。
快速回顾扫描和连接类型
重要的是要知道每个连接类型和扫描类型都有它们的时间和位置。有些人在寻找“顺序”扫描这个词，然后立刻在跳回原位，而不考虑是否值得再次访问数据。以一个两行的表为例，当查询规划器可以快速扫描表并在不接触索引的情况下提取数据时，查询规划器将无法扫描索引，然后返回并从磁盘中检索数据。在这种情况下，以及在大多数其他小表的情况下，进行顺序扫描会更有效。要快速查看PostgreSQL使用的联接和扫描类型：
· 扫描方式
1. 顺序扫描
§.基本上是从磁盘强制检索
§. 扫描整张表
§. 对于小表来说非常快
2.索引扫描
§. 扫描索引中的所有/部分行；查找堆中的行
§. 导致随机查找，这对于老式的基于主轴的磁盘而言可能会非常昂贵
§. 为大表提取少量行时，比顺序扫描更快
3.仅索引扫描
§. 扫描索引中的所有/部分行
§. 不需要查找表中的行，因为所需的值已经存储在索引本身中
4. 位图堆扫描
§. 扫描索引，建立要访问的页面位图
§. 然后，仅在表中查找所需行相关页面
· 连接类型
1. 嵌套循环
§. 对于外部表中的每一行，扫描内部表中的匹配行
§. 快速开始，最适合小表
2. 合并连接
§. 对_sorted_数据集进行压缩操作
§. 适合大表
§. 如果需要额外的排序，则启动成本较高
3. 散列联接
§. 构建内部表值的散列，扫描外部表匹配
§. 仅适用于平等条件
§. 启动成本高，但执行速度快
如我们所见，每种扫描类型和连接类型都有其位置。如前所述，最重要的是查询计划程序应具有良好的统计数据。
我们只讨论了一个例子，其中EXPLAIN帮助确定了一个问题，并给出了如何解决它的想法。在EDB支持部门，我们看到了许多情况，EXPLAIN可以帮助识别以下内容：：
· 不准确的统计数据导致错误的连接/扫描选择
· 维护活动（VACUUM和ANALYZE）不够积极
· 损坏的索引需要重建
· 索引定义v.查询不匹配
· work_mem设置得太低，阻止了内存中的排序和连接
· 编写查询时的连接顺序列表，性能较差
· ORM配置不正确
对于使用PostgreSQL的任何人来说，EXPLAIN无疑是最宝贵的工具之一，并且使用得当可以节省大量时间！
