Re: Fwd: Clarification about HOT - Mailing list pgsql-hackers

Gokulakannan Somasundaram wrote:
> On 11/5/07, Heikki Linnakangas <heikki@enterprisedb.com> wrote:
>> AFAICS, pointer-swinging would be exactly the same on cross-page HOT
>> chains as same-page chains.
> 
> When i read pointer-swinging, it talks a lot about in-page updates, pointing
> to the latest tuple instead of oldest tuple and circular pointers etc.
> Maybe, if i am missing the post, which you are referring to, please correct
> me on the steps i am talking about.

It talks about in-page updates etc, because we are only doing HOT 
updates within page. The pointer-swinging would still work across page 
boundaries, AFAICS.

> This scheme, if implemented would avoid the use of HOT stub and avoids the
> need to go to index, even if some other indexes are getting updated/ even if
> the updated new tuple goes into a new page

You'd still have the HOT stubs until the next VACUUM.

> a) Whenever we update, we will update only those indexes whose attributes
> are updated. So the index will point to the top of the HOT chain.

I don't see how pointer-swinging would solve the problem with updating 
just some indexes. On vacuum, you'd have to know which indexes were 
updated, and remove the old pointers in the ones that were updated, and 
pointer-swing others. You could store that information in the heap 
tuple, but that means more bloat in the heap, and more complexity.

We did talk about that back in spring as well, but all the suggestions 
were quite complex. Besides, as soon as you insert at least one new 
index pointer, you no longer can remove the dead HOT updated tuple 
without scanning at least that one index.

> b) Whenever we Vacuum the index, we take a list of tids and check whether
> there are any index tuples pointing to it. If the Vacuumed tuple is a start
> of the HOT chain, then we will carry the next in-line HOT tuple when we goto
> Vacuum the index. If the next in-line also satisfies the Vacuum, it will
> carry with it the next in-line HOT tuple-id.

Sorry, I didn't understand that. But the way you described it earlier, 
it's exactly the same thing as the pointer-swinging we talked about in 
spring. Is it the same or not?

> This will remove the in-page pruning exercises, but as i said already the
> cost of updates will go down a lot with normal indexes.

We don't want to get rid of the in-page pruning. It allows us to reclaim 
dead space without having to VACUUM. That's a major point of HOT.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com