部分索引(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