PostgreSQL索引失效會發生什麼

bill=# begin;
BEGIN
bill=*# create index idx_t1 on t1(id);
CREATE INDEX
bill=*# explain select * from t1 where id = 1;
                     QUERY PLAN
----------------------------------------------------
 Seq Scan on t1  (cost=0.00..25.88 rows=6 width=36)
   Filter: (id = 1)
(2 rows)

bill=*# end;
COMMIT
bill=# explain select * from t1 where id = 1;
                             QUERY PLAN
---------------------------------------------------------------------
 Bitmap Heap Scan on t1  (cost=1.50..7.01 rows=6 width=36)
   Recheck Cond: (id = 1)
   ->  Bitmap Index Scan on idx_t1  (cost=0.00..1.50 rows=6 width=0)
         Index Cond: (id = 1)
(4 rows)

bill=*# select indcheckxmin from pg_index where indexrelid = 'idx_t1'::regclass;
 indcheckxmin
--------------
 t
(1 row)

bill=# insert into t1 select generate_series(1,10),md5(random()::text);
INSERT 0 10

bill=# update t1 set info = 'bill' where id = 10;
UPDATE 1

/*此時indexInfo->ii_BrokenHotChain已被修改為true */	
if ((indexInfo->ii_BrokenHotChain || EarlyPruningEnabled(heapRelation)) &&
		!isreindex &&
		!indexInfo->ii_Concurrent)
	{
		Oid			indexId = RelationGetRelid(indexRelation);
		Relation	pg_index;
		HeapTuple	indexTuple;
		Form_pg_index indexForm;
		pg_index = table_open(IndexRelationId, RowExclusiveLock);
		indexTuple = SearchSysCacheCopy1(INDEXRELID,
										 ObjectIdGetDatum(indexId));
		if (!HeapTupleIsValid(indexTuple))
			elog(ERROR, "cache lookup failed for index %u", indexId);
		indexForm = (Form_pg_index) GETSTRUCT(indexTuple);
		/* If it's a new index, indcheckxmin shouldn't be set ... */
		Assert(!indexForm->indcheckxmin);
/*將indcheckxmin修改為true */
		indexForm->indcheckxmin = true;
		CatalogTupleUpdate(pg_index, &indexTuple->t_self, indexTuple);
		heap_freetuple(indexTuple);
		table_close(pg_index, RowExclusiveLock);
	}

bill=# drop table t1;
DROP TABLE
bill=# create table t1(id int,info text);
CREATE TABLE
bill=# begin;
BEGIN
bill=*# create index idx_t1 on t1(id);
CREATE INDEX
bill=*# select indcheckxmin from pg_index where indexrelid = 'idx_t1'::regclass;
 indcheckxmin
--------------
 t
(1 row)

bill=*# explain select * from t1 where id = 1;
                     QUERY PLAN
----------------------------------------------------
 Seq Scan on t1  (cost=0.00..25.88 rows=6 width=36)
   Filter: (id = 1)
(2 rows)

typedef struct SnapshotData
{
    SnapshotType snapshot_type; /* type of snapshot */
    /*
     * The remaining fields are used only for MVCC snapshots, and are normally
     * just zeroes in special snapshots.  (But xmin and xmax are used
     * specially by HeapTupleSatisfiesDirty, and xmin is used specially by
     * HeapTupleSatisfiesNonVacuumable.)
     *
     * An MVCC snapshot can never see the effects of XIDs >= xmax. It can see
     * the effects of all older XIDs except those listed in the snapshot. xmin
     * is stored as an optimization to avoid needing to search the XID arrays
     * for most tuples.
     */
    TransactionId xmin;         /* all XID < xmin are visible to me */
    TransactionId xmax;         /* all XID >= xmax are invisible to me */
    /*
     * For normal MVCC snapshot this contains the all xact IDs that are in
     * progress, unless the snapshot was taken during recovery in which case
     * it's empty. For historic MVCC snapshots, the meaning is inverted, i.e.
     * it contains *committed* transactions between xmin and xmax.
     *
     * note: all ids in xip[] satisfy xmin <= xip[i] < xmax
     */
    TransactionId *xip;
    uint32      xcnt;           /* # of xact ids in xip[] */
    /*
     * For non-historic MVCC snapshots, this contains subxact IDs that are in
     * progress (and other transactions that are in progress if taken during
     * recovery). For historic snapshot it contains *all* xids assigned to the
     * replayed transaction, including the toplevel xid.
     *
     * note: all ids in subxip[] are >= xmin, but we don't bother filtering
     * out any that are >= xmax
     */
    TransactionId *subxip;
    int32       subxcnt;        /* # of xact ids in subxip[] */
    bool        suboverflowed;  /* has the subxip array overflowed? */
    bool        takenDuringRecovery;    /* recovery-shaped snapshot? */
    bool        copied;         /* false if it's a static snapshot */
    CommandId   curcid;         /* in my xact, CID < curcid are visible */
    /*
     * An extra return value for HeapTupleSatisfiesDirty, not used in MVCC
     * snapshots.
     */
    uint32      speculativeToken;
    /*
     * For SNAPSHOT_NON_VACUUMABLE (and hopefully more in the future) this is
     * used to determine whether row could be vacuumed.
     */
    struct GlobalVisState *vistest;
    /*
     * Book-keeping information, used by the snapshot manager
     */
    uint32      active_count;   /* refcount on ActiveSnapshot stack */
    uint32      regd_count;     /* refcount on RegisteredSnapshots */
    pairingheap_node ph_node;   /* link in the RegisteredSnapshots heap */
    TimestampTz whenTaken;      /* timestamp when snapshot was taken */
    XLogRecPtr  lsn;            /* position in the WAL stream when taken */
    /*
     * The transaction completion count at the time GetSnapshotData() built
     * this snapshot. Allows to avoid re-computing static snapshots when no
     * transactions completed since the last GetSnapshotData().
     */
    uint64      snapXactCompletionCount;
} SnapshotData;

/*
 * Implement slower/larger portions of TestForOldSnapshot
 *
 * Smaller/faster portions are put inline, but the entire set of logic is too
 * big for that.
 */
void
TestForOldSnapshot_impl(Snapshot snapshot, Relation relation)
{
        if (RelationAllowsEarlyPruning(relation)
                && (snapshot)->whenTaken < GetOldSnapshotThresholdTimestamp())
                ereport(ERROR,
                                (errcode(ERRCODE_SNAPSHOT_TOO_OLD),
                                 errmsg("snapshot too old")));
}

 /*
     * At this moment we are sure that there are no transactions with the
     * table open for write that don't have this new index in their list of
     * indexes.  We have waited out all the existing transactions and any new
     * transaction will have the new index in its list, but the index is still
     * marked as "not-ready-for-inserts".  The index is consulted while
     * deciding HOT-safety though.  This arrangement ensures that no new HOT
     * chains can be created where the new tuple and the old tuple in the
     * chain have different index keys.
     *
     * We now take a new snapshot, and build the index using all tuples that
     * are visible in this snapshot.  We can be sure that any HOT updates to
     * these tuples will be compatible with the index, since any updates made
     * by transactions that didn't know about the index are now committed or
     * rolled back.  Thus, each visible tuple is either the end of its
     * HOT-chain or the extension of the chain is HOT-safe for this index.
     */