Performance of DELETE with NOT IN (SELECT ...)

Question

I have these two tables and want to delete all authors from ms_author, who are not present in author.

author (1.6M rows)

+-------+-------------+------+-----+-------+
| Field | Type        | Null | Key | index |
+-------+-------------+------+-----+-------+
| id    | text        | NO   | PRI | true  |
| name  | text        | YES  |     |       |
+-------+-------------+------+-----+-------+

ms_author (120M rows)

+-------+-------------+------+-----+-------+
| Field | Type        | Null | Key | index |
+-------+-------------+------+-----+-------+
| id    | text        | NO   | PRI |       |
| name  | text        | YES  |     | true  |
+-------+-------------+------+-----+-------+

This is my query:

    DELETE
FROM ms_author AS m
WHERE m.name NOT IN
                   (SELECT a.name
                    FROM author AS a);

I tried to estimate the query duration: ~ 130 hours.
Is there a faster way to achieve this?

EDIT:

EXPLAIN VERBOSE output

Delete on public.ms_author m  (cost=0.00..2906498718724.75 rows=59946100 width=6)"
  ->  Seq Scan on public.ms_author m  (cost=0.00..2906498718724.75 rows=59946100 width=6)"
        Output: m.ctid"
        Filter: (NOT (SubPlan 1))"
        SubPlan 1"
          ->  Materialize  (cost=0.00..44334.43 rows=1660295 width=15)"
                Output: a.name"
                ->  Seq Scan on public.author a  (cost=0.00..27925.95 rows=1660295 width=15)"
                      Output: a.name"

Indexing author(name):

create index author_name on author(name);

Indexing ms_author(name):

create index ms_author_name on ms_author(name);

I think that using `join` or `exists` will be more performance — Roman Marusyk, Dec 14 '15 at 09:14
@jarlh: author.name is not indexed, while ms_author is indexed right now. — Sebbas, Dec 14 '15 at 10:27
@a_horse_with_no_name: I think with "output" you mean the table representation, right? I created it by hand. ;) — Sebbas, Dec 14 '15 at 10:27
what is estimate query duration after index task was finished? — dani herrera, Dec 14 '15 at 10:34
@a_horse_with_no_name: I'll index author.name when I get home and check back here with updated results and index statemens. — Sebbas, Dec 14 '15 at 10:43
You typically would not have a `text` PK in a table as huge as that. Neither would you have an (undeclared) FK referencing the same. Use `integer` or `bigint` values instead. — Erwin Brandstetter, Dec 14 '15 at 19:22
@ErwinBrandstetter: The database was given to me by MS (It's the Microsoft Research Graph) and the ids contain numbers+letters. After the merging procedure I'm trying to do right now there will be bigint ids. Thanks anyway! :) — Sebbas, Dec 14 '15 at 20:43

score 15 · Accepted Answer · answered Dec 14 '15 at 12:58

15

I'm a big fan of the "anti-join." This works efficiently for both large and small datasets:

delete from ms_author ma
where not exists (
  select null
  from author a
  where ma.name = a.name
)

answered Dec 14 '15 at 12:58

Hambone

15,600
8
46
69

2

That's the way to go. `NOT IN (SELECT ...)` is a tricky clause. Typically, there are [better alternatives](http://stackoverflow.com/a/19364694/939860). – Erwin Brandstetter Dec 14 '15 at 19:12
Thanks! :) It took about 10 hours. Coming from around 130 hours is a huge improvement! ;) – Sebbas Dec 16 '15 at 14:33

Alex · Answer 2 · 2015-12-14T09:25:46.277

Your delete query using NOT IN usually result in a nested loop antijoin which will result in poor performance. You can rewrite your query as follows:

You can write something like this:

DELETE FROM ms_author AS m
WHERE m.id IN
               (SELECT m.id FROM ms_author AS m
                LEFT JOIN author AS a ON m.name = a.name
                WHERE a.name IS NULL);

This approach has as additional advantage that you are using the primary key 'id' to delete rows and this should be much faster.

Performance of DELETE with NOT IN (SELECT ...)

2 Answers2

Linked