How can i fast delete row in postgres?

user_15360018 0
Viewed 634

I have table with 17,000,000 rows. I need to delete 500,000 with certain conditions. At this moment i have a script with 500,000 rows looks like

delete from table where name = 'John' and date = '2010-08-04';
delete from table  where name = 'John' and date = '2010-08-05';
delete from table  where name = 'Adam' and date = '2010-08-06';

One row executed about 2.5 seconds. It's too long. How can i improve speed?

postgresql sql
2 Answers

If there is no index on name and date field then try to create below index and try your code.

CREATE INDEX idx_table_name_date  ON table  (name, date)

If possible you can also minimize the number of delete statement by merging them.

Instead of

delete from table where name = 'John' and date = '2010-08-04';
delete from table  where name = 'John' and date = '2010-08-05';

It can be:

delete from table where name = 'John' and date in('2010-08-04','2010-08-05');
user_86516010

I would suggest that you load the rows to delete into a table and use:

delete from table t
   from todelete td
   where t.name = td.name and t.date = td.date;

Even without indexes, this should be faster than zillions of separate delete statements. But you want an index on table(name, date) for performance.

If the data already comes from a table or query, then you can just use that directly.

You can also incorporate this into one query by listing the values explicitly in a from clause:

delete from table t
    from (values ('John', '2010-08-04'),
                 ('John', '2010-08-05')
                 ('Adam', '2010-08-06')
         ) todelete
    where t.name = td.name and t.date = td.date;
user_11440350
Related