一、概述
几年前，有人提议在 PostgreSQL 中为分区表添加全局索引支持。在该提案之后，进行了多次讨论，并且还有一个初始版本的POC来展示可能性、技术挑战和潜在的好处等。但是，全局索引功能在 PostgreSQL 官方版本中仍然不可用。这个博客试图分享一种不同的方法，并希望它可以被实施以使一些用户受益。
2. 基本的全局索引要求
让这个全局索引在 Postgres 分区表上可用的原因有很多，例如跨多个分区的只读查询的性能增强，使用非分区键作为跨多个分区的索引的唯一性等。例如，如果用户发现表增长太快，他们必须在应用程序使用 key2 检索数据时根据 key1 将该表拆分为分区。在这种情况下，使用可用的全局索引，他们可能会避免对应用程序进行不必要的更改。
3. 不同的方法
为了解决这个global索引要求，PostgreSQL 社区讨论中的一个 POC 试图将全局索引存储在单个索引关系中。这样一来，由于所有的索引元组都存储在一个索引关系中，它肯定会有更好的性能。但是，一个问题是它会遇到一个关系文件的物理大小限制。另一个问题是每个分离都需要清理这个单一的全局索引关系，这有点违反了原始分区表的设计思想。正如前面提到的，分区表的设计思想之一是to cheaply add and remove partitions.
另一种方法是，我们可以考虑将global索引关系基于分区键单独存储，并添加逻辑以允许全局访问global对非分区键具有唯一性限制的分离索引关系。在这种方法中，我们保留了原始分区表设计的好处。一是global索引关系单独存储的一种关系的大小限制，二是易于维护分离性能。对于附加分区，它将取决于它是空表还是带有数据甚至索引的表。
这种方法的主要思想是通过去除必须涉及分区键的限制来使用现有功能，并在索引构建期间添加逻辑来处理非分区键的全局唯一性检查和跨分区排序。
4. 一些初步测试
在第二种方法的基础上做了一些基本的改动，这里有一些简单的测试结果分享一下。
4.1。设置分区
首先，使用 pgbench 创建 12 个分区，加载规模为 1000 的数据。
$ pgbench -i -s 1000 --partitions=12 --partition-method=range -d postgres4.2. 设置global索引
其次，创建global没有分区键（aid）限制的索引，但对非分区键（bid）进行全局唯一性检查。
postgres=# create unique index gidx on pgbench_accounts using btree(bid) global;global这是创建索引后架构的样子。
postgres=# \d+ pgbench_accounts
Partitioned table "public.pgbench_accounts"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------+---------------+-----------+----------+---------+----------+-------------+--------------+-------------
aid | integer | | not null | | plain | | |
bid | integer | | | | plain | | |
abalance | integer | | | | plain | | |
filler | character(84) | | | | extended | | |
Partition key: RANGE (aid)
Indexes:
"pgbench_accounts_pkey" PRIMARY KEY, btree (aid)
"gidx" UNIQUE, btree (bid)
Partitions: pgbench_accounts_1 FOR VALUES FROM (MINVALUE) TO (8333335),
pgbench_accounts_10 FOR VALUES FROM (75000007) TO (83333341),
pgbench_accounts_11 FOR VALUES FROM (83333341) TO (91666675),
pgbench_accounts_12 FOR VALUES FROM (91666675) TO (MAXVALUE),
pgbench_accounts_2 FOR VALUES FROM (8333335) TO (16666669),
...为了区分global索引关系和正常的索引关系，这里我们使用g替换i。
postgres=# select oid, relname, relnamespace, reltype, reloftype, relam, relfilenode, relpages, reltuples, relhasindex, relkind from pg_class where relnamespace=2200 order by oid;
oid | relname | relnamespace | reltype | reloftype | relam | relfilenode | relpages | reltuples | relhasindex | relkind
-------+-----------------------------+--------------+---------+-----------+-------+-------------+----------+-----------+-------------+---------
16690 | gidx | 2200 | 0 | 0 | 403 | 0 | 0 | 0 | f | I
16691 | pgbench_accounts_1_bid_idx | 2200 | 0 | 0 | 403 | 16691 | 22852 | 8.333334e+06 | f | g
16692 | pgbench_accounts_2_bid_idx | 2200 | 0 | 0 | 403 | 16692 | 22852 | 8.333334e+06 | f | g
16693 | pgbench_accounts_3_bid_idx | 2200 | 0 | 0 | 403 | 16693 | 22852 | 8.333334e+06 | f | g
...4.3. 使用global索引查询
现在，让我们使用非分区键（bid）运行一个简单的查询来比较性能
postgres=# select * from pgbench_accounts where bid=75000007;
aid | bid | abalance | filler
----------+----------+----------+--------------------------------------------------------------------------------------
75000007 | 75000007 | 0 |
(1 row)
Time: 2.243 ms
postgres=# explain select * from pgbench_accounts where bid=75000007;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Append (cost=0.43..101.46 rows=12 width=97)
-> Index Scan using pgbench_accounts_1_bid_idx on pgbench_accounts_1 (cost=0.43..8.45 rows=1 width=97)
Index Cond: (bid = 75000007)
-> Index Scan using pgbench_accounts_2_bid_idx on pgbench_accounts_2 (cost=0.43..8.45 rows=1 width=97)
Index Cond: (bid = 75000007)
...正如上面的解释所示，已经使用了索引扫描。
4.4. 无索引查询
然后，让我们删除全局索引，再次运行相同的查询，
postgres=# drop index gidx;
DROP INDEX
postgres=# select * from pgbench_accounts where bid=75000007;
aid | bid | abalance | filler
----------+----------+----------+--------------------------------------------------------------------------------------
75000007 | 75000007 | 0 |
(1 row)
Time: 8345.590 ms (00:08.346)
postgres=# explain select * from pgbench_accounts where bid=75000007;
QUERY PLAN
----------------------------------------------------------------------------------------------
Gather (cost=1000.00..2161189.59 rows=12 width=97)
Workers Planned: 2
-> Parallel Append (cost=0.00..2160188.39 rows=12 width=97)
-> Parallel Seq Scan on pgbench_accounts_1 (cost=0.00..180015.78 rows=1 width=97)
Filter: (bid = 75000007)
-> Parallel Seq Scan on pgbench_accounts_2 (cost=0.00..180015.78 rows=1 width=97)
Filter: (bid = 75000007)
...在这里，我们可以看到有和没有全局索引之间的巨大性能差异（2.243 毫秒与 8345.590 毫秒）
4.5. 使用具有分区键限制的索引进行查询
现在，让我们用分区键（辅助）建立一个索引，
postgres=# create unique index lidx on pgbench_accounts using btree(aid, bid);
postgres=# create unique index lidx on pgbench_accounts using btree(aid, bid);
CREATE INDEX
postgres=# select * from pgbench_accounts where bid=75000007;
aid | bid | abalance | filler
----------+----------+----------+--------------------------------------------------------------------------------------
75000007 | 75000007 | 0 |
(1 row)
Time: 3312.177 ms (00:03.312)
postgres=# explain select * from pgbench_accounts where bid=75000007;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Append (cost=0.43..1846949.37 rows=12 width=97)
-> Index Scan using pgbench_accounts_1_aid_bid_idx on pgbench_accounts_1 (cost=0.43..153912.45 rows=1 width=97)
Index Cond: (bid = 75000007)
-> Index Scan using pgbench_accounts_2_aid_bid_idx on pgbench_accounts_2 (cost=0.43..153912.45 rows=1 width=97)
Index Cond: (bid = 75000007)global使用相同的查询，我们仍然可以看到索引和具有分区键限制的原始索引之间存在很大差异（2.243 ms vs. 3312.177 ms）。
4.6. 非分区键的唯一性
下面的例子试图证明，如果没有索引提供的对非分区键的唯一性检查，global就无法阻止重复的投标记录插入。
postgres=# insert into pgbench_accounts values(100000001, 75000007, 0, '');
INSERT 0 1
postgres=# select * from pgbench_accounts where bid=75000007;
aid | bid | abalance | filler
-----------+----------+----------+--------------------------------------------------------------------------------------
75000007 | 75000007 | 0 |
100000001 | 75000007 | 0 |
(2 rows)但是，通过索引提供的对非分区键的唯一性检查，global可以检测并阻止重复投标记录的插入。
postgres=# 插入 pgbench_accounts 值(100000001, 75000007, 0, ”);
错误：重复的键值违反了唯一约束“pgbench_accounts_10_bid_idx”
详细信息：键（投标）=（75000007）已经存在。
五、总结
在这篇博客中，我解释了一种实现逻辑global索引特性但保持物理存储分开的不同方法，这可以潜在地保留 PostgreSQL 上原有的分区表设计思想，并展示了查询性能和非分区键唯一性检查的好处.
原文标题：Global Index, a different approach
原文作者： David Zhang
原文地址：https://www.highgo.ca/2022/10/14/global-index-a-different-approach/
