I have a substantial database... not a very large one - around 1gb of data in total.
I need to delete some rows from several tables. For example I have a table
Order
id | ... | status | ...
1 | ... | 1 | ...
...
40 | ... | 20 | ...
41 | ... | 1 | ...
...
470000 | ... | 12 | ...
Now I want to delete all orders that have status=1
I presume I do it with:
DELETE FROM Order WHERE status=1
All nice and simple it would seem but it takes ages! When I have run this query it was still running at 100% CPU usage after 40 min... when I killed the process nothing was deleted.
When I tried limiting the scope by using
DELETE FROM Order WHERE status=1 AND id<1000
it took couple of minutes to delete some 200 rows....
Is there anything I am missing in my configuration? Anything I should look for/check/change? Any ideas at all why its so bloody inefficient?
Let me add that I am usually working with MySQL and need to manage this postgres database but don't really have any experience with postgres, so it may be something very simple.
Indexes are on both id and status columns.
Table has some 500k rows, around half needs to be deleted.
Execution plan:
Delete (cost=0.00..19474.19 rows=266518 width=6)
-> Seq Scan on Orders (cost=0.00..19474.19 rows=266518 width=6)
Filter: (statusid = 1)
There are no triggers or rules of any sort. What's more, I didn't add this is a fresh copy of the table, I mean it was moved from other server with export/import. Perhaps this plays a role somehow?
Will deleting indexes help?