翻译自：Just Upgrade: How PostgreSQL 12 Can Improve Your Performance
今天，许多应用程序记录来自传感器、设备、跟踪信息和其他共享一个共同属性的东西的数据：时间戳总是在增加。此时间戳非常有价值，因为它是查找类型、分析查询等的基础。
PostgreSQL 9.5引入了块级索引这一特性，它对于搜索大量数据非常有用，对时序型数据有比B树索引占用更少的磁盘空间的好处。块级索引条目指向页（页是PostgreSQL 存储数据的原子单位）存两个值：页的最小值和被索引的项最大值。
事实上，只要使用的恰当，块级索引不仅比B树索引做的好还会比B树索引节省99%的磁盘空间！
所以，我们该怎样使用块级索引才能帮助磁盘节省空间还能保持应用的性能以及怎样应用到PostgreSQL的分区系统中呢？
设置感应器读取应用
假设我们有个应用每两秒读取感应器的数据，存储它读到值和该值被记录的时间。我们可以用这样的表这样把值存起来：
CREATE TABLE scans (
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
scan float NOT NULL,
created_at timestamptz NOT NULL
);
出于测试的目的，我用了不会生成任何WAL的无日志表，来帮助提升性能。如果你想像我一样用这样的方法，你需要运行下面的语句：
CREATE UNLOGGED TABLE scans (
id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
scan float NOT NULL,
created_at timestamptz NOT NULL
);
我所有的测试都是在我的笔记本上的PostgreSQL 11.2上运行的，我的笔记本电脑有八个可用的内核。我调优过PostgreSQL配置文件。请使用本文中的所有时间作为参考方向。
插入一些数据。对于第一次测试,因为BRIN索引的使用指南是使用更大的数据集，我决定用10,000,000行（好吧，10,000,001行）。我计算出完成测试的数据的范围，使用以下查询生成了10,000,001次扫描数据：
INSERT INTO scans (scan, created_at)
SELECT random(), x
FROM generate_series('2012-01-01 0:00'::timestamptz,
'2018-05-03 20:33:20'::timestamptz, '2 seconds'::interval) x;
SELECT count(*) FROM scans;
count
-----------
100000001
我需要看看对于给出的一个月每天扫描的平均值。我可以用这样的一个查询完成这个任务：
SELECT date_trunc('day', created_at), avg(scan)
FROM scans
WHERE created_at BETWEEN '2017-02-01 0:00' AND '2017-02-28 11:59:59'
GROUP BY 1
ORDER BY 1;
（注意：我知道我也能像这样做：created_at >= ‘2017-02-01’ AND created_at < ‘2017-03-01’ ）
作为一个基础，让我们运行没有使用索引的查询。PostgreSQL会选择全表顺序扫描，如果你启用了并行查询的话，性能可能会让你惊讶。对于第一次测试，我不启用并行查询获取基础值：
SET max_parallel_workers = 0;
EXPLAIN ANALYZE SELECT date_trunc('day', created_at), avg(scan)
FROM scans
WHERE created_at BETWEEN '2017-02-01 0:00' AND '2017-02-28 11:59:59'
GROUP BY 1
ORDER BY 1;
返回了以下的结果：
Finalize GroupAggregate (cost=128919.29..294297.77 rows=1147587 width=16) (actual time=1335.080..1587.770 rows=28 loops=1)
Group Key: (date_trunc('day'::text, created_at))
-> Gather Merge (cost=128919.29..271346.02 rows=1147588 width=40) (actual time=1326.056..1587.715 rows=28 loops=1)
Workers Planned: 4
Workers Launched: 0
-> Partial GroupAggregate (cost=127919.23..133657.17 rows=286897 width=40) (actual time=1325.865..1587.464 rows=28 loops=1)
Group Key: (date_trunc('day'::text, created_at))
-> Sort (cost=127919.23..128636.47 rows=286897 width=16) (actual time=1316.989..1443.944 rows=1188000 loops=1)
Sort Key: (date_trunc('day'::text, created_at))
Sort Method: external merge Disk: 30232kB
-> Parallel Seq Scan on scans (cost=0.00..101911.77 rows=286897 width=16) (actual time=88.985..1026.441 rows=1188000 loops=1)
Filter: ((created_at >= '2017-02-01 00:00:00-05'::timestamp with time zone) AND (created_at <= '2017-02-28 11:59:59-05'::timestamp with time zone))
Rows Removed by Filter: 8812001
Planning Time: 0.098 ms
Execution Time: 1595.763 ms
在我的PostgreSQL环境中这个查询花费了大约1600ms执行。注意 尽管它计划去启用4个并行查询，实际上并没有执行。让我们看一下当我们设置并行查询会发生什么：
SET max_parallel_workers = 8;
EXPLAIN ANALYZE SELECT date_trunc('day', created_at), avg(scan)
FROM scans
WHERE created_at BETWEEN '2017-02-01 0:00' AND '2017-02-28 11:59:59'
GROUP BY 1
ORDER BY 1;
返回结果是这样的：
Finalize GroupAggregate (cost=131287.23..310221.83 rows=1241656 width=16) (actual time=396.036..453.017 rows=28 loops=1)
Group Key: (date_trunc('day'::text, created_at))
-> Gather Merge (cost=131287.23..285388.71 rows=1241656 width=40) (actual time=392.398..458.091 rows=140 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=130287.17..136495.45 rows=310414 width=40) (actual time=385.556..435.999 rows=28 loops=5)
Group Key: (date_trunc('day'::text, created_at))
-> Sort (cost=130287.17..131063.21 rows=310414 width=16) (actual time=383.855..401.129 rows=237600 loops=5)
Sort Key: (date_trunc('day'::text, created_at))
Sort Method: quicksort Memory: 24859kB
Worker 0: Sort Method: quicksort Memory: 16919kB
Worker 1: Sort Method: quicksort Memory: 16852kB
Worker 2: Sort Method: quicksort Memory: 17094kB
Worker 3: Sort Method: quicksort Memory: 16830kB
-> Parallel Seq Scan on scans (cost=0.00..101971.47 rows=310414 width=16) (actual time=31.678..334.686 rows=237600 loops=5)
Filter: ((created_at >= '2017-02-01 00:00:00-05'::timestamp with time zone) AND (created_at <= '2017-02-28 11:59:59-05'::timestamp with time zone))
Rows Removed by Filter: 1762400
Planning Time: 0.128 ms
Execution Time: 459.501 ms
在这个例子中 PostgreSQL决定使用四个并行查询，总体查询时间上性能提升了3.5倍
但是，这篇文章不是关于并行查询的，而是关于BRIN索引的。但是在我们使用BRIN索引之前，让我们使用标准的B树索引来获取另一个基数。让我们创建B树索引，将VACUUM设置激活。
CREATE INDEX scans_created_at_idx ON scans (created_at);
VACUUM FREEZE ANALYZE;
当我们再次运行这个查询时：
EXPLAIN ANALYZE SELECT date_trunc('day', created_at), avg(scan)
FROM scans
WHERE created_at BETWEEN '2017-02-01 0:00' AND '2017-02-28 11:59:59'
GROUP BY 1
ORDER BY 1;
我们得到：
GroupAggregate (cost=170490.94..197720.71 rows=1210212 width=16) (actual time=872.049..1128.159 rows=28 loops=1)
Group Key: (date_trunc('day'::text, created_at))
-> Sort (cost=170490.94..173516.47 rows=1210212 width=16) (actual time=861.351..985.978 rows=1188000 loops=1)
Sort Key: (date_trunc('day'::text, created_at))
Sort Method: external merge Disk: 30232kB
-> Index Scan using scans_created_at_idx on scans (cost=0.43..48218.21 rows=1210212 width=16) (actual time=0.044..561.311 rows=1188000 loops=1)
Index Cond: ((created_at >= '2017-02-01 00:00:00-05'::timestamp with time zone) AND (created_at <= '2017-02-28 11:59:59-05'::timestamp with time zone))
Planning Time: 0.145 ms
Execution Time: 1137.303 ms
这显示了比没有使用并行查询的基数有更好的性能。让我们看一眼索引的大小：
SELECT pg_size_pretty(pg_relation_size('scans_created_at_idx'));
pg_size_pretty
----------------
214 MB
现在，让我们看看当使用BRIN索引时会发生什么。在某列使用BRIN索引，你需要像这样利用 USING brin：
DROP INDEX scans_created_at_idx;
CREATE INDEX scans_created_at_brin_idx ON scans USING brin(created_at);
VACUUM FREEZE ANALYZE;
让我们看看查询性能怎么样。当我们运行：
EXPLAIN ANALYZE SELECT date_trunc('day', created_at), avg(scan)
FROM scans
WHERE created_at BETWEEN '2017-02-01 0:00' AND '2017-02-28 11:59:59'
GROUP BY 1
ORDER BY 1;
我们得到了这样了结果：
GroupAggregate (cost=205442.58..232278.17 rows=1192693 width=16) (actual time=702.798..954.675 rows=28 loops=1)
Group Key: (date_trunc('day'::text, created_at))
-> Sort (cost=205442.58..208424.31 rows=1192693 width=16) (actual time=694.105..813.864 rows=1188000 loops=1)
Sort Key: (date_trunc('day'::text, created_at))
Sort Method: external merge Disk: 30232kB
-> Bitmap Heap Scan on scans (cost=316.09..85065.32 rows=1192693 width=16) (actual time=1.834..423.803 rows=1188000 loops=1)
Recheck Cond: ((created_at >= '2017-02-01 00:00:00-05'::timestamp with time zone) AND (created_at <= '2017-02-28 11:59:59-05'::timestamp with time zone))
Rows Removed by Index Recheck: 17760
Heap Blocks: lossy=7680
-> Bitmap Index Scan on scans_created_at_brin_idx (cost=0.00..17.92 rows=1204833 width=0) (actual time=0.392..0.392 rows=76800 loops=1)
Index Cond: ((created_at >= '2017-02-01 00:00:00-05'::timestamp with time zone) AND (created_at <= '2017-02-28 11:59:59-05'::timestamp with time zone))
Planning Time: 0.143 ms
Execution Time: 967.445 ms
使用BRIN索引，查询性能比没有使用索引的（没有并行查询）快了40%，比使用B树索引快了15%。在这个例子中它仍然比并行顺序扫描慢，但这可能是我们正在查看的数据集的结果。磁盘利用率如何，BRIN索引占用了多少磁盘空间？
SELECT pg_size_pretty(pg_relation_size('scans_created_at_brin_idx'));
pg_size_pretty
----------------
32 kB
是的，BRIN索引仅仅占用了32kb！这意味着同样的数据BRIN索引占用存储B树索引空间的1/100，而且显示了更好的查询性能。
也许有人会问，并行顺序扫描性能这么好为什么我还需要索引呢？好吧，我可以给出两个原因：1）我们的查询 2）数据集可能仍然很小。
让我们稍微地改变该查询。我想查看2017年2月14日每小时的平均扫描值。我会运行这样的查询：
SELECT date_trunc('hour', created_at), avg(scan)
FROM scans
WHERE created_at BETWEEN '2017-02-14 0:00' AND '2017-02-14 11:59:59'
GROUP BY 1
ORDER BY 1;
随着并行查询的启用，我得到了这样的结果：
Finalize GroupAggregate (cost=102558.20..105786.81 rows=22403 width=16) (actual time=337.682..338.889 rows=12 loops=1)
Group Key: (date_trunc('hour'::text, created_at))
-> Gather Merge (cost=102558.20..105338.75 rows=22404 width=40) (actual time=337.611..349.192 rows=60 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=101558.14..101670.16 rows=5601 width=40) (actual time=330.144..331.137 rows=12 loops=5)
Group Key: (date_trunc('hour'::text, created_at))
-> Sort (cost=101558.14..101572.14 rows=5601 width=16) (actual time=330.029..330.453 rows=4320 loops=5)
Sort Key: (date_trunc('hour'::text, created_at))
Sort Method: quicksort Memory: 251kB
Worker 0: Sort Method: quicksort Memory: 251kB
Worker 1: Sort Method: quicksort Memory: 435kB
Worker 2: Sort Method: quicksort Memory: 391kB
Worker 3: Sort Method: quicksort Memory: 454kB
-> Parallel Seq Scan on scans (cost=0.00..101209.43 rows=5601 width=16) (actual time=55.838..328.854 rows=4320 loops=5)
Filter: ((created_at >= '2017-02-14 00:00:00-05'::timestamp with time zone) AND (created_at <= '2017-02-14 11:59:59-05'::timestamp with time zone))
Rows Removed by Filter: 1995680
Planning Time: 0.115 ms
Execution Time: 349.280 ms
当用BRIN索引的时候，我得到了：
GroupAggregate (cost=60909.70..61385.39 rows=21142 width=16) (actual time=13.448..16.902 rows=12 loops=1)
Group Key: (date_trunc('hour'::text, created_at))
-> Sort (cost=60909.70..60962.55 rows=21142 width=16) (actual time=13.081..14.367 rows=21600 loops=1)
Sort Key: (date_trunc('hour'::text, created_at))
Sort Method: quicksort Memory: 1781kB
-> Bitmap Heap Scan on scans (cost=21.35..59390.88 rows=21142 width=16) (actual time=1.534..9.303 rows=21600 loops=1)
Recheck Cond: ((created_at >= '2017-02-14 00:00:00-05'::timestamp with time zone) AND (created_at <= '2017-02-14 11:59:59-05'::timestamp with time zone))
Rows Removed by Index Recheck: 18592
Heap Blocks: lossy=256
-> Bitmap Index Scan on scans_created_at_brin_idx (cost=0.00..16.06 rows=40160 width=0) (actual time=0.123..0.123 rows=2560 loops=1)
Index Cond: ((created_at >= '2017-02-14 00:00:00-05'::timestamp with time zone) AND (created_at <= '2017-02-14 11:59:59-05'::timestamp with time zone))
Planning Time: 0.110 ms
Execution Time: 16.982 ms
在这个例子中，使用BRIN索引比使用并行顺序扫描快了大约20倍。现在让我们看看一些大的多的数据。
BERIN 与B-Tree 进行一亿行数据的查询比较
让我们将数据集增加10倍，并使用一亿行。之后删除和新建我的扫描表，我用这样的查询填充数据：
INSERT INTO scans (scan, created_at)
SELECT random(), x
FROM generate_series('2012-01-01 0:00'::timestamptz,
'2018-05-03 20:33:20'::timestamptz, '2 seconds'::interval) x;
产生了100,000,001行数据：
SELECT count(*) FROM scans;
count
-----------
100000001
我们会使用原始的查询进行下一步的实验，举个例子，查找2017年2月的每一天的平均扫描，即
SELECT date_trunc('day', created_at), avg(scan)
FROM scans
WHERE created_at BETWEEN '2017-02-01 0:00' AND '2017-02-28 11:59:59'
GROUP BY 1
ORDER BY 1;
这是不使用并行查询输出的基础数
Finalize GroupAggregate (cost=906718.36..1086472.33 rows=1240794 width=16) (actual time=9787.788..10042.382 rows=28 loops=1)
Group Key: (date_trunc('day'::text, created_at))
-> Gather Merge (cost=906718.36..1061656.45 rows=1240794 width=40) (actual time=9777.030..10043.054 rows=28 loops=1)
Workers Planned: 6
Workers Launched: 0
-> Partial GroupAggregate (cost=905718.27..909854.25 rows=206799 width=40) (actual time=9776.767..10041.978 rows=28 loops=1)
Group Key: (date_trunc('day'::text, created_at))
-> Sort (cost=905718.27..906235.26 rows=206799 width=16) (actual time=9765.630..9888.880 rows=1188000 loops=1)
Sort Key: (date_trunc('day'::text, created_at))
Sort Method: external merge Disk: 30232kB
-> Parallel Seq Scan on scans (cost=0.00..887460.12 rows=206799 width=16) (actual time=7496.431..9365.666 rows=1188000 loops=1)
Filter: ((created_at >= '2017-02-01 00:00:00-05'::timestamp with time zone) AND (created_at <= '2017-02-28 11:59:59-05'::timestamp with time zone))
Rows Removed by Filter: 98812001
Planning Time: 3.534 ms
Execution Time: 10051.513 ms
这是开启并行查询的：
Finalize GroupAggregate (cost=906718.36..1086472.33 rows=1240794 width=16) (actual time=2547.817..2604.061 rows=28 loops=1)
Group Key: (date_trunc('day'::text, created_at))
-> Gather Merge (cost=906718.36..1061656.45 rows=1240794 width=40) (actual time=2545.522..2615.073 rows=196 loops=1)
Workers Planned: 6
Workers Launched: 6
-> Partial GroupAggregate (cost=905718.27..909854.25 rows=206799 width=40) (actual time=2526.779..2575.461 rows=28 loops=7)
Group Key: (date_trunc('day'::text, created_at))
-> Sort (cost=905718.27..906235.26 rows=206799 width=16) (actual time=2525.088..2541.112 rows=169714 loops=7)
Sort Key: (date_trunc('day'::text, created_at))
Sort Method: quicksort Memory: 14277kB
Worker 0: Sort Method: quicksort Memory: 13945kB
Worker 1: Sort Method: quicksort Memory: 14225kB
Worker 2: Sort Method: quicksort Memory: 13945kB
Worker 3: Sort Method: quicksort Memory: 14094kB
Worker 4: Sort Method: quicksort Memory: 14372kB
Worker 5: Sort Method: quicksort Memory: 13840kB
-> Parallel Seq Scan on scans (cost=0.00..887460.12 rows=206799 width=16) (actual time=1910.448..2474.151 rows=169714 loops=7)
Filter: ((created_at >= '2017-02-01 00:00:00-05'::timestamp with time zone) AND (created_at <= '2017-02-28 11:59:59-05'::timestamp with time zone))
Rows Removed by Filter: 14116000
Planning Time: 0.138 ms
Execution Time: 2615.837 ms
在没有索引的情况下，并行顺序扫描确实有帮助。让我们创建一个B树索引，这个时间显示了创建索引花费的时间，并准备运行下一次查询：
\timing
CREATE INDEX scans_created_at_idx ON scans (created_at);
Time: 34434.702 ms (00:34.435)
VACUUM FREEZE ANALYZE;
让我们来看看运行这个查询时会发生什么：
EXPLAIN ANALYZE SELECT date_trunc('day', created_at), avg(scan)
FROM scans
WHERE created_at BETWEEN '2017-02-01 0:00' AND '2017-02-28 11:59:59'
GROUP BY 1
ORDER BY 1;
我们得到了以下结果：
GroupAggregate (cost=175102.37..203032.61 rows=1241344 width=16) (actual time=854.269..1118.307 rows=28 loops=1)
Group Key: (date_trunc('day'::text, created_at))
-> Sort (cost=175102.37..178205.73 rows=1241344 width=16) (actual time=845.117..971.251 rows=1188000 loops=1)
Sort Key: (date_trunc('day'::text, created_at))
Sort Method: external merge Disk: 30232kB
-> Index Scan using scans_created_at_idx on scans (cost=0.57..49456.81 rows=1241344 width=16) (actual time=0.020..531.775 rows=1188000 loops=1)
Index Cond: ((created_at >= '2017-02-01 00:00:00-05'::timestamp with time zone) AND (created_at <= '2017-02-28 11:59:59-05'::timestamp with time zone))
Planning Time: 0.094 ms
Execution Time: 1126.357 ms
这一亿行数据，B树索引帮助这个查询比并行顺序扫描有更出色的性能表现。它占用了多少磁盘空间？
SELECT pg_size_pretty(pg_relation_size('scans_created_at_idx'));
pg_size_pretty
----------------
2142 MB
B树索引占用了超过2GB的磁盘空间。让我们看看这与使用BRIN索引的情况，从空间利用和性能两方面。首先，让我们设置BRIN索引，确保这次也对其创建进行计时：
DROP INDEX scans_created_at_idx;
CREATE INDEX scans_created_at_brin_idx ON scans USING brin(created_at);
Time: 18396.309 ms (00:18.396)
VACUUM FREEZE ANALYZE;
BRIN索引比B树索引创建节省了很多的时间。由于我的测试环境运行在PostgreSQL 11上，我得到了并行化创建B树索引的好处,所以你能真正看到BRIN索引创建的效率。现在，让我们看看查询性能怎么样。当我运行：
EXPLAIN ANALYZE SELECT date_trunc('day', created_at), avg(scan)
FROM scans
WHERE created_at BETWEEN '2017-02-01 0:00' AND '2017-02-28 11:59:59'
GROUP BY 1
ORDER BY 1;
我的输出是：
GroupAggregate (cost=785242.87..810191.48 rows=1108827 width=16) (actual time=703.571..968.501 rows=28 loops=1)
Group Key: (date_trunc('day'::text, created_at))
-> Sort (cost=785242.87..788014.94 rows=1108827 width=16) (actual time=693.621..821.642 rows=1188000 loops=1)
Sort Key: (date_trunc('day'::text, created_at))
Sort Method: external merge Disk: 30232kB
-> Bitmap Heap Scan on scans (cost=362.00..673913.30 rows=1108827 width=16) (actual time=1.424..416.475 rows=1188000 loops=1)
Recheck Cond: ((created_at >= '2017-02-01 00:00:00-05'::timestamp with time zone) AND (created_at <= '2017-02-28 11:59:59-05'::timestamp with time zone))
Rows Removed by Index Recheck: 17760
Heap Blocks: lossy=7680
-> Bitmap Index Scan on scans_created_at_brin_idx (cost=0.00..84.79 rows=1125176 width=0) (actual time=1.146..1.146 rows=76800 loops=1)
Index Cond: ((created_at >= '2017-02-01 00:00:00-05'::timestamp with time zone) AND (created_at <= '2017-02-28 11:59:59-05'::timestamp with time zone))
Planning Time: 0.111 ms
Execution Time: 975.262 ms
BRIN索引帮助这个查询显示了比B树索引和并行顺序扫描更好的性能。那么磁盘占用大小呢？
SELECT pg_size_pretty(pg_relation_size('scans_created_at_brin_idx'));
pg_size_pretty
----------------
184 kB
是的，当我第一次看到这个结果的时候，我也重复看了一遍。BRIN索引仅仅占用了18KB而B树索引却超过2GB！而且BRIN索引的查询性能还比B树索引高出大约15%，哇！
这是否意味着BRIN索引是万能的？
答案是：也许。首先，你可能已经观察到了：当你的表增长到相当大的大小时你会真正开始看到BRIN索引的好处。它还演示了PostgreSQL的垂直伸缩的能力：BRIN索引能确切地帮助你针对你尝试去解决的很多问题高效地运行查询，特别是时间分析方面的。
最大的好处是在存储空间上：如果你的数据集允许你使用BRIN索引的话，它能将索引占用空间减少99%以上。
那对于分区又是怎样的呢？PostgreSQL 11引进了对于内部分区系统的改进支持，了解分区怎样影响你的索引选择也是有趣的。但是这个话题是本系列下篇文章的主题。
139
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
