I'm trying to delete a bunch of rows from a table matching a query. The general form of my query is:
DELETE FROM mytable WHERE _id IN (SELECT _id FROM mytable WHERE ...);
_id column is a
SERIAL PRIMARY KEY.
If I run the inner
SELECT query on its own it runs in about 1 second and returns about 100,000 rows. But when I add the
DELETE call onto it, it seems to just sit and chug away. And chug away. I let it run for about a minute or so, and then cancelled it assuming no progress was being made.
EXPLAIN ANALYZE onto the front of it to see if I could check what was taking so long, but that call also hangs for a very long period of time.
Can anybody tell me what could be going on such that it's very quick to identify the rows I'd like to delete but takes an indeterminate amount of time to delete them?
Update: The full query is as follows.
DELETE FROM cards WHERE _id IN ( SELECT _id FROM cards LEFT JOIN game_results ON game_results.card_id = cards._id WHERE NOT available AND game_id IS NULL )
A simplified version of the tables would be:
CREATE TABLE cards (_id INTEGER PRIMARY KEY, available BOOLEAN); CREATE TABLE games (_id INTEGER PRIMARY KEY); CREATE TABLE game_results (game_id INTEGER REFERENCES games, card_id INTEGER REFERENCES cards);
I'm sure this query would eventually finish running, I'm just surprised at how long it's taking given that retrieving all of the IDs to delete is so quick.
The problem was three-fold!
I had one of the earlier (very slow!) queries running that had been triggered by an aborted script. The query was still running even though the script had been aborted. As such, operations like trying to drop indexes were locked waiting for the query to finish. Manually cancelling that query using
pg_cancel_backendallowed me to experiment with dropping indexes and foreign key constraints.
Foreign key constraints seem to be the issue slowing everything down. The fact that the
cardsmade the delete take forever. The funny thing is, I was only deleting cards explicitly unused in the game_results, but of course that didn't stop the foreign key checks from happening upon delete! That made things very slow. I dropped the foreign key constraint before running the delete and that sped things up to a level I felt was appropriate.
For whatever reason, the second delete query in the accepted answer ran much quicker than the first.
By combining these three things I was able to do the whole delete in a few seconds, whereas until I had combined these three factors together my queries were running to 5-10 minutes (before I was cancelling them!).
Thanks to all for the help!