Re: making update/delete of inheritance trees scale better

On 30/10/2020 23:10, Tom Lane wrote:
> Heikki Linnakangas <hlinnaka(at)iki(dot)fi> writes:
>> I also did some quick performance testing with a simple update designed
>> as a worst-case scenario:
>
>> vacuum tab; update tab set b = b, a = a;
>
>> In this case, the patch fetches the old tuple, but it wouldn't really
>> need to, because all the columns are updated. Could we optimize that
>> special case?
>
> I'm not following. We need to read the old values of a and b for
> the update source expressions, no?
>
> (One could imagine realizing that this is a no-op update, but that
> seems quite distinct from the problem at hand, and probably not
> worth the cycles.)

Ah, no, that's not what I meant. You do need to read the old values to
calculate the new ones, but if you update all the columns or if you
happened to read all the old values as part of the scan, then you don't
need to fetch the old tuple in the ModifyTable node.

Let's try better example. Currently with the patch:

postgres=# explain verbose update tab set a = 1;
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=10)
Output: 1, ctid

The Modify Table node will fetch the old tuple to get the value for 'b',
which is unchanged. 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.

- Heikki