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: http://www.postgresql.org/mailpref/pgsql-hackers