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

jboes@nexcerpt.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