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

Поиск
Список
Период
Сортировка
От Craig James
Тема Re: Simple delete query is taking too long (never ends)
Дата
Msg-id CAFwQ8ret2Kj7ZKoDT_UnZ0Qtj1WOd=oZod8JpEUMAJEW5mpRpw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Simple delete query is taking too long (never ends)  (Merlin Moncure <mmoncure@gmail.com>)
Ответы Re: Simple delete query is taking too long (never ends)
Список pgsql-performance
Дерево обсуждения

On Thu, Nov 12, 2015 at 7:12 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
On Wed, Nov 11, 2015 at 1:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Massalin Yerzhan <yerzhik@gmail.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.

Craig
 

merlin


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



--
---------------------------------
Craig A. James
Chief Technology Officer
eMolecules, Inc.
---------------------------------

В списке pgsql-performance по дате отправления: