Postgresql delete query never ends

Question

I am having an issue which I can't resolve.

delete from bb_gamelist_league

this operation is taking too long. There are 276224 rows in that column. Delete is performed without any WHERE conditions.

Here is the table:

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));


CREATE INDEX bb_gamelist_league_FKIndex1 ON bb_gamelist_league (bb_league_id); 

And there is no locks happening, the delete query is active and not waiting(information from pg_stat_activity).

I waited for one day but it didn't end. Also the CPU load is 100% by postgresql, though I'm having 2.24 Ghz CPU and memory is enough (there is free memory).

How can I understand the cause?

EDIT: there is an index for id column in that table (not listed in above create statement)

Answer 1

The cause is probably due to I/O.

Use "iostat -xm 2" to see I/O, or iotop to monitor performance, or check the "wa" (i/o wait) in top for a rough estimate.

To delete everything immediately, use truncate:

truncate bb_gamelist_league

this will run immediately, emptying your table with the added benefit of releasing the disk space (DELETE would not release it).

Be careful with constraints/references from other tables though. Check the manual for more info.