Re: batch update query performance

Dear Jeff, Albe and Heikki,

Let me start by thanking you for your time. It is really nice to have a
real supportive community. Thank you.

After reading the answers, we decided to do an experiment with a
fillfactor of 40% and dropping the index on the is_grc_002 field (but
retaining the other indexes.) The experiment showed a reduction in
run-time to ~125 seconds. That is almost 25 times faster than it was. We
are now doing more tests to verify this fix. We will send a SOLVED
message when the fix is verified (unless you state to not bother...)

We think we understand why the improvement works. Let me state our
understanding here. Please comment if we got it wrong.

Index entries point to record pages. An update on a row results in a new
row instance. If the new instance can be written in the same page as the
old instance, then no indexes need to be updated because the index still
points to the correct page. (Unless the update itself modifies an
indexed value). By specifying a fillfactor of 40%, there will be room
for an updated version of each row in the page.

We assume (sorry) that vacuuming the table will release the space of the
old rows, so that we can again do an update query and reuse the freed up
space in the pages.

Jeff, answering your question: The update is done after each cycle. It
will actually also update rows that were already updated before. We
realize this is actually wasteful.


So we might change

update t67cdi_nl_cmp_descr set is_grc_002='Y'

to

update t67cdi_nl_cmp_descr set is_grc_002='Y' where is_grc_002 is null

This will avoid creating new records for records that where already
changed before. This might give us additional speed improvement.