Not all check constraints used when ATTACH PARTITION in PostgreSQL

by

The question:

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)
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

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
ALTER TABLE verbatim ATTACH PARTITION md_verbatim FOR VALUES IN (8000);

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

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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.


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

Leave a Comment