PostgreSQL不能始终如一地使用部分索引

Question

我有一个postgres 13.3表，看起来像这样:

CREATE TABLE public.enrollments (
id bigint NOT NULL,
portfolio_id bigint NOT NULL,
consumer_id character varying(255) NOT NULL,
identity_id character varying(255) NOT NULL,
deleted_at timestamp(0) without time zone,
batch_replace boolean DEFAULT false NOT NULL
);
CREATE UNIQUE INDEX enrollments_portfolio_id_consumer_id_index ON public.enrollments 
USING btree (portfolio_id, consumer_id) WHERE (deleted_at IS NULL);

每个投资组合通常包含数百万个注册。我的客户通常会定期向我发送一个批处理文件，其中包含他们所有的注册，因此我必须使数据库与该文件匹配。我尝试一次读取大约1000个块，然后查询以检查注册是否预先存在，例如以下查询:

SELECT * FROM enrollments WHERE deleted_at IS NULL AND portfolio_id = 1 
AND consumer_id = ANY(ARRAY["C1", "C2", ..., "C1000"])

看来,新组合,它不使用独特的部分指数这查询需要30秒。当投资组合中已经有数以百万计的注册时，该指数似乎起作用，大约需要20毫秒。我不得不将sql更改为每次只查询一个注册，这大约需要1秒/1000。这并不理想，因为完成一个文件可能需要花费一天的时间，但至少它完成了。

有人知道我能做些什么来获得唯一的部分索引一致使用时，使用许多consumer_id在选择?

下面是一些解释输出。这个冗长的查询花费了4秒多一点的时间，随着越来越多的注册被插入到投资组合中，这个时间增加到至少30秒，直到它达到某个点并下降到大约20毫秒

Existing enrollments in this portfolio: 78140485
Index Scan using enrollments_portfolio_id_consumer_id_index on enrollments e0  (cost=0.70..8637.14 rows=1344 width=75) (actual time=3.529..37.827 rows=1000 loops=1)
Index Cond: ((portfolio_id = '59031'::bigint) AND ((consumer_id)::text = ANY ('{C1,C2,...,C1000}'::text[])))
I/O Timings: read=27.280
Planning Time: 0.477 ms
Execution Time: 37.914 ms
Benchmark time: 20 ms

Existing enrollments in this portfolio: 136000
Index Scan using enrollments_portfolio_id_consumer_id_index on enrollments e0  (cost=0.70..8.87 rows=1 width=75) (actual time=76.615..4354.081 rows=1000 loops=1)
Index Cond: (portfolio_id = '59028'::bigint)
Filter: ((consumer_id)::text = ANY ('{C1,C2,...,C1000}'::text[]))
Rows Removed by Filter: 135000
Planning Time: 1.188 ms
Execution Time: 4354.341 ms
Benchmark time: 4398 ms

Answer 1

这里的事情实际上是缓慢的是，=ANY是通过循环在你的数组的1000个成员和测试每一个来实现的，并为每136000行它需要检查。这是很多循环(但在我手中不是4秒钟的价值，只是)。我是1.5美元)。更糟糕的是，计划者没有预料到=ANY的实现如此糟糕，因此没有理由选择其他计划来避免它。

v14将通过使用哈希表来实现=ANY来修复这个问题，因此它将不再如此缓慢。

如果您不能或不想升级到v14，您可以通过加入VALUES列表来重写查询，而不是使用=ANY

SELECT * FROM enrollments join (VALUES ('C1'),...,('C1000')) f(c) on c=consumer_id
WHERE deleted_at IS NULL AND portfolio_id = 1