| 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: | Whole Thread | Raw Message | 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 |