On Fri, Aug 5, 2016 at 03:26:15PM -0400, Bruce Momjian wrote: > > I just don't know how would you do that without delaying/not-doing HOT chain > > prune. As soon as root and intermediate HOT tuples are gone, all > > information is > > lost. You may delay that, but that will defeat the whole purpose. If chains > > are > > not pruned in-time, you may not find any free space in the page and end up > > doing a cold update anyways. But may be I am missing something and Claudio > > has > > a different idea. > > Yes, pruning would be a problem. :-( > > A check only needs to happen when the indexed key changes, right? So we > are comparing the cost of adding an index key vs. the cost of trying to > find a matching key/ctid in the index. Seems the later is cheaper, but > it is not obvious.
Here is an illustration of the issue: CREATE TABLE test (col1 INTEGER, col2 INTEGER, col3 INTEGER); -- index first two columns CREATE INDEX i_test1 ON test (col1); CREATE INDEX i_test2 ON test (col2); INSERT INTO test VALUES (1, 7, 20); -- create a HOT chain UPDATE test SET col3 = 30; -- change the HOT chain to a WARM chain, changes i_test2 but not i_test1 UPDATE test SET col2 = 8; -- we should avoid adding a col2=7 i_test2 index tuple -- because we already have one; how do we know that? UPDATE test SET col2 = 7; -- we should see only one col2=7 i_test2 index tuple SELECT * FROM test WHERE col2 = 7; col1 | col2 | col3 ------+------+------ 1 | 7 | 30 -- Bruce Momjian <br...@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers