From: Tom Lane <tgl@sss.pgh.pa.us>
To: Merlin Moncure <mmoncure@gmail.com>
cc: Craig James <cjames@emolecules.com>, Massalin Yerzhan <yerzhik@gmail.com>,
 postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Simple delete query is taking too long (never ends)
In-reply-to: 
 <CAHyXU0yFODMX0oGkQJ-TTkXU4X3xdov-3e_XUjotYRA_bnc6-A@mail.gmail.com>
References: 
 <CAMAXSpJyE4TN068xzDNXk-=7AJBnmZVrK8D2ZQz0eoP0vo30CQ@mail.gmail.com>
 <17089.1447270415@sss.pgh.pa.us>
 <CAHyXU0wNYJQwW+m0vP_GaSmY6yh82WxB8+425TziJPVMAu-dVA@mail.gmail.com>
 <CAFwQ8ret2Kj7ZKoDT_UnZ0Qtj1WOd=oZod8JpEUMAJEW5mpRpw@mail.gmail.com>
 <CAHyXU0yFODMX0oGkQJ-TTkXU4X3xdov-3e_XUjotYRA_bnc6-A@mail.gmail.com>
Comments: In-reply-to Merlin Moncure <mmoncure@gmail.com>
 message dated "Thu, 12 Nov 2015 16:07:29 -0600"
Date: Thu, 12 Nov 2015 17:26:22 -0500
Message-ID: <22756.1447367182@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance