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?

1 Answer 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.

  • Thanks, that makes sense and is interesting to know. operator_predicate_proof seems to be the actual method to test simple clauses. Mar 9, 2022 at 23:14
  • Good explanation, but it is more about code speed than code size Mar 10, 2022 at 8:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.