Not all check constraints used when ATTACH PARTITION in PostgreSQL

All we need is an easy explanation of the problem, so here it is.

I have a table verbatim which is partitioned by an integer column dataset_key which also is part of a compound primary 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)
    "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

There is a verbatim_default partition that catches all dataset keys not explicitly mentioned in any of the partitions. This default partition is in itself partitioned again by HASH and contains about 100 million records alltogether.

When I attach a new table with a single dataset_key it takes a long time, because the verbatim_default table apparently needs to be scanned. My intention is to provide a check constraint that avoids the scanning of the default partition. If I use a simple check constraint like dataset_key < 10000 this works fine and attaching is instant.

But if I use a bit more complex constraint which does do some calculations the check is not used and instead the entire table is scanned. Examples of checks that do not work and an example attach statement:

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

Is this expected? I am using PostgreSQL 13. Is this maybe different in PG14?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Yes, this is expected. It’s a trade-off between code size (for specific and limited use cases) and performance. Note that not only about the performance to speed up a particular case, but it also means performance degradation for all others cases due to deeper analysis of the conditions.

A slight slowdown of the alter table is generally acceptable. But attach partition uses the usual condition analyze infrastructure – predicate_implied_by. This function is used many times during query planning. Slowing down this function will slow down all queries even if their plans don’t change. Would you like to slow down a simple select * from tablename where id=5 to speed up alter table?

This is the reason why postgresql don’t use an index in the where id + 1 = 6 condition. It’s really possible to figure out how to check for such conditions and automatically rewrite query to where id = 5. But it will slow down every request. It’s not a missing optimization opportunity.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply