Re: Update big table

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 using JOIN 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(dot)romano(at)notorand(dot)it]
Sent: Sunday, 14 July 2013 9:03 PM
To: Haiming Zhang
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Update big table

2013/7/14 Haiming Zhang <Haiming(dot)Zhang(at)redflex(dot)com(dot)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 by email. 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 privileged information and/or copyright material of Redflex or third parties. You should only retransmit, distribute or commercialise the material if you are authorised to do so. This notice should not be removed.