On Jul 13, 2005, at 12:46 PM, Tom Lane wrote:
Doug Hall <doughalldev@xxxxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
