Re: Question about explain of index scan

On P, 2005-09-04 at 18:21 -0400, Tom Lane wrote:
> Hannu Krosing <hannu@skype.net> writes:
> > On R, 2005-09-02 at 11:03 -0400, Tom Lane wrote:
> >> I once started to make a btree opclass for XID, and stopped when it
> >> occurred to me that XID comparison doesn't obey the transitive law.
> >> btree won't like that...
> 
> > Does this mean that Slony's usage of btree index on XID gives
> > (occasionally) wrong results ?
> 
> I seem to recall some discussion of that in the archives (but can't find
> it right now).  If they do actually make btree indexes on XIDs then they
> are probably broken.
> 
> XID comparison works OK as long as you make sure that all the XIDs
> extant in the system at any one time are within +/- 2 billion of each
> other, and so transitivity does hold within that subset. 

The table itself is very dynamic, only a a few hundred thousand rows are
live at any time, consumed in FIFO fashion.

> The problem
> with a btree is that upper-level tree nodes are likely to contain page
> boundary keys copied from data that vanished some time ago from the
> underlying table.

So a reindex / cluster every now and then should keep it usable ?

> VACUUM-like techniques can guarantee that the
> underlying table is free of old XIDs before the wraparound horizon is
> reached, but I don't know how much extra safety margin is needed to
> guarantee no inconsistencies inside a btree index (if indeed any such
> guarantee is possible at all).

I'm not sure how the ordering by xid works in slony though - after a
wraparound has happened, it seems very hard to determine the first XID,
even though the ordering between any two XID's may be ok.

-- 
Hannu Krosing <hannu@skype.net>