Re: Slow delete

On Jul 13, 2005, at 12:46 PM, Tom Lane wrote:

> 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.

My boss is using 8.0.0 beta4! (Yikes) I'll upgrade him just to make
sure.

> 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.

No, the EXPLAIN doesn't mention "hashed subplan". I suspect it was a
bug in the beta.

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

The foreign key is indexed without specifying the method, so it's
B-tree by default.

Does PostgreSQL automatically create a hashed index for primary keys?
If not, then we need to drop the index and create it using...

CREATE INDEX name ON table USING HASH (column);

However, the documentation says:

Note: Testing has shown PostgreSQL's hash indexes to perform no better
than B-tree indexes, and the index size and build time for hash indexes
is much worse. For these reasons, hash index use is presently
discouraged.

So, why have hashed indexes?

Thanks,
Doug