'DELETE CASCADE in PostgreSQL extremely slow
I have a table "studies" with 14 subtables that are connected to "studies" via a foreign key that refers to the primary key in "studies".
I need to delete all rows from the table "studies" where the column "overall_status" is neither "Recruiting" nor "Not yet recruiting". I need to delete the appropriate rows in the dependent tables, too.
I tried with
DELETE from ctgov.studies where overall_status not in ('Recruiting','Not yet recruiting');
But this takes hours. Is there a faster solution?
Solution 1:[1]
DELETE CASCADE was extremely slow in PgAdmin; after an overnight session, I discontinued it. Exactly the same query ran flawlessly in psql. I also reduced the number of indexes before deleting a big number of rows.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |