Slow cascade delete on rows referenced from a partitioned table

Question

I have the following two tables:

CREATE TABLE events (
    id bigserial not null
            constraint birth_events _pkey
                primary key
) 

CREATE TABLE birth_events (
    simulation_id integer not null,
    event_id integer not null
            constraint fk_event_id
                references public.events
                    on update cascade on delete cascade
) PARTITION BY LIST (simulation_id);

create index ix_birth_events_event_id
    on public.birth_events (event_id);

birth_events is a partitioned table (using declarative partitioning) with simulation_id being the partition key. It has hundred of partitions containing thousands of rows each. Deleting from table events (thousands of rows at the same time) is super slow because PostgreSQL will scan the foreign key index of each birth_events partitions individually so it can cascade delete them. I would like to tell PostgreSQL to limit its cascade deletion of birth_events on a specific simulation_id so it only scans one partition, but how do I do this in SQL?

DELETE FROM events
LEFT JOIN birth_events on events.id = birth_events.event_id
WHERE events.id IN (XXX) AND birth_events.simulation_id = XXX;

This would tell PostgreSQL to look only a specific table partition, but it is not valid SQL. Is there a solution to this?

Answer 1

You could try an EXISTS condition:

delete from events
where events.id in (....)
  and exists (select * 
              from birth_events 
              where birth_events.simulation_id = 123456
                and birth_events.event_id = events.id);

Or maybe the USING clause:

delete from events
  using birth_events 
where events.id in (....)
  and birth_events.simulation_id = 123456
  and birth_events.event_id = events.id

Whether or not that uses partition pruning depends on the Postgres version. I doubt it would happen on Postgres 10 or 11.

On Postgres 14, both result in the same execution plan and I can see only the partition for the specified simulation_id being scanned.