在 PostgreSQL 中 ATTACH PARTITION 時並非所有檢查約束都使用

我有一個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 中有所不同嗎？

是的，這是意料之中的。這是程式碼大小（針對特定和有限的案例）和性能之間的權衡。請注意，這不僅與加速特定情況的性能有關，而且還意味著由於對條件的更深入分析而導致所有其他情況的性能下降。

改變表的輕微減速通常是可以接受的。但attach partition使用通常的狀態分析基礎設施 - predicate_implied_by。該功能在查詢規劃時會被多次使用。即使他們的計劃沒有改變，減慢這個功能也會減慢所有的查詢。你想放慢一個簡單select * from tablename where id=5的加速alter table嗎？

這就是 postgresql 在條件中不使用索引的原因where id + 1 = 6。確實有可能弄清楚如何檢查這些條件並自動將查詢重寫為where id = 5. 但它會減慢每個請求。這不是錯過的優化機會。

引用自：https://dba.stackexchange.com/questions/308492