Re: Very slow DELETEs with foreign keys

On 8 February 2016 at 14:52, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Thom Brown <thom@linux.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