Re: Update big table
От | Haiming Zhang |
---|---|
Тема | Re: Update big table |
Дата | |
Msg-id | 8AE6CD7104B80845B0732DAC65C8B6294F746018E7@rts-exchange1.traffic.redflex.com.au обсуждение исходный текст |
Ответ на | Re: Update big table (Vincenzo Romano <vincenzo.romano@notorand.it>) |
Ответы |
Re: Update big table
(bricklen <bricklen@gmail.com>)
|
Список | pgsql-general |
Hi, Thanks for your reply. Yes you are right, IN predicate is the cause but the JOIN does not help much. I run my query usingJOIN for two hours, and did not get it done. Here is my query: update table1 set col1 = true from table2 where table1.event_id = table2.event_id Regards, Haiming -----Original Message----- From: Vincenzo Romano [mailto:vincenzo.romano@notorand.it] Sent: Sunday, 14 July 2013 9:03 PM To: Haiming Zhang Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Update big table 2013/7/14 Haiming Zhang <Haiming.Zhang@redflex.com.au>: > Hi All, > > > > I am using postgres 9.1, I have a question about updating big table. > Here is the basic information of this table. > > 1. This table has one primary key and other 11 columns. > > 2. It also has a trigger that before update records, > another table got updated first. > > 3. The has millions of records now. > 4. I am not allowed to delete records in this table > when UPDATE > > The problem is when I do a "Update" query it takes a long time to execute. > Eg. when I run query like this " update TABLE set column1 = true where > EVENT_ID in (select EVENT_ID from TABLE2 );" , it took hours to update > the whole table. In order to optimize the update speed. I tried the > following > strategies: > > 1. create index based on primary key, column1 and > combination of primary key and column1. > > 2. Alter FILLFACTOR = 70, vacuum all and then reindex > > 3. drop trigger before update > > Then I use "EXPLAIN" to estimate query plan, all of the above > strategies do not improve the UPDATE speed dramatically. > > > > Please comments on my three strategies (eg, does I index too many > columns in > 1?) and please advise me how to improve the update speed. Any advice > is welcomed. I appreciate all you help. > > > > Thanks, > > > > Regards, > > Haiming A JOIN would solve your speed problem. The IN() predicate is the cause. AFAIK. If you are not an authorised recipient of this e-mail, please contact me at Redflex immediately by return phone call or byemail. In this case, you should not read, print, retransmit, store or act in reliance on this e-mail or any attachments,and should destroy all copies of them. This e-mail and any attachments are confidential and may contain privilegedinformation and/or copyright material of Redflex or third parties. You should only retransmit, distribute or commercialisethe material if you are authorised to do so. This notice should not be removed.
В списке pgsql-general по дате отправления: