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.
