Search code examples
postgresqlsql-delete

Improve delete from

I need to delete records from a table using another table as follow:

DELETE FROM table1 ri
USING table2 ta
WHERE ri.uid = ta.uid;

Table1 has 160 million rows, and table2, 14 million rows.

It is taking ages, I added a unique index to table2 and cost didn't improve, is there a way to get this done faster?

Thanks

Solution

  • Using Explain Analyze with a small chunk of data to delete from, we ended up realizing there was a constraint taking all the cost, we removed the constraint to speed up the delete operation, then re-added it.