【PostgreSQL】部分索引、覆盖索引和多列索引是如何降低update操作的

部分索引(partial indexes)

部分索引(partial indexes)可以提升查询性能且节省磁盘空间。(索引越小，对应的索引扫描越快)

下面是PostgreSQL文档中的例子：

create index orders_unbilled_index
on orders (order_nr)
where billed is not true;

如果我们要优化的查询包含billed列，优化器就会考虑该索引。并且过滤针对表中总行数的一小部分，我们应该看到通过相应索引扫描的显着性能改进。部分索引比较小，只是索引我们需要的行。

多列、覆盖索引(Covering indexes)、仅索引扫描(Index-only Scans)

索引不仅可以用来定位我们需要的记录。也可以用与读取数据，以供使用。这可以通过借助多列索引、或从PostgreSQL11引入的覆盖索引。覆盖索引(Covering indexes)包含额外的列的值，但是这些值在B树索引中没有被当作键。

首选是使用多列索引来包含列的值：

create index i_myindex
on table mytable
using btree(col1, col2);

其次还是使用覆盖索引：

create index i_myindex
on table mytable
using btree(col1)
include(col2);

如果，我们的select语句涉及的列都在索引中，可以使用仅索引扫描。这可能比索引扫描更快，因为不用再回表。在这种场景下，我们需要做的是调优autovacuum，使得表有个“好形状”，比如没有膨胀、碎片等。

覆盖索引(Covering indexes)在唯一性索引和唯一性约束的场景尤其有用。如果，我们有唯一性索引，且我们想增加一列，我们不能只是简单地增加列，因为可能会破坏唯一性约束。但是，我们可以通过关键字include来添加列。

覆盖索引(Covering indexes)的另一个好处是，相比多列索引，修改“额外”列的值更轻量、更快。

实验

DB Schema

create table asset(
  id bigserial primary key,
  owner_id int8 not null,
  created_at timestamptz default now(),
  c1 int8,
  c2 int8,
  c3 int8,
  price int8
);

这是一张资产表，插入一些记录：

insert into asset(owner_id,c1,c2,c3,price)
select
  round(random() * 10000),
  round(random() * 1000000), -- c1
  round(random() * 1000000),
  round(random() * 1000000),
  round(random() * 10000000) -- price
from generate_series(1, 600000) i;

插入更多的记录，只是price列是空值：

insert into asset(owner_id,c1,c2,c3,price)
select
  round(random() * 10000),
  round(random() * 1000000), -- c1
  round(random() * 1000000),
  round(random() * 1000000),
  null                                      -- price is unknown (yet)
from generate_series(600001, 1000000) i;

create index on asset(c1);
create index on asset(c2);
create index on asset(c3);

现在有一百万条记录，其中40%的记录的price列是null。

我们在三个int8类型的列c1、c2、c3上创建了索引。在后面我们会看到，三个索引使得常规的update操作变得更加昂贵。这并非是什么罕见的场景，俗称“写放大”。

测试负载

我们使用三种类型的查询。

假设我们需要快速地找到属于某个owner的所有资产的价钱：

select sum(price)
from asset
where owner_id = :owner_id;

此外，我们还会执行以下查询：

select sum(price)
from asset
where
  owner_id = :owner_id
  and price is not null;

从语义上讲，它等同于前面的查询，因为 sum(..) 不考虑 NULL 值。

但是，正如我们将看到的，原始查询不能使用where price is not null的部分索引。理论上，PostgreSQL计划器可以针对这种情况进行适当的优化，但目前还没有。

最后，对于update操作。资产的price会不时变化，因此任何记录都可能会被更新以具有新的、修正的price值。我们将使用以下查询作为示例（请原谅它非常悲观，价格一直在上涨）：

update asset
set price = price + 10
where id = :id;

我们使用PostgreSQL标准的基准测试工具pgbench来生成负载：

echo "=== ### Benchmark scripts ==="
echo "\\set owner_id random(1, 1000)" > selects.bench
echo "select sum(price) from asset where owner_id = :owner_id;" >> selects.bench

echo "\\set owner_id random(1, 1000)" > selects_not_null.bench
echo "select sum(price) from asset where owner_id = :owner_id and price is not null;" >> selects_not_null.bench

echo "\\set id random(1, 600000)" > updates.bench
echo "update asset set price = price + 10 where id = :id;" >> updates.bench

for test in selects selects_not_null updates; do
  pgbench test --no-vacuum \
    --report-latencies --time=30 --progress=10 \
    --jobs=4 --client=12 \
    --protocol=prepared \
    --file="${test}.bench"
done

索引

使用什么样的索引可以加快查询呢？以下是DBA通常会想到的：

·update操作应该已经很快，我们通过主键进行检索，因为我们需要根据id找到对应的行。所以我们应该只是想着优化select。

·为了找到属于某个用户的所有记录(where owner_id = :owner_id)，我们应该在owner_id上创建一个索引

·如果我们考虑对select查询使用额外的过滤条件(... and price is not null),就会很自然地想到使用部分索引应该可以加速索引扫描的速度(create index ... where price is not null)。这个想法值得验证一下。

·为了避免回表，可以在索引中加入price列。有两种方式来实现：使用多列索引(owner_id,price)、或使用覆盖索引(covering index)借助关键字include。无论使用哪种方法，列price都被包含在索引中了。不用回表后，最好的情况就是，使用仅索引覆盖，但要求所有的页都被标记为可见的(visible)。否则还是要检查表中恰当的记录条目：对应explain(analyze,buffers)执行计划中的heap fetches: xx。要想保持回表fetch的次数少，可能需要调优autovacuum，不过这不是本次讨论的话题。

考虑到这些因素，我们准备好为我们的基准系列提供索引列表：

1.没有额外的索引。我们也来检查一下，只是为了出于完整性。

2.一个列的索引

create index i_1column
on asset
using btree(owner_id);

3.多列索引

create index i_2columns
on asset
using btree(owner_id, price);

4.部分索引(Partial index)

create index i_partial
on asset
using btree(owner_id)
where price is not null;

5.覆盖索引(Covering index)

create index i_covering
on asset
using btree(owner_id)
include (price);

6.最后，部分覆盖索引(partial covering index)

create index i_partial_covering
on asset
using btree(owner_id)
include (price)
where price is not null;

我们真正需要的

首先，我们真正感兴趣的是三个查询的表现，pgbench提供了延迟和tps的数量。在这些实验中，我们的目标是尽可能多地使用资源，所以我们的首要度量是实现TPS。

其次，我们感兴趣的是查看Postgres计划器在每种情况下为所有三个查询选择了哪些计划：我们将使用简单的 explain <query>；– 没有实际执行，因为我们只对结构感兴趣。请注意，选择的计划可能会因使用的值而异——我们将在UPDATE查询中使用 1来表示id和在SELECT查询中使用owner_id，假设计划对于所有其他值都是相同的，因为它们应该是均匀的分布，在我们合成的情况下。

最后，我们感兴趣的是想知道索引的大小，在我们30秒的update之后会如何变化。这里我们两次使用psql，一次在update之前、一次在update之后：

\di+ i_*

此外，还有更重要的一件事。检查一下有多少updates是"HOT"的：

select
  n_tup_upd,
  n_tup_hot_upd,
  round(100 * n_tup_hot_upd::numeric / n_tup_upd, 2) as hot_ratio
from pg_stat_user_tables
where relname = 'asset';

HOT表示heap only tuples。这是PostgreSQL的一项内部技术，文档中没有很详细的说明，感兴趣可以参考：

·https://github.com/postgres/postgres/blob/master/src/backend/access/heap/README.HOT

·https://www.cybertec-postgresql.com/en/hot-updates-in-postgresql-for-better-performance/

·https://www.interdb.jp/pg/pgsql07.html

·https://habr.com/en/company/postgrespro/blog/483768/

结果

这是实验的结果总结

为我们分析的查询选择什么版本的索引？看这张照片，我可能会保留1列索引。它对SELECT查询表现出相当好的性能(但不是最优),它对UPDATE非常快，并且它在这些 UPDATE下索引不会膨胀。

当然，对于不同类型的查询和数据，情况可能会有所不同。在某些情况下，我可能想要选择覆盖索引或2列索引——如果我需要更好的SELECT速度并且不太关心UPDATE。

总结

·关键的总结：如果我们在索引定义中包含一个列，那么更新该列中的值就不能再HOT，这可能会使update显著变慢。对于owner_id上具有1列索引的情况，大多数 (~97%) 对price的UPDATE都是HOT的。但是一旦price成为索引定义的一部分，所有的更新都不再是HOT。这是非常明显的：TPS值从~48k下降到~28-30k。所以我们有~40%的降级。降级的程度取决于HOT和常规UPDATE之间的差异有多大：我们在表上拥有的索引越多，这种差异就越大。

·在多列或覆盖索引中使用列时，人们可能很容易预料到这种效果会发生(尽管仍然很容易忘记它)。如果我们create index的时候在where加入了列定义，对该列updates就不能使用HOT，因此就会变慢。使用部分索引优化SELECT而不检查UPDATE查询的行为，我们肯定会打破“Primum non nocere”规则。

·众所周知，创建的任何新索引都会稍微减慢写入操作(UPDATE、DELETE、INSERT)—但这种下降通常比我们在这里观察到的要小得多(事实上，没有任何额外的索引，相比只有1列的索引，也甚至会稍微降低UPDATE查询的TPS：46k与48k)。

·结果证明，覆盖索引的TPS比两列索引要大一点(30 MiB和21 MiB)。这是因为直到PostgreSQL13,才加入b树索引去重功能。

https://postgres.ai/blog/20211029-how-partial-and-covering-indexes-affect-update-performance-in-postgresql