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 chain2. {someid=3} pointing to single tuple (no HOT chain)3. {someid=2} pointing
tosingle 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 <bruce@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 +
