执行计划解决-part1
当要排查SQL执行性能问题时，可能我们听到的第一件事就是explain analyze，下面是一个explain的输出结果样例：
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Sort (cost=146.63..148.65 rows=808 width=138) (actual time=55.009..55.012 rows=71 loops=1)
Sort Key: n.nspname, p.proname, (pg_get_function_arguments(p.oid))
Sort Method: quicksort Memory: 43kB
-> Hash Join (cost=1.14..107.61 rows=808 width=138) (actual time=42.495..54.854 rows=71 loops=1)
Hash Cond: (p.pronamespace = n.oid)
-> Seq Scan on pg_proc p (cost=0.00..89.30 rows=808 width=78) (actual time=0.052..53.465 rows=2402 loops=1)
Filter: pg_function_is_visible(oid)
-> Hash (cost=1.09..1.09 rows=4 width=68) (actual time=0.011..0.011 rows=4 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on pg_namespace n (cost=0.00..1.09 rows=4 width=68) (actual time=0.005..0.007 rows=4 loops=1)
Filter: ((nspname <> 'pg_catalog'::name) AND (nspname <> 'information_schema'::name))
这些到底代表什么意思呢？
如果直接以上述的explain的输出来解决，可能不太现实。我们从一些简单的东西开始，在开始之前，我们应该知道数据库保存了一些元信息，比如行数、不同值的数量、最常见的值等等。对于大表，这些是基于随机样本的。
现在。让我们看一个简单的查询，它的explain：
$ explain select * from test where i = 1;
QUERY PLAN
------------------------------------------------------
Seq Scan on test (cost=0.00..40.00 rows=12 width=4)
Filter: (i = 1)
(2 rows)
查询特别简单。
在 explain 中第一行，以及所有以->开头的行都是操作。其他行是关于上述操作的附加信息。
在这个例子中，只有一个操作：对表test 进行顺序扫描，也就是全表扫描。这里还有一个Filter的信息。
顺序扫描意味着数据库将打开表数据，并读取所有数据，可能会过滤（删除）行，但通常会准备好读取并返回整个表。
Seq Scan行告诉我们，我们正在以顺序模式扫描表。并且该表名为test。
那些数字代表什么意思呢？
现在我们有这样一个表：
Table "public.t"
Column | Type | Modifiers
-------------+---------+------------------------------------------------
id | integer | not null default nextval('t_id_seq'::regclass)
some_column | integer |
something | text |
Indexes:
"t_pkey" PRIMARY KEY, btree (id)
"q" btree (some_column)
根据表定义，我们做一个如下的查询：
SELECT * FROM t where some_column = 123;
你认为运行查询的最佳方式是什么？顺序扫描表，还是使用索引？
如果你说：当然使用索引，这个列上有索引，所以它会更快——我可能会问：如果表只有一行，且该行有 some_column = 123 这种情况怎么办？
实际情况是：如果要进行顺序扫描，我只需要从表中读取一个页面（8192 字节），就能获取到行。而如果要使用索引，我必须先从索引中读取页面，检查它以确定表中是否有匹配条件的行，然后才从表中读取页面。
使用索引工作量是两倍！
你可能会说这是非常小的表，所以速度并不重要。那我们想象一个有 100 亿行的表，其中所有行的 some_column 都等于 123。这里索引完全帮不上忙，实际上它会让情况变得更加糟糕。
当然如果表有一百万行数据，其中只有一行有 some_column = 123，使用索引扫描显然会更好。
所以，无法判断给定的查询是否会使用索引，甚至无法确定它是否应该使用索引（我指的是一般情况）——你需要了解更多信息。这引出了一个简单的事实：根据不同的情况，获取数据的方式会有好坏之分。
数据库(在一定限度内）会检查所有可能的计划。它知道表有多少行数据，也知道有多少行数据（很可能）会匹配条件，因此它能够做出相当明智的决策。
但决策是如何做出的？这正是 explain 中第一组数字所显示的内容。它是成本(cost)。
成本不是以时间为单位的估算，它的单位是顺序获取单页。涉及时间和资源使用情况。
在数据库的postgresql.conf文件中，你可能会看到这些参数：
seq_page_cost = 1.0 # measured on an arbitrary scale random_page_cost = 4.0 # same scale as above cpu_tuple_cost = 0.01 # same scale as above cpu_index_tuple_cost = 0.005 # same scale as above cpu_operator_cost = 0.0025 # same scale as above
因此，我们甚至可以调整读取顺序页面的成本。这些参数规定了数据在执行同一查询的不同方法时所假设的代价。
For example, let’s make a simple 1000 row table, with some texts, and index:
例如，我们创建一个简单的有1000行数据的表，并且有索引。
create table test (id serial primary key, some_text text);
CREATE TABLE
insert into test (some_text) select 'whatever' from generate_series(1,1000);
INSERT 0 1000
现在，我们可以看到，对 id 条件运行的 explain 显示如下：
explain select * from test where id = 50;
QUERY PLAN
-----------------------------------------------------------------------
Index Scan using test_pkey on test (cost=0.28..8.29 rows=1 width=36)
Index Cond: (id = 50)
(2 rows)
如果我们告诉数据库在任何情况下都不能使用索引扫描会怎样呢？
set enable_indexscan = false;
explain select * from test where id = 50;
QUERY PLAN
------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=4.28..8.30 rows=1 width=13)
Recheck Cond: (id = 50)
-> Bitmap Index Scan on test_pkey (cost=0.00..4.28 rows=1 width=0)
Index Cond: (id = 50)
(4 rows)
并且我们也禁用下面这个:
set enable_bitmapscan = false;
explain select * from test where id = 50;
QUERY PLAN
------------------------------------------------------
Seq Scan on test (cost=0.00..18.50 rows=1 width=13)
Filter: (id = 50)
(2 rows)
在继续之前，请注意我们使用了 SET 命令来禁用数据库处理查询的某些方式。前面我们禁用了两个。还有更多这样的设置，你可以使用以下命令查看所有设置：
select name, setting, short_desc || coalesce(E'\n' || extra_desc, '')
from pg_settings where name ~ '^enable_';
这些参数可以用来测试各种想法，这些是用于开发/测试的，不是用于生产的。
我们再回到前面的部分:
Index Scan using test_pkey on test (cost=0.28..8.29 rows=1 width=36) Bitmap Heap Scan on test (cost=4.28..8.30 rows=1 width=13) Seq Scan on test (cost=0.00..18.50 rows=1 width=13)
默认情况下，数据库使用 Index Scan。在这种情况下这是最便宜的。总成本为 8.29，而 bitmap heap scan会是 8.30，seq scan 则是 18.5。
好的，但成本显示两个数字：数字…数字。这是怎么回事，为什么我们只在谈论第二个数字？如果我们首先考虑第一个数字，那么顺序扫描是赢家，因为它那里是 0（零），而索引扫描是 0.28，位图堆扫描是 4.28。
所以，范围（数字…数字）是因为它显示了开始操作行的成本以及获取所有行的成本（通过全部，我指的是由这个操作返回的全部，而不是表中全部）。
启动成本是多少呢？对于顺序扫描（seq scan）来说，起始成本为零，只需读取页面并返回行。但以数据排序为例，在返回第一行数据之前，你必须先读取全部数据，并实际完成排序操作。这一点在下面的执行计划（explain）中可以清楚地看到：
QUERY PLAN
-------------------------------------------------------------------
Sort (cost=22.88..23.61 rows=292 width=202)
Sort Key: relfilenode
-> Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=202)
(3 rows)
请注意，Sort 的启动成本为 22.88，而总成本仅为 23.61。因此，从 Sort 中返回行在成本方面微不足道，但排序它们则不然。
接下来执行计划（explain）中的信息是rows（行数）。这是数据库对该操作预计能够返回多少行数据的估算（实际返回的行数可能更少，例如在使用 LIMIT的情况下）。这个估算对于某些操作也很重要，比如表连接（join）。例如，将两个各含 20 行的表进行连接时，可以采用多种方式实现，具体方法并不重要；但当你连接一个包含 100 万行的表和一个包含 10 亿行的表时，所采用的连接方式就变得非常关键了。
这个数字当然可能被误估——原因很多。有时这无关紧要，有时则很重要。但我们会稍后再谈论误估的问题。
最后一点信息是宽度。这是数据库关于从给定操作返回的单行中平均有多少字节的看法。例如：
explain select * from pg_class;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=202)
(1 row)
explain select relname, relkind from pg_class;
QUERY PLAN
------------------------------------------------------------
Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=65)
(1 row)
如上所见，限制修改字段的数量，进而限制了需要通过查询执行传递的总数据量。
接下来是信息中最重要的一点。树。上层节点需要从下层节点获取数据。
让我们看下面这个执行计划：
For example:
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Sort (cost=146.63..148.65 rows=808 width=138) (actual time=55.009..55.012 rows=71 loops=1)
Sort Key: n.nspname, p.proname, (pg_get_function_arguments(p.oid))
Sort Method: quicksort Memory: 43kB
-> Hash Join (cost=1.14..107.61 rows=808 width=138) (actual time=42.495..54.854 rows=71 loops=1)
Hash Cond: (p.pronamespace = n.oid)
-> Seq Scan on pg_proc p (cost=0.00..89.30 rows=808 width=78) (actual time=0.052..53.465 rows=2402 loops=1)
Filter: pg_function_is_visible(oid)
-> Hash (cost=1.09..1.09 rows=4 width=68) (actual time=0.011..0.011 rows=4 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Seq Scan on pg_namespace n (cost=0.00..1.09 rows=4 width=68) (actual time=0.005..0.007 rows=4 loops=1)
Filter: ((nspname <> 'pg_catalog'::name) AND (nspname <> 'information_schema'::name))
这里有五种操作：排序、哈希连接、顺序扫描、哈希和顺序扫描。数据库执行最上面的一项——排序。排序会执行直接下方的一项（哈希连接），并从它们那里获取数据。哈希连接为了返回数据给排序，必须运行顺序扫描（在 pg_proc 上）和哈希。而最终的哈希操作，为了能够返回数据，必须对 pg_namespace 运行顺序扫描。
关键是要理解，有些操作可以立即返回数据，甚至更重要的是，能够逐步地返回数据，例如顺序扫描（Seq Scan）；而另一些操作则不能。例如，在这里我们看到，哈希操作的启动成本与其子操作“顺序扫描”获取“所有行”的成本相同。这意味着，哈希操作要想开始执行（更准确地说，要想能够返回哪怕一行数据），就必须先将其子操作中的所有行全部读取完毕。
开始编写函数时，关于逐渐返回行的部分变得非常重要。让我们考虑这样的函数：
CREATE OR REPLACE FUNCTION public.test()
RETURNS SETOF integer
LANGUAGE plpgsql
AS $function$
declare
i int4;
begin
for i in 1..3 loop
return next i;
perform pg_sleep(1);
end loop;
return;
end;
$function$;
如果你不理解，不用担心。该函数返回 3 行，每行包含一个整数——1、2 和 3。不过重要的是，它在返回每一行后会暂停 1 秒钟。
这意味着如果我做：
select * from test() limit 1;
我可以预期等待 3 秒钟才能获得结果。
让我们看看:
\timing
Timing is on.
select * from test() limit 1;
test
------
(1 row)
Time: 3005.334 ms
同样的 3 秒。为什么？因为 PL/pgSQL不能返回部分结果。看起来它可以使用return next，但所有这些都被存储在缓冲区中，并在函数执行结束时一起返回。
另一方面,正常操作通常可以返回部分数据。可以通过像 seq scan这样简单的东西在非简单表上看到这一点：
create table t as
select i as id,
repeat('depesz', 100)::text as payload
from generate_series(1,1000000) i;
有了这张表，我们可以看到：
explain analyze select * from t;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..185834.82 rows=10250082 width=36) (actual time=0.015..232.380 rows=1000000 loops=1)
Total runtime: 269.666 ms
(2 rows)
explain analyze select * from t limit 1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..0.02 rows=1 width=36) (actual time=0.003..0.003 rows=1 loops=1)
-> Seq Scan on t (cost=0.00..185834.82 rows=10250082 width=36) (actual time=0.003..0.003 rows=1 loops=1)
Total runtime: 0.016 ms
(3 rows)
我们看看总运行时间。
如所见，一旦它满足了 Limit 对正好 1 行的需求，seq scan 非常快的结束了。
同时请注意，在这里，即使成本（这并不是比较查询的最佳指标）也显示，顶层节点（第一个查询中的 seq scan，第二个查询中的 limit）在返回所有行时具有非常不同的值：85834.82 与 0.02。
所以，对于任何给定操作的前 4 个数字（成本加上行数和宽度各两个）都是估计值。它们可能是正确的，但也可能不正确。
运行EXPLAIN ANALYZE query时得到的另外 4 个数字，才会显示实际情况。
时间同样是一个范围，但这次是真实的时间。它表示数据库实际花费在特定操作上的时间（平均值，因为该操作可能执行了多次）。与成本类似，时间也是一个范围：包括启动时间（startup time）和返回所有数据所需的时间。让我们来看一下这个执行计划：
$ explain analyze select * from t limit 100;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..9.33 rows=100 width=608) (actual time=0.008..0.152 rows=100 loops=1)
-> Seq Scan on t (cost=0.00..93333.86 rows=999986 width=608) (actual time=0.007..0.133 rows=100 loops=1)
Total runtime: 0.181 ms
(3 rows)
如您所见，Limit 的启动时间为 0.008（毫秒，这是这里的单位）。这是因为 Seq Scan（Limit 调用以获取数据）花费了 0.007 毫秒来返回第一行，然后 Limit 内部还有 0.001 毫秒的处理时间。
之后（返回第一行后），限制不断从 Seq Scan 获取数据，直到获取到 100 行。然后它终止了 Seq Scan（在查询开始后 0.133 毫秒发生），并在另外 0.019 毫秒后完成。
实际行数值，正如其名称所示，显示了此操作返回了多少行（平均而言）。而循环则显示了此操作总共运行了多少次。
在什么情况下一个操作会被调用多次呢？例如，在某些类型的连接（join）或子查询中就可能发生。就像下面这个执行计划所展示的情况一样。
Nested Loop (cost=0.00..3.17 rows=1 width=8) (actual time=0.316..0.797 rows=2 loops=1) -> Seq Scan on s (cost=0.00..1.07 rows=1 width=4) (actual time=0.072..0.141 rows=2 loops=1) Filter: (id = 7) Rows Removed by Filter: 4 -> Seq Scan on s2 (cost=0.00..2.09 rows=1 width=4) (actual time=0.128..0.160 rows=1 loops=2) Filter: (id = 7) Rows Removed by Filter: 86 Total runtime: 1.115 ms
请注意第三个操作中的循环是 2。这意味着这个 Seq Scan 被执行了两次，平均返回 1 行，并且平均耗时 0.160ms 完成。所以这个特定操作所花费的总时间是 2 * 0.160ms = 0.32ms。
查询性能差通常是因为它必须多次遍历某些内容。就像这里一样。
Nested Loop (cost=0.00..10715.90 rows=26284 width=4449) (actual time=0.054..291.131 rows=26284 loops=1) -> Index Scan using books_index_title on books (cost=0.00..3306.28 rows=26284 width=3357) (actual time=0.033..50.773 rows=26284 loops=1) -> Index Scan using categories_pkey on categories (cost=0.00..0.27 rows=1 width=1092) (actual time=0.002..0.003 rows=1 loops=26284) Index Cond: (categories.id = books.category_id) Total runtime: 312.212 ms
在上面的例子中，虽然操作 3 的实际执行时间仅为 0.003 毫秒，但该操作被执行了超过 26000 次，导致在此处花费的总时间接近 79 毫秒。
我认为，以上内容已经涵盖了阅读执行计划（EXPLAIN）所需的基本理论知识。你可能仍然不太理解各个操作或其他信息的具体含义，但至少你现在明白了这些数字代表什么，以及 EXPLAIN 和 EXPLAIN ANALYZE 之间的区别：
EXPLAIN显示的是以抽象单位表示的成本，这些成本基于随机抽样的估算；- 而
EXPLAIN ANALYZE显示的是真实执行时间、实际返回的行数以及操作的执行次数，使用的是可以跨不同查询进行比较的实际单位。
后续计划涵盖更多关于：
- 各种操作是什么，它们如何工作，以及当你看到它们在
explain输出中时可以期待什么 - 统计信息是什么，数据库如何获取它们，如何查看它们，以及如何充分利用它们
