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:

Reply via email to