执行计划解决-part2
上一次我们介绍了 EXPLAIN 输出所展示的内容。今天，我们更深入地聊一聊你在执行计划中可能遇到的各种类型的节点或操作。
最基本的一种是 Seq Scan。
它看起来是这样的：
explain analyze select * from pg_class;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=202) (actual time=0.009..0.049 rows=295 loops=1)
Total runtime: 0.249 ms
(2 rows)
这是最简单的操作，数据库打开表文件，逐行读取数据，并将这些行逐个返回给用户，或返回给执行计划树中的上层节点（例如 LIMIT节点），如下所示：
explain analyze select * from pg_class limit 2;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..0.07 rows=2 width=202) (actual time=0.014..0.014 rows=2 loops=1)
-> Seq Scan on pg_class (cost=0.00..10.92 rows=292 width=202) (actual time=0.009..0.009 rows=2 loops=1)
Total runtime: 0.132 ms
(3 rows)
重要的是要理解，返回的行顺序并没有特定的规律。它既不是“按插入顺序”，也不是“最近更新的在前”或其他任何固定顺序。并发的查询、更新、删除和 VACUUM 操作都可能随时改变行的物理存储顺序，从而影响返回的顺序。
顺序扫描Seq Scan可以对行进行过滤，即拒绝返回某些不符合条件的行。例如，当你添加了WHERE 子句时，就会发生这种情况：
explain analyze select * from pg_class where relname ~ 'a';
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Seq Scan on pg_class (cost=0.00..11.65 rows=227 width=202) (actual time=0.030..0.294 rows=229 loops=1)
Filter: (relname ~ 'a'::text)
Rows Removed by Filter: 66
Total runtime: 0.379 ms
(4 rows)
可以看到我们有“Filter：信息”。还有“Filter 移除的行”这一行。
接下来是索引扫描节点类型。
这种扫描方式看起来非常直接，大多数人至少在一种情况下能明白它的使用场景：
explain analyze select * from pg_class where oid = 1247;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Index Scan using pg_class_oid_index on pg_class (cost=0.15..8.17 rows=1 width=202) (actual time=0.007..0.007 rows=1 loops=1)
Index Cond: (oid = 1247::oid)
Total runtime: 0.077 ms
(3 rows)
那就是，我们有一个匹配条件的索引，所以数据库会：
- 打开索引
- 在索引中查找可能满足给定条件的行在表数据中的位置
- 打开表
- 获取索引指向的行
- 如果这些行可以被返回——即它们对当前会话是可见的——那么它们就会被返回。
一行数据如何变得不可见？这可能是由于已删除但尚未被清理（未执行 VACUUM）的行。或者已被更新。或者已插入，但时间在当前事务之后。
当您希望使用索引对数据进行排序时，也会使用 Index Scan。就像这里一样：
explain analyze select * from pg_class order by oid limit 10;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.15..1.67 rows=10 width=206) (actual time=0.017..0.029 rows=10 loops=1)
-> Index Scan using pg_class_oid_index on pg_class (cost=0.15..44.53 rows=292 width=206) (actual time=0.014..0.026 rows=10 loops=1)
Total runtime: 0.145 ms
(3 rows)
这里没有where条件，但我们可以轻松添加条件，如下：
explain analyze select * from pg_class where oid > 1247 order by oid limit 10;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.15..4.03 rows=10 width=206) (actual time=0.021..0.035 rows=10 loops=1)
-> Index Scan using pg_class_oid_index on pg_class (cost=0.15..37.84 rows=97 width=206) (actual time=0.017..0.031 rows=10 loops=1)
Index Cond: (oid > 1247::oid)
Total runtime: 0.132 ms
(4 rows)
在这些情况下，数据库会在索引中找到起始点（要么是第一个大于 1247 的行，要么就是索引中的最小值），然后依次返回后续的行或值，直到满足 LIMIT 条件为止。
有一种索引扫描的版本，称为反向索引扫描——它执行相同的操作，但用于降序扫描：
explain analyze select * from pg_class where oid < 1247 order by oid desc limit 10;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.15..4.03 rows=10 width=206) (actual time=0.012..0.026 rows=10 loops=1)
-> Index Scan Backward using pg_class_oid_index on pg_class (cost=0.15..37.84 rows=97 width=206) (actual time=0.009..0.022 rows=10 loops=1)
Index Cond: (oid < 1247::oid)
Total runtime: 0.119 ms
(4 rows)
这是同一种操作——打开索引，对于索引指向的每一行，从表中获取行，只是它不是从小到大而是从大到小。
让我们创建一个简单的表：
create table test (id serial primary key, i int4);
CREATE TABLE
insert into test (i) select random() * 1000000000 from generate_series(1,100000);
INSERT 0 100000
vacuum analyze test;
VACUUM
表数据像这样：
select * from test limit 10;
id | i
----+-----------
1 | 546119592
2 | 253476978
3 | 235791031
4 | 654694043
5 | 187647296
6 | 709050245
7 | 210316749
8 | 348927354
9 | 120463097
10 | 5611946
(10 rows)
在这里，我对 id 建立了索引：
\d test
Table "public.test"
Column | Type | Modifiers
--------+---------+---------------------------------------------------
id | integer | not null default nextval('test_id_seq'::regclass)
i | integer |
Indexes:
"test_pkey" PRIMARY KEY, btree (id)
所以，如果满足某些条件（稍后会详细说明），我可以得到这样的计划：
explain analyze select id from test order by id asc limit 10;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.29..0.55 rows=10 width=4) (actual time=0.039..0.042 rows=10 loops=1)
-> Index Only Scan using test_pkey on test (cost=0.29..2604.29 rows=100000 width=4) (actual time=0.036..0.038 rows=10 loops=1)
Heap Fetches: 0
Total runtime: 0.092 ms
(4 rows)
请注意Index Only Scan中的Only一词。
这意味着数据库意识到，所查询的只是索引中包含的数据（列）。因此，它可能不需要再去查看表文件中的任何内容，而是直接从索引中返回所需的数据。
这比普通的索引扫描快得多，因为它们不需要在表数据中验证任何内容。
问题在于，为了使索引仅扫描（Index Only Scan）能够正常工作，索引必须包含这样的信息：相关行所在的页面“最近”没有发生过任何修改。这意味着，要想有效利用索引仅扫描，你的表必须经过良好的 VACUUM 处理。不过，只要启用了自动清理（autovacuum），这通常不会成为大问题。
最后一种表扫描是所谓的位图索引扫描
它看起来是这样的：
#在 test (i)上创建索引 i1
# 查看执行计划
explain analyze select * from test where i < 100000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=4.37..39.99 rows=10 width=8) (actual time=0.025..0.110 rows=13 loops=1)
Recheck Cond: (i < 100000)
-> Bitmap Index Scan on i1 (cost=0.00..4.37 rows=10 width=0) (actual time=0.013..0.013 rows=13 loops=1)
Index Cond: (i < 100000)
Total runtime: 0.154 ms
(5 rows)
位图扫描始终涉及（至少）两个节点。首先是位图索引扫描，然后是位图堆扫描。
它是如何工作的？
假设你的表有 100,000 个页面（大约为 780MB）。位图索引扫描（Bitmap Index Scan）会创建一个位图，其中为表中的每个页面分配一个比特位。在这种情况下，我们将得到一个大小约为 100,000 比特的内存块，即约 12.5KB。这些比特位初始值都为 0。随后，位图索引扫描会根据哪些页面可能包含需要返回的行，将对应的比特位设置为 1。
这个阶段完全不涉及表数据，只访问索引。当该阶段完成时——也就是所有可能包含目标行的页面都被标记后，这个位图会被传递给上层节点——位图堆扫描（Bitmap Heap Scan），由它以更接近顺序的方式读取这些被标记的页面。
这种操作的意义何在呢？普通的索引扫描（Index Scan）会导致随机 I/O——也就是说，磁盘上的数据页以随机的方式被读取。而在机械硬盘（旋转磁盘）上，这种随机读取速度较慢。
顺序扫描在获取单页时更快，但另一方面——你并不总是需要所有页面。
位图索引扫描（Bitmap Index Scan）适用于这样两种情况：你需要从表中获取大量行，但不是全部；并且这些要返回的行并不集中在同一个数据块中（例如，如果我执行的是 … where id < …，就可能跨越多个块）。
位图扫描还有一个有趣的特点：它可以将两个操作、即两个索引扫描的结果合并起来。例如下面这个例子：
explain analyze select * from test where i < 5000000 or i > 950000000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=107.36..630.60 rows=5323 width=8) (actual time=1.023..4.353 rows=5386 loops=1)
Recheck Cond: ((i < 5000000) OR (i > 950000000))
-> BitmapOr (cost=107.36..107.36 rows=5349 width=0) (actual time=0.922..0.922 rows=0 loops=1)
-> Bitmap Index Scan on i1 (cost=0.00..12.25 rows=527 width=0) (actual time=0.120..0.120 rows=491 loops=1)
Index Cond: (i < 5000000)
-> Bitmap Index Scan on i1 (cost=0.00..92.46 rows=4822 width=0) (actual time=0.799..0.799 rows=4895 loops=1)
Index Cond: (i > 950000000)
Total runtime: 4.765 ms
(8 rows)
在这里，我们看到两个位图索引扫描（Bitmap Index Scan）——这样的扫描可以有多个——然后通过 BitmapOr 将它们的结果进行合并（注意：这里的合并不是 SQL 中的JOIN操作）。
正如你所记得的，位图索引扫描（Bitmap Index Scan）的输出是一个位图——即一个包含若干 0 和 1 的内存块。当存在多个这样的位图时，就可以很方便地对其执行逻辑运算：如“或”（OR）、“与”（AND）或“非”（NOT）。
在这里我们可以看到，两个这样的位图通过“或”（OR）操作符进行了合并，生成的新位图随后被传递给位图堆扫描（Bitmap Heap Scan），由它从表中加载相应匹配的行数据。
虽然在这里两个索引扫描都使用了同一个索引，但这并不总是情况。例如，让我们快速添加一些更多列：
alter table test add column j int4 default random() * 1000000000;
ALTER TABLE
alter table test add column h int4 default random() * 1000000000;
ALTER TABLE
create index i2 on test (j);
CREATE INDEX
create index i3 on test (h);
CREATE INDEX
现在：
explain analyze select * from test where j < 50000000 and i < 50000000 and h > 950000000;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=280.76..323.61 rows=12 width=16) (actual time=2.295..2.352 rows=11 loops=1)
Recheck Cond: ((h > 950000000) AND (j < 50000000) AND (i < 50000000))
-> BitmapAnd (cost=280.76..280.76 rows=12 width=0) (actual time=2.278..2.278 rows=0 loops=1)
-> Bitmap Index Scan on i3 (cost=0.00..92.53 rows=4832 width=0) (actual time=0.546..0.546 rows=4938 loops=1)
Index Cond: (h > 950000000)
-> Bitmap Index Scan on i2 (cost=0.00..93.76 rows=4996 width=0) (actual time=0.783..0.783 rows=5021 loops=1)
Index Cond: (j < 50000000)
-> Bitmap Index Scan on i1 (cost=0.00..93.96 rows=5022 width=0) (actual time=0.798..0.798 rows=4998 loops=1)
Index Cond: (i < 50000000)
Total runtime: 2.428 ms
(10 rows)
三个位图索引扫描，每个使用不同的索引，位图通过与位运算连接，结果输入到位图堆扫描。
如果你好奇为什么 BitmapAnd 显示Actual Rows = 0，原因很简单：这个节点根本不处理具体的行数据，它只处理磁盘页面的位图。因此，它本身不会返回任何行。
目前关于表扫描的内容就到这里——这些就是你从磁盘获取数据的可能方式。下次我将介绍如何将多个数据源进行连接，以及其他类型的执行计划。
