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

On Thu, Nov 12, 2015 at 4:26 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.

For posterity, indexes are the last step -- and I think that's a good
way to do things.  As to the broader point, I agree.  Warnings should
be reserved for things that are demonstrably dubious, and there are
just too many situations where that doesn't apply for an unindexed
foreign constraint.  Oh well.

merlin