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.

for about 182,095 records. But when I used where budgetforeyar = 2018, it will return 60,751 records. — msagala25
– msagala25, Commented Nov 22, 2016 at 3:06
sorry mr. @TimBiegeleisen , but can you explain what index sir. I cant understand. Thanks. sorry for being noob. — msagala25
– msagala25, Commented Nov 22, 2016 at 3:18
What are the columns linking monthlyevaluatedbudgettable and monthlyadjustedbudgettable ? — Tim Biegeleisen
– Tim Biegeleisen, Commented Nov 22, 2016 at 3:33

Tim Biegeleisen · Accepted Answer · 2016-11-22 03:45:27Z

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.

answered Nov 22, 2016 at 3:45

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

msagala25 Over a year ago

I don't know how it happens, but it really work. THANK YOU mr. @Tim Biegelsen. pls Help me to understand how this works. I encounter many of this in my query. Can you tell me more about why you use index. hais I cant believe it and Im verry happy. Thanks. 1 vote up and Right answer for this.

msagala25 Over a year ago

It is like when 1 record in monthlyevaluatedbudgettable will be remove, when it cascades to monthlyadjustedbudgettable, it will check all the 182,095 records of it one-by-one? but when you have index, it will search for its same budgetid and it will end? sorry for me. im not so good in english. :)

Tim Biegeleisen Over a year ago

@msagala On the contrary, both your English and reasoning seem completely clear to me. Yes, without an index, Postgres might have to search the entire child table to find records which need to be cascade deleted. But with an index, the lookup is dramatically faster. This is all there really is to it.

msagala25 Over a year ago

Ok I got it now. Thanks Mr. @Tim Biegeleisin. Appreciate it. See you around then and help others like me too. :)

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related