From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Craig James <cjames(at)emolecules(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Massalin Yerzhan <yerzhik(at)gmail(dot)com>, postgres performance list <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Simple delete query is taking too long (never ends) |
Date: | 2015-11-12 22:07:29 |
Message-ID: | CAHyXU0yFODMX0oGkQJ-TTkXU4X3xdov-3e_XUjotYRA_bnc6-A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Thu, Nov 12, 2015 at 9:48 AM, Craig James <cjames(at)emolecules(dot)com> wrote:
>
> On Thu, Nov 12, 2015 at 7:12 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>>
>> On Wed, Nov 11, 2015 at 1:33 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> > Massalin Yerzhan <yerzhik(at)gmail(dot)com> writes:
>> >> I'm having an issue. The query never ends:
>> >> delete from bb_gamelist_league;
>> >
>> > 9 times out of 10, the answer to this type of problem is that you have
>> > some table referencing this one by a foreign key, and the referencing
>> > column is not indexed. PG doesn't require such an index, but lack of
>> > one will mean that retail checks or deletions of referencing rows are
>> > really slow.
>> >
>> > If you're not sure which table is the problem, try doing an EXPLAIN
>> > ANALYZE of a DELETE that will only remove a few rows. You should
>> > see some time blamed on a trigger associated with the FK constraint.
>>
>> You've answered this question (with the same answer) what feels like a
>> gazillion times. I guess the underlying problem is that EXPLAIN is,
>> uh, not explaining things very well.
>
>
> 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. I was hoping that explain could report potential
irregularities, but Tom's comments seem to suggest difficulties there.
merlin
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2015-11-12 22:26:22 | Re: Simple delete query is taking too long (never ends) |
Previous Message | Craig James | 2015-11-12 15:48:50 | Re: Simple delete query is taking too long (never ends) |