Why is this delete operation slower after a foreign key relationship has been added?

Imagine the following table about cat owners.

drop table if exists  owners cascade;
create table owners(
    id bigint primary key generated always as identity ,
    name text not null
)
;

insert into owners(name)
select random()::text from generate_series(1,20000);
--insert 200,000 owners records

When I delete some of the owners records it is pretty fast:

delete  from owners
where id %10 = 0;

20000 rows affected in 85 ms

Now I add a table called 'cats' that refers to owners:

drop table if exists cats;
create table cats(
    id serial primary key ,
    name varchar(20000) not null,
    owner_id int not null references owners(id)
);

--insert 1bn cats records
insert into cats(name, owner_id)
select
       random()::text,
       owners.id
from generate_series(1,10), owners;

Lets delete some owners, but first we have to delete the cats that these owners 'own':

--delete the records in cats so we don't get a foreign key constraint violation
delete  from cats
where owner_id %10 = 1;

---now we do the same delete on owners as we did before
delete  from owners
where id %10 = 1;

2000 rows affected in 25 s 828 ms

Why is the second delete from owners ~5000 times slower than when we didn't have the cats table?

It's the check if the owner is still referenced by the cats table during the DELETE. The check is essentially done using a select * from cats where owner_id = ? for every owner that you delete.

You can speed up the check by creating an index on the foreign key column:

create index on cats (owner_id);

StackExchange Database Administrators Q#241954, answer score: 7