在 pg v11 和 v12 上 常见测试用例
CREATE TABLE rel ( a bigint NOT NULL, b bigint NOT NULL ); ALTER TABLE rel ADD CONSTRAINT rel_pkey PRIMARY KEY (a, b); CREATE INDEX rel_b_idx ON rel (b); \d rel Table "public.rel" Column | Type | Collation | Nullable | Default --------+--------+-----------+----------+--------- a | bigint | | not null | b | bigint | | not null | Indexes: "rel_pkey" PRIMARY KEY, btree (a, b) "rel_b_idx" btree (b)
- 它确保“a”和“b” 两字段的每种组合最多有一个条目。
- 它可以加快与给定“b”相关的所有“a”的搜索速度。
加入测试数据
INSERT INTO rel (a, b) SELECT i, i / 10000 FROM generate_series(1, 20000000) AS i; /* 收集统计信息 */ VACUUM (ANALYZE) rel;
B树索引提高1：插入很多重复的索引和数值
当我们比较的b列索引的大小的第一个区别是显而易见的：
v11:
\di+ rel_b_idx
List of relations
Schema | Name | Type | Owner | Table | Size | Description
--------+-------------+-------+----------+-------+--------+-------------
public | rel_b_idx | index | postgres | rel | 545 MB |
(1 row)
v12:
\di+ rel_b_idx
Schema | Name | Type | Owner | Table | Size | Description
--------+-------------+-------+----------+-------+--------+-------------
public | rel_b_idx | index | postgres | rel | 408 MB |
(1 row)
v11 比 v12 还要大 33%
每一个b列在index发生10000次，因此会有很多叶子节点的所有密钥是相同的（每个叶子节点可以包含几百项）。
V12之前，叶子页必须是分立的，有时是最右边的叶子节点，但有时不是。最右边的叶子节点总是朝着右端，
以优化单调递增插入拆分。与此相反，其他叶子节点是在中间，其中浪费的空间分割。
与V12，该表的行的物理地址（“元组ID”或TID）是索引关键字的一部分，所以重复的索引条目存储在表的顺序。
这会造成这样的条目索引扫描访问的物理顺序表，它可以是一个显著的性能优势，特别是在机械磁盘。
换句话说，重复索引条目的相关性将是完美的。而且，仅由重复的页将在右端分裂，产生密集索引。
加入类似的优化多列索引，但它并不适用于我们的主键索引，因为重复不是在第1列。
主键索引在V11和V12紧凑，因为第一列是单调递增的，所以叶页拆分在最右边的页面总是发生。
PostgreSQL的已经有针对的优化。
B树索引提高2：内部索引页面的压缩存储
对于主键索引的改进是不那么明显，因为它们几乎在尺寸在V11和V12相同。我们必须更深入的挖掘这里。
首先，观察指标，只有在这两个V11和V12（块缓存）扫描：
在v11:
EXPLAIN (ANALYZE, BUFFERS, COSTS off, SUMMARY off, TIMING off)
S
SELECT a, b FROM rel
W
WHERE a = 420024 AND b = 42;
QUERY PLAN
-
---------------------------------------------------------------
Index Only Scan using rel_pkey on rel (actual rows=1 loops=1)
Index Cond: ((a = 420024) AND (b = 42))
Heap Fetches: 0
Buffers: shared hit=5
(
(4 rows)
在v12:
EXPLAIN (ANALYZE, BUFFERS, COSTS off, SUMMARY off, TIMING off)
S
SELECT a, b FROM rel
W
WHERE a = 420024 AND b = 42;
QUERY PLAN
-
---------------------------------------------------------------
Index Only Scan using rel_pkey on rel (actual rows=1 loops=1)
Index Cond: ((a = 420024) AND (b = 42))
Heap Fetches: 0
Buffers: shared hit=4
(
(4 rows)
在v12中，将读取少一（索引）的块，这意味着该索引少一级。
由于索引的大小几乎相同，因此必须意味着内部页面可以容纳更多的索引条目。
在v12中，索引具有更大的扇出度。
如上所述，PostgreSQL的V12引入的TID作为索引关键字，这会浪费在内部索引页的空间过多量的一部分。
所以同一个commit引入的来自内部 Page “冗余”索引属性。该TID是多余的，
因为是从包含子句非键属性（V11这些也从内部索引页除去）。
不过，PostgreSQL的V12也可以截断不需要的表行识别这些指标的属性。
在我们的主键索引，出价是一个冗余列，并从内部索引页，
从而节省了8个字节的每个索引条目空间。让我们一起来看看与pageinspect扩展内部索引页：
在 v11:
SELECT * FROM bt_page_items('rel_pkey', 2550);
itemoffset | ctid | itemlen | nulls | vars | data
-
------------+------------+---------+-------+------+-------------------------------------------------
1 | (2667,88) | 24 | f | f | cd 8f 0a 00 00 00 00 00 45 00 00 00 00 00 00 00
2 | (2462,0) | 8 | f | f |
3 | (2463,15) | 24 | f | f | d6 c0 09 00 00 00 00 00 3f 00 00 00 00 00 00 00
4 | (2464,91) | 24 | f | f | db c1 09 00 00 00 00 00 3f 00 00 00 00 00 00 00
5 | (2465,167) | 24 | f | f | e0 c2 09 00 00 00 00 00 3f 00 00 00 00 00 00 00
6 | (2466,58) | 24 | f | f | e5 c3 09 00 00 00 00 00 3f 00 00 00 00 00 00 00
7 | (2467,134) | 24 | f | f | ea c4 09 00 00 00 00 00 40 00 00 00 00 00 00 00
8 | (2468,25) | 24 | f | f | ef c5 09 00 00 00 00 00 40 00 00 00 00 00 00 00
9 | (2469,101) | 24 | f | f | f4 c6 09 00 00 00 00 00 40 00 00 00 00 00 00 00
10 | (2470,177) | 24 | f | f | f9 c7 09 00 00 00 00 00 40 00 00 00 00 00 00 00
.
...
205 | (2666,12) | 24 | f | f | c8 8e 0a 00 00 00 00 00 45 00 00 00 00 00 00 00
(
(205 rows)
在数据输入我们所看到的援助和出价字节。该实验在 little-endian 机器上进行的，
所以在第6行的数目将是0x09C3E5和0x3F的或（十进制数）639973和63.每个索引条目是24个字节宽，这8个字节是所述元组报头。
在 v12:
SELECT * FROM bt_page_items('rel_pkey', 2700);
itemoffset | ctid | itemlen | nulls | vars | data
-
------------+----------+---------+-------+------+-------------------------
1 | (2862,1) | 16 | f | f | ab 59 0b 00 00 00 00 00
2 | (2576,0) | 8 | f | f |
3 | (2577,1) | 16 | f | f | 1f 38 0a 00 00 00 00 00
4 | (2578,1) | 16 | f | f | 24 39 0a 00 00 00 00 00
5 | (2579,1) | 16 | f | f | 29 3a 0a 00 00 00 00 00
6 | (2580,1) | 16 | f | f | 2e 3b 0a 00 00 00 00 00
7 | (2581,1) | 16 | f | f | 33 3c 0a 00 00 00 00 00
8 | (2582,1) | 16 | f | f | 38 3d 0a 00 00 00 00 00
9 | (2583,1) | 16 | f | f | 3d 3e 0a 00 00 00 00 00
10 | (2584,1) | 16 | f | f | 42 3f 0a 00 00 00 00 00
.
...
286 | (2861,1) | 16 | f | f | a6 58 0b 00 00 00 00 00
(
(286 rows)
该数据仅包含a列，因为a列已经被截断了。这减少了索引项的大小为16，让更多的条目适合索引页上。
升级注意事项
由于索引存储在V12被改变，新的B-tree索引第4版已经推出。
由于与pg_upgrade不改变数据文件升级，索引仍然会在3.0版本升级后。
PostgreSQL的V12可以使用这些指标，但上述的优化将不可用。
你需要重新索引的索引将其升级到4.0版本（这已经在PostgreSQL的V12变得更加容易与REINDEX兼）。
其他B-tree索引功能在推出V12
有PostgreSQL中V12添加了一些其他方面的改进。如下简单列表：
1. 减少B树索引插入，以提高性能锁定开销。
2. REINDEX CONCURRENTLY，重建无停机时间的索引。
3. 完善与许多属性的索引仅索引扫描性能。
4. 添加视图 pg_stat_progress_create_index 报到CREATE INDEX和REINDEX进展。
总结
拥有许多重复的条目索引, V12 更有优势 , 推荐 pg_upgrade后用 REINDEX CONCURRENTLY 重新索引。
