【Postgresql架构】部分索引（ Partial

语言 Chinese, Simplified

SEO Title

How Partial Indexes Affect UPDATE Performance in Postgres

部分索引是节省磁盘空间和提高记录查找性能的好方法。共同的规则是“用它当你能负担得起”——换句话说,如果你所有的查询涉及到一些过滤器,这是通常被认为是一个好主意,以包括过滤索引定义,以减少其规模和改善其性能(指数越小,越快就会执行相应的索引扫描或IndexOnlyScan)。

但是，部分索引是否总是提供更好的性能?好,让我们看看。

举个简单的例子:

create table asset(

id bigserial primary key,

owner_id int8 not null,

name text not null,

price int8

);

假设在这个表格中我们存储了一些资产的信息，让我们用一些样本数据来填充它:

insert into asset(

owner_id,

name,

price

)

select

i,

round(random()*100000)::text,

round(random() * 10000)

from generate_series(1, 600000) _(i);

再加入更多的样本数据，这次的价格“未知”:

insert into asset(
  owner_id,
  name,
  price
)
select
  i,
  round(random()*100000)::text,
  null                                     -- price is unknown (yet)
from generate_series(600001, 1000000) _(i);
vacuum analyze asset;

现在我们有100万条记录，其中40%的价格未知(无效)。

价格会不时变化，因此任何记录都可能被更新为新的、经过修正的价格值。

接下来，考虑这样一种情况:我们需要快速找到属于某个特定用户的所有资产，但我们从不对价格未知的记录感兴趣:

select *

from asset

where

owner_id = :owner_id

and price is not null;

我们需要使用什么索引?我们很自然地认为，这部分指数将完美地满足我们的需要:

create index i_asset_price_partial

on asset

using btree(owner_id)

where price is not null;

但是拥有最好的表现真的有帮助吗?

这得视情况而定。

首先，这样的索引会小得多，即常规的全表索引:

create index i_asset_price_full

on asset

using btree(owner_id);

让我们来比较一下它们的大小:

test=# \x
Expanded display is on.test=# \di+ i_asset_price_partial
-[ RECORD 1 ]----------------------
Schema      | public
Name        | i_asset_price_partial
Type        | index
Owner       | nikolay
Table       | asset
Size        | 13 MB
Description |test=# \di+ i_asset_price_full
-[ RECORD 1 ]--------------
Schema      | public
Name        | i_asset_price_full
Type        | index
Owner       | nikolay
Table       | asset
Size        | 21 MB
Description |

正如预期的那样，全表索引的大小更高——21 MB vs 13 MB，因此比部分索引大60%左右。这是您可以决定的时候—好吧，我最好使用部分索引(就像我在优化的一个Postgres实例中所做的那样)。但是不要着急，等一会儿。

那么SELECT性能呢?让我们用pgbench快速检查一下，这是Postgres的本地基准测试工具:

echo "\set owner_id random(1, 1 * 1000000)" > selects.bench
echo "select from asset where owner_id = :owner_id and price is not null;" >> selects.bench
pgbench -n -T 30 -j 4 -c 12 -M prepared -f selects.bench -r test

在没有索引的情况下，我的笔记本只有13.12 TPS(每秒事务数):

$ pgbench -n -T 30 -j 4 -c 12 -M prepared -r test -f selects.bench

transaction type: selects.bench

scaling factor: 1

query mode: prepared

number of clients: 12

number of threads: 4

duration: 30 s

number of transactions actually processed: 397

latency average = 915.767 ms

tps = 13.103765 (including connections establishing)

tps = 13.113909 (excluding connections establishing)

script statistics:

- statement latencies in milliseconds:

0.003 \set owner_id random(1, 1 * 1000000)

908.755 select from asset where owner_id = :owner_id and price is not null;

连续扫描不适合在干草堆里找针。这里没有惊喜。

全表索引(单独定义，不包含部分索引):

$ pgbench -n -T 30 -j 4 -c 12 -M prepared -r test -f selects.bench

transaction type: selects.bench

scaling factor: 1

query mode: prepared

number of clients: 12

number of threads: 4

duration: 30 s

number of transactions actually processed: 779801

latency average = 0.462 ms

tps = 25963.230818 (including connections establishing)

tps = 25972.470987 (excluding connections establishing)

script statistics:

- statement latencies in milliseconds:

0.001 \set owner_id random(1, 1 * 1000000)

0.460 select from asset where owner_id = :owner_id and price is not null;

和部分索引(再次定义，没有全文):

$ pgbench -n -T 30 -j 4 -c 12 -M prepared -r test -f selects.bench

transaction type: selects.bench

scaling factor: 1

query mode: prepared

number of clients: 12

number of threads: 4

duration: 30 s

number of transactions actually processed: 817490

latency average = 0.440 ms

tps = 27242.705122 (including connections establishing)

tps = 27253.100588 (excluding connections establishing)

script statistics:

- statement latencies in milliseconds:

0.001 \set owner_id random(1, 1 * 1000000)

0.439 select from asset where owner_id = :owner_id and price is not null;

可以看到，使用部分索引的select要比使用全表索引的select快一点(~5%)。这是我们所期望的。

现在让我们看看更新性能的不同。我们将只接触那些已经定义了价格的记录，以保持null(40%)的数量不变，以防我们想要做更多的实验:

echo "\set id random(1, 1 * 600000)" > updates.bench
echo "update asset set price = price + 10 where id = :id;" >> updates.benchpsql test -c 'vacuum full analyze asset;' && \
  psql test -c 'select pg_stat_reset();' >> /dev/null && \
  pgbench -n -T 30 -j 4 -c 12 -M prepared -r test -f updates.bench

当除主键外没有索引时:14553 tps。

全文索引:14371 tps。

部分索引:12198 tps。

这看起来可能令人惊讶——部分索引显著地(约14%)减慢了更新速度!

为什么如此?要回答这个问题，让我们看看Postgres内部统计信息pg_stat_user_tables。注意到我们在运行pgbench进行更新之前调用了pg_stat_reset()函数吗?这是为了重置整个Postgres集群的统计数据并收集新的数据。现在，如果我们用这个查询每次实验后检查这个查询的结果:

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';

我们会看到这样的情况:

-- no indexes except PK
 n_tup_upd | n_tup_hot_upd | hot_ratio
-----------+---------------+-----------
    436808 |        409220 |     93.68-- full-table index
 n_tup_upd | n_tup_hot_upd | hot_ratio
-----------+---------------+-----------
    431473 |        430807 |     99.85-- partial index
 n_tup_upd | n_tup_hot_upd | hot_ratio
-----------+---------------+-----------
    366089 |             0 |      0.00

这些数字很清楚——在部分索引的情况下，我们将完全失去热更新。HOT表示仅堆元组，这是PostgreSQL执行元组更新的内部技术。虽然在PostgreSQL的主文档中没有详细的说明，但是你可以在README中找到详细的解释。来自Postgres源文件的热(或阅读本文:“使用热更新提高更新查询的速度”)。

简而言之,如果在更新期间,新老元组(行你更新的版本)都位于同一页面的内存,这更新不修改任何索引列,特殊类型的优化会发生,这将允许Postgres节省时间,首先,因为它不会改变索引条目。

但是这里的索引列究竟是什么呢?自述文件。热解释: