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)

• user1822: There is something strange. Deleting 276224 shouldn't take long, definitely not longer than a couple of seconds.
• maximus: yeah its strange. I wonder if indexes not put to foreign key constraints of dependent tables could cause it?

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.