Re: Very slow DELETEs with foreign keys

Thom Brown <thom(at)linux(dot)com> writes:
> I've just noticed a general delete performance issue while testing a
> patch, and this can be recreated on all recent major versions.

> I have 2 tables:

> CREATE TABLE countries (
> country text PRIMARY KEY,
> continent text
> );

> CREATE TABLE contacts (
> id serial PRIMARY KEY,
> first_name text,
> last_name text,
> age integer,
> country text REFERENCES countries (country)
> );

Apparently, you don't have an index on the referencing column.
That makes insertions into contacts faster, at the cost of making
deletions from countries much slower. Since there are cases where
that's a reasonable tradeoff, we don't prohibit you from omitting
the index ... but it is a pretty standard foot-gun.

regards, tom lane