[GENERAL] slow update but have an index
Hi, I have a table with no relations to other tables. It has a sequence number field (integer) and a status field being a char. There is a unique index on the seqno field. Now this table has about 80,000 records. I need to update 74,000 status fields. So I made a dump, and hacked the dump into SQL statements like: update accounting set status = 'C' where seqno = 1566385; and the other 74,000 This is awfully slow. How come ? The index on the seqno should give speedy access to the record. Thanks, -- Feite Brekeveld [EMAIL PROTECTED] http://www.osiris-it.nl ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] slow update but have an index
On Fri, Aug 17, 2001 at 01:08:29PM +0200, Feite Brekeveld wrote: Now this table has about 80,000 records. I need to update 74,000 status fields. So I made a dump, and hacked the dump into SQL statements like: update accounting set status = 'C' where seqno = 1566385; and the other 74,000 This is awfully slow. How come ? The index on the seqno should give speedy access to the record. Well, an index speeds it up, but that times 80,000 will still take a while. Is there any trickery or will this work? update accounting set status = 'C'; If so, that will be much faster. One sequential scan is faster than 80,000 index scans. -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ It would be nice if someone came up with a certification system that actually separated those who can barely regurgitate what they crammed over the last few weeks from those who command secret ninja networking powers. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] slow update but have an index
Martijn van Oosterhout wrote: On Fri, Aug 17, 2001 at 01:08:29PM +0200, Feite Brekeveld wrote: Now this table has about 80,000 records. I need to update 74,000 status fields. So I made a dump, and hacked the dump into SQL statements like: update accounting set status = 'C' where seqno = 1566385; and the other 74,000 This is awfully slow. How come ? The index on the seqno should give speedy access to the record. Well, an index speeds it up, but that times 80,000 will still take a while. Is there any trickery or will this work? update accounting set status = 'C'; If so, that will be much faster. No that will not work, because they other 6000 need not to be changed. Of course I could update the this way and change the other 6000 back to their original status, but the query I issued is so slow that I think something is wrong. One sequential scan is faster than 80,000 index scans. -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ It would be nice if someone came up with a certification system that actually separated those who can barely regurgitate what they crammed over the last few weeks from those who command secret ninja networking powers. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- Feite Brekeveld [EMAIL PROTECTED] http://www.osiris-it.nl ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] slow update but have an index
On Fri, Aug 17, 2001 at 01:40:32PM +0200, Feite Brekeveld wrote: Well, an index speeds it up, but that times 80,000 will still take a while. Is there any trickery or will this work? update accounting set status = 'C'; If so, that will be much faster. No that will not work, because they other 6000 need not to be changed. Of course I could update the this way and change the other 6000 back to their original status, but the query I issued is so slow that I think something is wrong. Well, there's a bit of an issue here. Each time you do an insert, the table gets larger, the index gets larger, etc. Disk accesses everywhere. If you can do it one query then the sequential is much friendlier to disk caches and the performance will be much more consistant. Can you codify in an SQL query how you decide which records to change. I've found the best way to improve performance is to minimise the number of queries, letting the database do the maximum optimisation possible. -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ It would be nice if someone came up with a certification system that actually separated those who can barely regurgitate what they crammed over the last few weeks from those who command secret ninja networking powers. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] slow update but have an index
Martijn van Oosterhout wrote: On Fri, Aug 17, 2001 at 01:40:32PM +0200, Feite Brekeveld wrote: Well, an index speeds it up, but that times 80,000 will still take a while. Is there any trickery or will this work? update accounting set status = 'C'; If so, that will be much faster. No that will not work, because they other 6000 need not to be changed. Of course I could update the this way and change the other 6000 back to their original status, but the query I issued is so slow that I think something is wrong. Well, there's a bit of an issue here. Each time you do an insert, the table gets larger, the index gets larger, etc. Disk accesses everywhere. If you can do it one query then the sequential is much friendlier to disk caches and the performance will be much more consistant. Can you codify in an SQL query how you decide which records to change. I've found the best way to improve performance is to minimise the number of queries, letting the database do the maximum optimisation possible. hacked it with perl into several update ... where seqno between x and y statements. That went smoothly. -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ It would be nice if someone came up with a certification system that actually separated those who can barely regurgitate what they crammed over the last few weeks from those who command secret ninja networking powers. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Feite Brekeveld [EMAIL PROTECTED] http://www.osiris-it.nl ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
