Re: making update/delete of inheritance trees scale better

On 31/10/2020 00:12, Tom Lane wrote:
> Heikki Linnakangas <hlinnaka@iki.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