Re: Simple delete query is taking too long (never ends) - Mailing list pgsql-performance

From Tom Lane
Subject Re: Simple delete query is taking too long (never ends)
Date
Msg-id 22756.1447367182@sss.pgh.pa.us
Whole thread Raw
In response to Re: Simple delete query is taking too long (never ends)  (Merlin Moncure <mmoncure@gmail.com>)
Responses Re: Simple delete query is taking too long (never ends)  (Merlin Moncure <mmoncure@gmail.com>)
List pgsql-performance
Merlin Moncure <mmoncure@gmail.com> writes:
> On Thu, Nov 12, 2015 at 9:48 AM, Craig James <cjames@emolecules.com> wrote:
>> What about a warning on creation?
>>
>> db=> create table foo(i integer primary key);
>> db=> create table bar(j integer primary key, i integer);
>> db=> alter table bar add constraint fk_bar foreign key(i) references foo(i);
>> WARNING: fk_bar: column bar(i) has no index, deletions on table foo may be
>> slow.
>>
>> It might save some fraction of these questions.

> Maybe, but I wonder if this would cause pg_restore to bleat warnings
> when restoring.

We could probably teach pg_dump to put index definitions before FKs, if it
doesn't already.  But I'm suspicious of this sort of "training wheels"
warning --- we've had roughly similar messages in the past and removed
them because too many people complained about them.

Worth noting in particular is that there would be no way to avoid the
warning unless you split out the FK declaration to a separate "alter table
add constraint" step.  pg_dump does that anyway, but manual schema
definitions likely would look more like

create table foo(i integer primary key);
create table bar(j integer primary key, i integer references foo);
create index on bar(i);

which would provoke the warning.  I fear a warning like that would have
a very short life expectancy.

            regards, tom lane


pgsql-performance by date:

Previous
From: Merlin Moncure
Date:
Subject: Re: Simple delete query is taking too long (never ends)
Next
From: Merlin Moncure
Date:
Subject: Re: Simple delete query is taking too long (never ends)