Thread: Simple delete query is taking too long (never ends)
Postgresql version 9.4.4.
I'm having an issue. The query never ends:delete from bb_gamelist_league;
No WHERE clause used. There are approx. 227000 rows in that table.
Here is the table itself:
CREATE TABLE bb_gamelist_league (
id SERIAL NOT NULL ,
bb_league_id INTEGER NOT NULL ,
day_number INTEGER,
date BIGINT ,
team_id1 INTEGER ,
team_id2 INTEGER ,
score1 SMALLINT ,
score2 SMALLINT ,
attended_people INTEGER ,
is_play_off BOOL ,
play_off_code VARCHAR(5),
game_status BOOL ,
is_finished BOOL ,
was_taken_by_gameserv BOOL,
taken_by_coordinator_status BOOL,
seed TIMESTAMP,
managerA_watching BOOL,
managerB_watching BOOL,
day_period VARCHAR(10),
group_number VARCHAR(30),
PRIMARY KEY(id) ,
FOREIGN KEY(bb_league_id) REFERENCES bb_league(id),
FOREIGN KEY (team_id1) REFERENCES bb_team_info(id),
FOREIGN KEY (team_id2) REFERENCES bb_team_info(id));
There are some indexes on that table:
public | bb_gamelist_league | bb_gamelist_league_fkindex1 | | CREATE INDEX bb_gamelist_league_fkindex1 ON bb_gamelist_league USING btree (bb_league_id)
public | bb_gamelist_league | bb_gamelist_league_pkey | | CREATE UNIQUE INDEX bb_gamelist_league_pkey ON bb_gamelist_league USING btree (id)
Also explain gives the following result:
explain delete from bb_gamelist_league;
QUERY PLAN
--------------------------------------------------------------------------------
Delete on bb_gamelist_league (cost=0.00..6954.63 rows=281363 width=6)
-> Seq Scan on bb_gamelist_league (cost=0.00..6954.63 rows=281363 width=6)
(2 rows)
Explain analyze never ends (because the query itself is never ending).
I checked the locks: there are no locks on tables.
The CPU is fast enough but "top" command on linux shows 100% load for postgres process.
Could you help to resolve the issue?
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. regards, tom lane
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. merlin
Merlin Moncure <mmoncure@gmail.com> writes: > On Wed, Nov 11, 2015 at 1:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> 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. In principle, a plain EXPLAIN could list the triggers that would potentially be fired by the query, but I'm afraid that wouldn't help much. The actual performance problem is down inside the trigger, which is an opaque black box so far as EXPLAIN can possibly know. regards, tom lane
On Thu, Nov 12, 2015 at 7:12 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
What about a warning on creation?
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.
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 OfficerCraig A. James
On Thu, Nov 12, 2015 at 9:48 AM, Craig James <cjames@emolecules.com> wrote: > > 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. 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
Merlin Moncure <mmoncure@gmail.com> writes: > On Thu, Nov 12, 2015 at 9:48 AM, Craig James <cjames@emolecules.com> wrote: >> 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. We could probably teach pg_dump to put index definitions before FKs, if it doesn't already. But I'm suspicious of this sort of "training wheels" warning --- we've had roughly similar messages in the past and removed them because too many people complained about them. Worth noting in particular is that there would be no way to avoid the warning unless you split out the FK declaration to a separate "alter table add constraint" step. pg_dump does that anyway, but manual schema definitions likely would look more like create table foo(i integer primary key); create table bar(j integer primary key, i integer references foo); create index on bar(i); which would provoke the warning. I fear a warning like that would have a very short life expectancy. regards, tom lane
On Thu, Nov 12, 2015 at 4:26 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Merlin Moncure <mmoncure@gmail.com> writes: >> On Thu, Nov 12, 2015 at 9:48 AM, Craig James <cjames@emolecules.com> wrote: >>> 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. > > We could probably teach pg_dump to put index definitions before FKs, if it > doesn't already. But I'm suspicious of this sort of "training wheels" > warning --- we've had roughly similar messages in the past and removed > them because too many people complained about them. For posterity, indexes are the last step -- and I think that's a good way to do things. As to the broader point, I agree. Warnings should be reserved for things that are demonstrably dubious, and there are just too many situations where that doesn't apply for an unindexed foreign constraint. Oh well. merlin
On Fri, Nov 13, 2015 at 7:15 AM, Merlin Moncure <mmoncure@gmail.com> wrote: > On Thu, Nov 12, 2015 at 4:26 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Merlin Moncure <mmoncure@gmail.com> writes: >>> On Thu, Nov 12, 2015 at 9:48 AM, Craig James <cjames@emolecules.com> wrote: >>>> 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. >> >> We could probably teach pg_dump to put index definitions before FKs, if it >> doesn't already. But I'm suspicious of this sort of "training wheels" >> warning --- we've had roughly similar messages in the past and removed >> them because too many people complained about them. > > For posterity, indexes are the last step -- and I think that's a good > way to do things. As to the broader point, I agree. Warnings should > be reserved for things that are demonstrably dubious, and there are > just too many situations where that doesn't apply for an unindexed > foreign constraint. Oh well. If they were implemented as a notice that would be different. Much like the notice you get about index creation on PK / Unique constraint creation. But I'm not 100% sure it's a good idea.