主页 / dba / 问题 / 308492
Accepted
Markus Döring
Asked: 2022-03-10 06:49:42 +0800 CST

在 PostgreSQL 中 ATTACH PARTITION 时并非所有检查约束都使用

  • 772

我有一个verbatim由整数列 dataset_key 分区的表，它也是复合主键的一部分：

\d+ verbatim_default
                     Partitioned table "public.verbatim_default"
   Column    |   Type   | Collation | Nullable |  Default                                                               
-------------+----------+-----------+----------+-----------
 id          | integer  |           | not null | 
 dataset_key | integer  |           | not null | 
Partition key: LIST (dataset_key)
Indexes:
    "verbatim_pkey" PRIMARY KEY, btree (dataset_key, id)
Partitions: verbatim_2049 FOR VALUES IN (2049),
            verbatim_2064 FOR VALUES IN (2064),
            verbatim_2066 FOR VALUES IN (2066),
            verbatim_3 FOR VALUES IN (3),
            verbatim_default DEFAULT, PARTITIONED

有一个 verbatim_default 分区可以捕获任何分区中未明确提及的所有数据集键。这个默认分区本身又被 HASH 分区，总共包含大约 1 亿条记录。

当我使用单个 dataset_key 附加一个新表时，它需要很长时间，因为显然需要扫描 verbatim_default 表。我的目的是提供一个避免扫描默认分区的检查约束。如果我使用这样的简单检查约束dataset_key < 10000可以正常工作并且附加是即时的。

但是，如果我使用更复杂的约束来进行一些计算，则不使用检查，而是扫描整个表。不起作用的检查示例和附加语句示例：

ALTER TABLE verbatim_default ADD CONSTRAINT vb_check1 CHECK (dataset_key <= 10000);
ALTER TABLE verbatim_default ADD CONSTRAINT vb_check2 CHECK (dataset_key <= 1000 OR dataset_key+2500<10000);
ALTER TABLE verbatim_default ADD CONSTRAINT vb_check3 CHECK (dataset_key+2500<10000);
ALTER TABLE verbatim_default ADD CONSTRAINT vb_check4 CHECK (dataset_key%100 <> 0);

-- this is instant as it can use check1
ALTER TABLE verbatim ATTACH PARTITION md_verbatim FOR VALUES IN (10800);

-- this scans verbatim_default even though check2, 3 and 4 apply
ALTER TABLE verbatim ATTACH PARTITION md_verbatim FOR VALUES IN (8000);

这是预期的吗？我正在使用 PostgreSQL 13。这可能在 PG14 中有所不同吗？

postgresql partitioning

1 个回答

  1. Best Answer

    是的，这是意料之中的。这是代码大小（针对特定和有限的用例）和性能之间的权衡。请注意，这不仅与加速特定情况的性能有关，而且还意味着由于对条件的更深入分析而导致所有其他情况的性能下降。

    改变表的轻微减速通常是可以接受的。但attach partition使用通常的状态分析基础设施 - predicate_implied_by。该功能在查询规划时会被多次使用。即使他们的计划没有改变，减慢这个功能也会减慢所有的查询。你想放慢一个简单select * from tablename where id=5的加速alter table吗？

    这就是 postgresql 在条件中不使用索引的原因where id + 1 = 6。确实有可能弄清楚如何检查这些条件并自动将查询重写为where id = 5. 但它会减慢每个请求。这不是错过的优化机会。

    • 1

相关问题