Query running is too long when I delete some records in Postgres

Question

When I run this line of code.

DELETE from monthlyevaluatedbudgettable where budgetforyear = 2018;

It was only saying in the status bar Query is Running. and I think that It will not end up, so I will cancel it, and then this warning post in the messages of the logs of error:

ERROR:  canceling statement due to user request
CONTEXT:  SQL statement "DELETE FROM ONLY "public"."monthlyadjustedbudgettable" WHERE $1 OPERATOR(pg_catalog.=) "budgetid""

********** Error **********

ERROR: canceling statement due to user request
SQL state: 57014
Context: SQL statement "DELETE FROM ONLY "public"."monthlyadjustedbudgettable" WHERE $1 OPERATOR(pg_catalog.=) "budgetid""

The relationship of monthlyadjustedbudgettable is ON DELETE CASCADE on monthlyevaluatedbudgettable. Can someone tell me what would be the problem ?

I have 182,095 records in both of the table. their relationship is one-to-one.

Answer 1

You should first try using EXPLAIN on your current query to see what is happening in detail.

My hunch as to why your delete query is so slow is that you have an ON DELETE CASCADE constraint on monthlyadjustedbudgettable. This means that for each record in monthlyevaluatedbudgettable a check must be made in monthlyadjustedbudgettable to see if any records there need to be removed as well. Since there is no index on that table, a full table scan likely is happening. Given that you have about 200K records in each table, this might be prohibitively large in terms of time needed.

There is a quick fix you can try. You can add an index on the foreign key column in monthlyadjustedbudgettable:

CREATE UNIQUE INDEX budget_idx ON monthlyadjustedbudgettable (budgetid);

This assumes that the foreign key column in monthlyadjustedbudgettable is called budgetid.