Clarification of behaviour when dropping partitions

[Bolaji Wahab <bolajiwahab23@xxxxxxxxx>]

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.