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