Re: slow update but have an index
От | Tod McQuillin |
---|---|
Тема | Re: slow update but have an index |
Дата | |
Msg-id | Pine.GSO.4.33.0108172233370.22208-100000@sysadmin обсуждение исходный текст |
Ответ на | slow update but have an index (Feite Brekeveld <feite.brekeveld@osiris-it.nl>) |
Список | pgsql-general |
On Fri, 17 Aug 2001, Feite Brekeveld wrote: > 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. Unless you are putting all these updates in one transaction, each UPDATE will be in its own separate transaction. This means you get per-transaction overhead on every query. I bet you will find this runs a lot faster if you 'BEGIN TRANSACTION' before all the updates and 'COMMIT TRANSACTION' at the end. -- Tod McQuillin
В списке pgsql-general по дате отправления: