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?