Обсуждение: question about "delete performance"
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 whereoid in (XXX1,XXX2,XXX3,....X1000) ", but I got same result, does anyone can tell me the reason or postgresql has a badperformance in such situation. Thanks for help ! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Zhou.daojing on 2005/01/24 Email: yourfriend@hf-sanyo.com Tel: 0551-5338001 Fax: 0551-5319984 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
When grilled further on (Mon, 24 Jan 2005 12:29:12 +0800), Öܵ½¾© <yourfriend@hf-sanyo.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
Вложения
On Mon, Jan 24, 2005 at 12:29:12PM +0800, Öܵ½¾© wrote: > 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. What does EXPLAIN ANALYZE DELETE ... show? Do you have an index on the oid column? Do you have foreign key constraints that need to be checked when rows are deleted? Do you have rules or triggers that are being invoked? -- Michael Fuhr http://www.fuhr.org/~mfuhr/