Re: Slow delete

Doug Hall <doughalldev(at)gmail(dot)com> writes:
> delete from citizen where id not in (select citizenid from
> citizen_stage);

> The explain select tells me that there is a sequential select of
> citizen_stage records. (??) There are 75009 citizen records and 14778
> records, and it's taking more than half an hour. How can I speed this
> up?

How old is your Postgres? I'd expect 7.4 and up to do this with a hashed
IN, which'd be reasonably fast. If the EXPLAIN output doesn't say
anything about a "hashed subplan", then either you've got an old version
or there's some sort of estimation problem.

If it is a hashed IN and it's still slow, I'd wonder about unindexed
foreign key references to the citizen table.

regards, tom lane