Postgres 9 super slow easy removal

Question

I have a substantial database ... not very large - only about 1 g of data.

I need to delete multiple rows from multiple 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 with status=1

I suppose I do it with

REMOVE FROM ORDER WHERE status = 1

Everything is beautiful and simple, it would seem, but it takes age! When I ran this request, it still worked at 100% CPU usage after 40 minutes ... when I killed the process, nothing was deleted.

When I tried to limit the area with

REMOVE FROM SIGN Status WHERE = 1 AND id <1000

it took a couple of minutes to remove 200 rows ....

Is there something that I am missing in my configuration? All I have to look for / check / modify? Any ideas whatsoever, why are they so bloody ineffective?

Let me add that I usually work with MySQL and should manage this postgres database, but I really have no experience with postgres, so this can be something very simple.

Indexes are displayed both in id columns and in status.

There are several 500 thousand rows in the table, and half should 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. What else, I did not add that this is a new copy of the table, I mean that it was transferred from another server using export / import. Maybe this somehow plays a role?

Will deleting indexes help?

Answer 1

Without deleting anything after you killed the process, EXACTLY what you will see.

Deletion occurs as a transaction, meaning that either everything is deleted or there is nothing. To make sure this can happen, the lines should be copied somewhere before they are deleted. This means that deleting rows of 250 thousand takes about the same time as inserting many rows. In some cases, it’s faster to create a new table with everything NOT deleted and rename the new table to the old one.

If this is moving from another db, you will probably be better off if you can save 250-line lines in the first place.

(This is the general wisdom of RDBMS, not postgresql specific — the details of how postgres MVCC works can vary greatly.)

Answer 2

This is a much faster use of COPY FROM / TURNCATE / COPY TO on large tables.

But, of course, you have to be careful with links and, if possible, disable triggers.