Re: Batch update query performance

On Fri, Apr 4, 2014 at 5:00 AM, Hans Drexler <
Hans(dot)Drexler(at)humaninference(dot)com> wrote:

>
> update t67cdi_nl_cmp_descr set is_grc_002='Y'
>
> This post contains the data of two runs of the query. the first with
> explain analyze. The second run is with explain buffers. Between the
> runs, an explicit Vacuum Analyze was done on the table.
>
> Observations
> We tried removing the index on the field is_grc_002. That did not have a
> big impact.
>

To benefit from HOT update, you need both spare room in the table, and to
not have an index on the updated column.
So just dropping the index is probably not enough for a full-table update
as you don't have the spare room. You also have to populate the table with
a lower fillfactor, as has already been noted, as well as dropping the
index.

Is this update a one-time thing, or does the application do it on a regular
basis?

Cheers,

Jeff