Re: Simple delete query is taking too long (never ends)

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