On Thu, Nov 12, 2015 at 9:48 AM, Craig James <cjames@xxxxxxxxxxxxxx> wrote: > > On Thu, Nov 12, 2015 at 7:12 AM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote: >> >> On Wed, Nov 11, 2015 at 1:33 PM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote: >> > Massalin Yerzhan <yerzhik@xxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
