Re: Very slow DELETEs with foreign keys

On 8 February 2016 at 14:52, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 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.

Yeah, there's no index on contacts.country, and that would certainly
make the query acceptably quick, but I'm asking whether the
non-indexed scenario is going about things the most efficient way,
given what it has available to it.

Thom