Re: partial heap only tuples - Mailing list pgsql-hackers

On 2/10/21, 2:43 PM, "Bruce Momjian" <bruce@momjian.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