Re: Batch update query performance

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: Batch update query performance
Дата
Msg-id CAMkU=1yBGZqUf9KPo-mRDkzpUScR+Bq_bDcySwxdf+GBrKLHWg@mail.gmail.com
обсуждение исходный текст
Ответ на Batch update query performance  (Hans Drexler <Hans.Drexler@HumanInference.com>)
Список pgsql-performance
On Fri, Apr 4, 2014 at 5:00 AM, Hans Drexler <Hans.Drexler@humaninference.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

В списке pgsql-performance по дате отправления:

Предыдущее
От: uher dslij
Дата:
Сообщение: Performance regressions in PG 9.3 vs PG 9.0
Следующее
От: "Manoj Gadi"
Дата:
Сообщение: Nested loop issue