Re: Index creation

Helge Bahmann <bahmann(at)math(dot)tu-freiberg(dot)de> writes:
> I got a strange messange wrt indexes today:

> dhcp=# create index idx_fqhname on hosts using hash(hostname);
> CREATE
> dhcp=# vacuum analyze;
> NOTICE: Index tmp: NUMBER OF INDEX' TUPLES (61) IS NOT THE SAME AS HEAP'
> (62). Recreate the index.

If you have a long-running transaction in some other backend, this
behavior isn't too surprising. The extra heap tuple was probably
deleted since the long-running transaction started, and hence isn't
reflected in the index. But vacuum is unable to remove it completely,
because that old transaction could still see it under MVCC rules. The
cross-check between index and heap tuple counts isn't very bright about
this situation.

If you see this in an otherwise-idle system, then it might be worth
worrying about...

regards, tom lane