1

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)

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

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.