On Thu, Aug  4, 2016 at 11:53:05PM -0300, Claudio Freire wrote:
> The point is avoiding duplicate rows in the output of index scans.
> I don't think you can avoid it simply by applying index condition
> rechecks as the original proposal implies, in this case:
> CREATE TABLE t (id integer not null primary key, someid integer, dat integer);
> CREATE INDEX i1 ON t (someid);
> INSERT INTO t (id, someid, dat) VALUES (1, 2, 100);

OK, let me run through this and you can tell me where I am wrong.

At this point there are two indexes, one on 'id' and one on 'someid'.

> UPDATE t SET dat = dat + 1 where id = 1;

This is a HOT update because no indexes were changed.

> UPDATE t SET dat = dat + 1, id = 2 where id = 1;

This changes the HOT chain to a WARM chain because one index is changed.
That means that lookups on both indexes recheck the single visible
tuple, if there is one.

> UPDATE t SET dat = dat + 1, id = 3, someid = 3 where id = 2;

This is ends the WARM chain, and creates new index entries because all
indexes are changed.

> UPDATE t SET dat = dat + 1, id = 1, someid = 2 where id = 3;

This does the same thing.

> SELECT * FROM t WHERE someid = 2;

This uses the 'someid' index.  The index contains three entries:

        1. {someid=2} pointing to first WARM chain
        2. {someid=3} pointing to single tuple (no HOT chain)
        3. {someid=2} pointing to single tuple (no HOT chain)

The scan of #1 returns no visible rows.  #2 doesn't match the value in
the WHERE clause, so we don't even check the heap.  The scan of #3
returns one row.

Remember, we don't scan past the end of the HOT chain, which is what we
do now.

  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