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