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) 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?
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.
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
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 🙂