Simple delete query is taking too long (never ends)

From: Massalin Yerzhan <yerzhik(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Simple delete query is taking too long (never ends)
Date: 2015-11-11 19:09:36
Message-ID: CAMAXSpJyE4TN068xzDNXk-=7AJBnmZVrK8D2ZQz0eoP0vo30CQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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?

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2015-11-11 19:33:35 Re: Simple delete query is taking too long (never ends)
Previous Message Skarsol 2015-11-11 17:57:18 Queries getting canceled inside a proc that seems to slow down randomly