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
________________________________
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.
