Re: making update/delete of inheritance trees scale better

On 31/10/2020 00:12, Tom Lane wrote:
> Heikki Linnakangas <hlinnaka(at)iki(dot)fi> writes:
>> .... But if you do:
>
>> postgres=# explain verbose update tab set a = 1, b = 2;
>> QUERY PLAN
>> ---------------------------------------------------------------------------------
>> Update on public.tab (cost=0.00..269603.27 rows=0 width=0)
>> -> Seq Scan on public.tab (cost=0.00..269603.27 rows=10028327
>> width=14)
>> Output: 1, 2, ctid
>
>> The Modify Table will still fetch the old tuple, but in this case, it's
>> not really necessary, because both columns are overwritten.
>
> Ah, that I believe. Not sure it's a common enough case to spend cycles
> looking for, though.
>
> In any case, we still have to access the old tuple, don't we?
> To lock it and update its t_ctid, whether or not we have use for
> its user columns. Maybe there's some gain from not having to
> deconstruct the tuple, but it doesn't seem like it'd be much.

Yeah, you need to access the old tuple to update its t_ctid, but
accessing it twice is still more expensive than accessing it once. Maybe
you could optimize it somewhat by keeping the buffer pinned or
something. Or push the responsibility down to the table AM, passing the
AM only the modified columns, and let the AM figure out how to deal with
the columns that were not modified, hoping that it can do something smart.

It's indeed not a big deal in usual cases. The test case I constructed
was deliberately bad, and the slowdown was only about 10%. I'm OK with
that, but if there's an easy way to avoid it, we should. (Seems like
there isn't.)

- Heikki