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

Simple delete query is taking too long (never ends)

From
Massalin Yerzhan
Date:
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?

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

From
Tom Lane
Date:
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


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

From
Merlin Moncure
Date:
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


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

From
Tom Lane
Date:
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


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

From
Craig James
Date:

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.
---------------------------------

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

From
Merlin Moncure
Date:
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


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

From
Tom Lane
Date:
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


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

From
Merlin Moncure
Date:
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


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

From
Scott Marlowe
Date:
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.