Hi team,
I have these two partitioned tables, with referential integrity. The tables
are structured in such a way that we have 1 to 1 mapping between their
partitions. This is achieved with a foreign key.
```
CREATE TABLE parent (
partition_date date NOT NULL,
id uuid NOT NULL,
external_transaction_id uuid NOT NULL,
CONSTRAINT parent_pkey
PRIMARY KEY (id, partition_date),
CONSTRAINT parent_external_transaction_id_key
UNIQUE (external_transaction_id, partition_date)
) PARTITION BY RANGE (partition_date);
CREATE TABLE parent_2024_12_01
PARTITION OF public.parent
FOR VALUES FROM ('2024-12-01') TO ('2024-12-02');
CREATE TABLE parent_2024_12_02
PARTITION OF public.parent
FOR VALUES FROM ('2024-12-02') TO ('2024-12-03');
CREATE TABLE parent_2024_12_03
PARTITION OF public.parent
FOR VALUES FROM ('2024-12-03') TO ('2024-12-04');
CREATE TABLE parent_2024_12_04
PARTITION OF public.parent
FOR VALUES FROM ('2024-12-04') TO ('2024-12-05');
CREATE TABLE parent_2024_12_05
PARTITION OF public.parent
FOR VALUES FROM ('2024-12-05') TO ('2024-12-06');
CREATE TABLE child (
partition_date date NOT NULL,
transaction_id uuid NOT NULL,
key text NOT NULL,
value text NOT NULL,
CONSTRAINT child_pkey
PRIMARY KEY (transaction_id, key, partition_date),
CONSTRAINT child_transaction_id_fkey
FOREIGN KEY (transaction_id, partition_date)
REFERENCES parent (id, partition_date)
) PARTITION BY RANGE (partition_date);
CREATE TABLE child_2024_12_01
PARTITION OF child
FOR VALUES FROM ('2024-12-01') TO ('2024-12-02');
CREATE TABLE child_2024_12_02
PARTITION OF public.child
FOR VALUES FROM ('2024-12-02') TO ('2024-12-03');
CREATE TABLE child_2024_12_03
PARTITION OF public.child
FOR VALUES FROM ('2024-12-03') TO ('2024-12-04');
CREATE TABLE child_2024_12_04
PARTITION OF public.child
FOR VALUES FROM ('2024-12-04') TO ('2024-12-05');
CREATE TABLE child_2024_12_05
PARTITION OF public.child
FOR VALUES FROM ('2024-12-05') TO ('2024-12-06');
```
I have a scheduled job that removes old partitions with simply `DROP
TABLE`. It processes the child table first, then the parent table. For
example
First transaction (works fine and quick):
```
DROP TABLE child_2024_12_01;
```
Second transaction (slow, degrading performance):
Here, I had to detach the partition first because of the inherited
references
```
ALTER TABLE parent DETACH PARTITION parent_2024_12_01;
DROP TABLE parent_2024_12_01;
```
I noticed the job was taking a long time and affecting the performance of
the database.
After debugging, I found this query used internally by Postgres.
```
SELECT fk."transaction_id", fk."partition_date"
FROM "public"."child" fk
JOIN "public"."parent_2024_12_01" pk ON
(pk."id" OPERATOR(pg_catalog.=) fk."transaction_id" AND pk."partition_date"
OPERATOR(pg_catalog.=) fk."partition_date")
WHERE ((pk.partition_date IS NOT NULL)
AND (pk.partition_date >= '2024-12-01'::date) AND (pk.partition_date <
'2024-12-02'::date))
AND (fk."transaction_id" IS NOT NULL AND fk."partition_date" IS NOT NULL)
```
Which of course is not able to do partition pruning on the child table.
Wondering if this is somehow the expectation or an edge case. One would
have expected the optimiser to target the child partition with the
available foreign key.
Postgres Version: *14.10*
Thanks.
