Re: Performance impact of updating target columns with unchanged values ON CONFLICT

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I take that question back – someone helped me on StackExchange and addressed it:

> It appears that Postgres is smart enough to identify cases where indexed columns are not changed , and perform HOT updates; thus , there is no difference between having or not having key columns in update statement from performance point of view. The only thing that matters it whether actual value changed. Surely, this behaviour is limited to B-Tree indexes.

https://dba.stackexchange.com/questions/223231/performance-impact-of-updating-target-columns-with-same-values-on-conflict

On Fri, Nov 23, 2018 at 7:44 PM Abi Noda <a@xxxxxxxxxxx> wrote:
Thanks Justin. Do you know if Postgres treats an UPDATE that sets the indexed columns set to the same previous values as a change? Or does it only count it as "changed" if the values are different. This is ambiguous to me.

> HOT solves this problem for a restricted but useful special case where a tuple is repeatedly updated in ways that do not change its indexed columns.

> With HOT, a new tuple placed on the same page and with all indexed columns the same as its parent row version does not get new index entries.

> [HOT] will create a new physical heap tuple when inserting, and not a new index tuple, if and only if the update did not affect indexed columns.



On Thu, Nov 22, 2018 at 2:40 PM Justin Pryzby <pryzby@xxxxxxxxxxxxx> wrote:
On Thu, Nov 22, 2018 at 01:31:10PM -0800, Abi Noda wrote:
> In other words, is Postgres smart enough to not actually write to disk any
> columns that haven’t changed value or update indexes based on those columns?

You're asking about what's referred to as Heap only tuples:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/heap/README.HOT;hb=HEAD
https://wiki.postgresql.org/wiki/Index-only_scans#Interaction_with_HOT

Note, if you're doing alot of updates, you should consider setting a lower the
table fillfactor, since HOT is only possible if the new tuple (row version) is
on the same page as the old tuple.

|With HOT, a new tuple placed on the same page and with all indexed columns the
|same as its parent row version does not get new index entries."

And check pg_stat_user_tables to verify that's working as intended.

Justin


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux