postgresql12 b-tree v4空间上和性能上的优化

在 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 重新索引。

我来评几句
登录后评论

已发表评论数()

相关站点

热门文章