postgresql - 如何在不扫描所有表的索引的情况下通过 id 在 postgres 上的 LIST 分区表中获取元素？

Question

我在 pg major 11 中有以下场景:

DROP TABLE IF EXISTS public.agent_sessions_partitioned;

CREATE TABLE public.agent_sessions_partitioned
            (
                id uuid NOT NULL DEFAULT uuid_generate_v4(),
                account_id uuid,
                user_id uuid
            ) PARTITION BY LIST (account_id);

CREATE TABLE "agent_sessions_bcbc5acc-f020-4073-bdf4-3098bc043e8b"
                  PARTITION OF agent_sessions_partitioned
                  FOR VALUES IN ('bcbc5acc-f020-4073-bdf4-3098bc043e8b'); 

INSERT INTO agent_sessions_partitioned (id, account_id, user_id)
    SELECT agent_sessions.id, account_id, user_id FROM agent_sessions; 

ALTER TABLE "agent_sessions_bcbc5acc-f020-4073-bdf4-3098bc043e8b" ADD PRIMARY KEY (id); 

等等。

当我有这样的查询时，这非常有效:

Select * from agent_sessions_partitioned where account_id = 'XX'

但是因为我使用的是 ORM(Rails - 事件记录)，所以我无法选择始终在语句中使用 account_id 以及每当我需要执行以下操作时:

UPDATE agent_sessions_partitioned set user_id = 'x' where id = 'y'

PG 扫描所有子表试图找到这个元组，见下面的解释分析:

"Append  (cost=0.28..2612.12 rows=355 width=558) (actual time=0.956..277.658 rows=1 
loops=1)"
"  ->  Index Scan using "agent_sessions_a13f3c88-3022-4676-bd48-6580d8877ae2_pkey" on 
"agent_sessions_a13f3c88-3022-4676-bd48-6580d8877ae2"  (cost=0.28..8.30 rows=1 width=500)             
(actual time=0.955..0.956 rows=1 loops=1)"
"        Index Cond: (id = 'b21a0178-f97c-4598-ba39-bf763ba377b5'::uuid)"
"  ->  Index Scan using "agent_sessions_325774d6-e5e7-4fae-9659-8b76349a6c2a_pkey" on 
"agent_sessions_325774d6-e5e7-4fae-9659-8b76349a6c2a"  (cost=0.29..8.30 rows=1 width=481) 
(actual time=0.750..0.750 rows=0 loops=1)"
"        Index Cond: (id = 'b21a0178-f97c-4598-ba39-bf763ba377b5'::uuid)"
"  ->  Index Scan using "agent_sessions_1f781bcd-b941-4915-949a-9af893d8f066_pkey" on 
"agent_sessions_1f781bcd-b941-4915-949a-9af893d8f066"  (cost=0.29..8.30 rows=1 width=507) 
 (actual time=1.523..1.523 rows=0 loops=1)"

由于我没有更改此按 id 更新记录的查询的选项，我可以在 postgres 方面做些什么吗？任何可能对我有帮助的配置或其他类型的分区，甚至升级到 pg 12/13 的版本？

Answer 1

不，除非您以不同方式对表进行分区，否则无法避免这种增加的计划和执行成本。

与普遍看法相反，对表进行分区会降低大多数使用该表的 SQL 语句的速度。只有在极少数情况下，当您可以将顺序扫描限制在分区的子集时，您才会看到性能提升。请注意，大表上的索引扫描并不明显比小表上的索引扫描慢。

相反，您使用分区来提高在批量操作中添加和删除许多表行的速度和灵 active (在 PostgreSQL 中，它还有助于大型表上的自动清理)。对查询的性能影响是您为此付出的代价。

除非您有太多分区(注意!)，否则影响应该不会太差。您可以使用准备好的语句来减少查询的计划时间，例如问题中的查询。