Re: Long-running DELETE...WHERE...

jboes(at)nexcerpt(dot)com (Jeff Boes) writes:
> My situation: table A has 200,000 rows. I've made up a temporary table
> which holds the single-valued primary key for 80,000 rows which I want
> to delete.
> DELETE FROM a WHERE id IN (select ID from tmp LIMIT 800);
> runs for several minutes.

WHERE ... IN ... is notoriously inefficient. I'd try

DELETE FROM aa WHERE id = tmp.id;

which is not standard SQL but should be able to produce a decent plan.

You might find that a VACUUM ANALYZE on both tables beforehand would be
a good idea, too; never-vacuumed temp tables have some default
statistics assumed that are a lot less than 80k rows.

regards, tom lane