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

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.

Craig

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

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