From: Tom Lane <tgl@sss.pgh.pa.us>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: Craig James <cjames@emolecules.com>
Cc: Massalin Yerzhan <yerzhik@gmail.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Simple delete query is taking too long (never ends)
Date: Thu, 12 Nov 2015 17:26:22 -0500
Message-ID: <22756.1447367182@sss.pgh.pa.us> (raw)
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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, mmoncure@gmail.com, cjames@emolecules.com, yerzhik@gmail.com
Subject: Re: Simple delete query is taking too long (never ends)
In-Reply-To: <22756.1447367182@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox