Re: partial heap only tuples

On 2/10/21, 2:43 PM, "Bruce Momjian" <bruce(at)momjian(dot)us> wrote:
> On Tue, Feb 9, 2021 at 06:48:21PM +0000, Bossart, Nathan wrote:
>> HOT works wonders when no indexed columns are updated. However, as
>> soon as you touch one indexed column, you lose that optimization
>> entirely, as you must update every index on the table. The resulting
>> performance impact is a pain point for many of our (AWS's) enterprise
>> customers, so we'd like to lend a hand for some improvements in this
>> area. For workloads involving a lot of columns and a lot of indexes,
>> an optimization like PHOT can make a huge difference. I'm aware that
>> there was a previous attempt a few years ago to add a similar
>> optimization called WARM [0] [1]. However, I only noticed this
>> previous effort after coming up with the design for PHOT, so I ended
>> up taking a slightly different approach. I am also aware of a couple
>> of recent nbtree improvements that may mitigate some of the impact of
>> non-HOT updates [2] [3], but I am hoping that PHOT serves as a nice
>> complement to those. I've attached a very early proof-of-concept
>> patch with the design described below.
>
> How is your approach different from those of [0] and [1]? It is
> interesting you still see performance benefits even after the btree
> duplication improvements. Did you test with those improvements?

I believe one of the main differences is that index tuples will point
to the corresponding PHOT tuple instead of the root of the HOT/PHOT
chain. I'm sure there are other differences. I plan on giving those
two long threads another read-through in the near future.

I made sure that the btree duplication improvements were applied for
my benchmarking. IIUC those don't alleviate the requirement that you
insert all index tuples for non-HOT updates, so PHOT can still provide
some added benefits there.

>> Next, I'll go into the design a bit. I've commandeered the two
>> remaining bits in t_infomask2 to use as HEAP_PHOT_UPDATED and
>> HEAP_PHOT_TUPLE. These are analogous to the HEAP_HOT_UPDATED and
>> HEAP_ONLY_TUPLE bits. (If there are concerns about exhausting the
>> t_infomask2 bits, I think we could only use one of the remaining bits
>> as a "modifier" bit on the HOT ones. I opted against that for the
>> proof-of-concept patch to keep things simple.) When creating a PHOT
>> tuple, we only create new index tuples for updated columns. These new
>> index tuples point to the PHOT tuple. Following is a simple
>> demonstration with a table with two integer columns, each with its own
>> index:
>
> Whatever solution you have, you have to be able to handle
> adding/removing columns, and adding/removing indexes.

I admittedly have not thought too much about the implications of
adding/removing columns and indexes for PHOT yet, but that's
definitely an important part of this project that I need to look into.
I see that HOT has some special handling for commands like CREATE
INDEX that I can reference.

>> When it is time to scan through a PHOT chain, there are a couple of
>> things to account for. Sequential scans work out-of-the-box thanks to
>> the visibility rules, but other types of scans like index scans
>> require additional checks. If you encounter a PHOT chain when
>> performing an index scan, you should only continue following the chain
>> as long as none of the columns the index indexes are modified. If the
>> scan does encounter such a modification, we stop following the chain
>> and continue with the index scan. Even if there is a tuple in that
>
> I think in patch [0] and [1], if an index column changes, all the
> indexes had to be inserted into, while you seem to require inserts only
> into the index that needs it. Is that correct?

Right, PHOT only requires new index tuples for the modified columns.
However, I was under the impression that WARM aimed to do the same
thing. I might be misunderstanding your question.

> I wonder if you should create a Postgres wiki page to document all of
> this. I agree PG 15 makes sense. I would like to help with this if I
> can. I will need to study this email more later.

Thanks for taking a look. I think a wiki is a good idea for keeping
track of the current state of the design. I'll look into that.

Nathan