Re: Expanding HOT updates for expression and partial indexes

Matthias,

Thanks for the in-depth review, you are correct and I appreciate you uncovering that oversight with summarizing indexes. I’ll add a test case and modify the logic to prevent updates to unchanged summarizing indexes by testing their attributes against the modified set while keeping the HOT optimization when only summarizing indexes are changed.

thanks for finding this,

-greg

> On Feb 11, 2025, at 4:40 PM, Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com> wrote:
>
> On Mon, 10 Feb 2025 at 19:15, Burd, Greg <gregburd(at)amazon(dot)com> wrote:
>>
>> Apologies for not being clear, this preserves the current behavior for summarizing indexes allowing for HOT updates while also updating the index. No degradation here that I’m aware of, indeed the tests that ensure that behavior are unchanged and pass.
>
> Looking at the code again, while it does indeed preserve the current
> behaviour, it doesn't actually improve the behavior for summarizing
> indexes when that would be expected.
>
> Example:
>
> CREATE INDEX hotblocking ON mytab USING btree((att1->'data'));
> CREATE INDEX summarizing ON mytab USING BRIN(att2);
> UPDATE mytab SET att1 = att1 || '{"check": "mate"}';
>
> In v3 (same code present in v4), I notice that in the above case we
> hit the "indexed attribute updated" path (hotblocking indeed indexes
> the updated attribute att1), go into ExecIndexesRequiringUpdates, and
> mark index 'summarizing' as 'needs an update', even though no
> attribute of that index has a new value. Then we notice that
> att1->'data' hasn't changed, and so we don't need to update the
> 'hotblocking' index, but we do update the (unchanged) 'summarizing'
> index.
>
> This indicates that in practice (with this version of the patch) this
> will improve the HOT applicability situation while summarizing indexes
> don't really gain a benefit from this - they're always updated when
> any indexed column is updated, even if we could detect that there were
> no changes to any indexed values.
>
> Actually, you could say we find ourselves in the counter-intuitive
> situation that the addition of the 'hotblocking' index whose value
> were not updated now caused index insertions into summarizing indexes.
>
> Kind regards,
>
> Matthias van de Meent
> Neon (https://neon.tech)