Re: question about "delete performance"

When grilled further on (Mon, 24 Jan 2005 12:29:12 +0800),
Öܵ½¾© <yourfriend(at)hf-sanyo(dot)com> confessed:

> I have a table with 500,000 records which has some invalid records, I had
> wrote a program to check it, by the program I get all OIDs of the redundant
> records, so I use "delete from tableXXX where oid =XXX1 or oid =XXX2 or oid
> =XXX3 ... or oid=XXX1000, but it take me a long time to complete this action,
> then I change the query to " delete from tableXXX where oid in
> (XXX1,XXX2,XXX3,....X1000) ", but I got same result, does anyone can tell me
> the reason or postgresql has a bad performance in such situation.
>

You probably don't have an index on the oid column, so it searches through
the entire table for each element you're deleting. Put an index on the first
table on the oid column. I would just delete them one at a time also, as I know
it would then use the index. I don't know if the index would be used in
your form. Probably would.

Cheers,
Rob
--
21:59:32 up 40 days, 2 min, 9 users, load average: 2.64, 3.10, 3.77
Linux 2.6.5-02 #8 SMP Mon Jul 12 21:34:44 MDT 2004