title: Slow cascade delete on rows referenced from a partitioned table
body: <p>I have the following two tables:</p>
  <pre><code>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);
  </code></pre>
  <p><code>birth_events</code> is a partitioned table (using declarative partitioning) with <code>simulation_id</code> being the partition key. It has hundred of partitions containing thousands of rows each. Deleting from table <code>events</code> (thousands of rows at the same time) is super slow because PostgreSQL will scan the foreign key index of each <code>birth_events</code> partitions individually so it can cascade delete them. I would like to tell PostgreSQL to limit its cascade deletion of <code>birth_events</code> on a specific <code>simulation_id</code> so it only scans one partition, but how do I do this in SQL?</p>
  <pre><code>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;
  </code></pre>
  <p>This would tell PostgreSQL to look only a specific table partition, but it is not valid SQL. Is there a solution to this?</p>