Обсуждение: Index creation

Hi,

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.

Should I be worried? I can drop the index, but every time I recreate it,
the same message.

Postgres version 7.0.2.

Helge
--
This signature is intentionally left blank.

Helge Bahmann <bahmann@math.tu-freiberg.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

On Thu, 7 Dec 2000, Tom Lane wrote:

> Helge Bahmann <bahmann@math.tu-freiberg.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.
>
[snip]
> If you see this in an otherwise-idle system, then it might be worth
> worrying about...

I retried, this time the system was completely idle, only one backend
running (me). Same message. What should I do?

Sidenote: I have several other indices on the table which appear to be
unaffected.

Thanks for you quick reply,
Helge
--
This signature is intentionally left blank.

Helge Bahmann <bahmann@math.tu-freiberg.de> writes:
>>>> 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.
>>
> [snip]
>> If you see this in an otherwise-idle system, then it might be worth
>> worrying about...

> I retried, this time the system was completely idle, only one backend
> running (me). Same message. What should I do?

Oh, I hadn't noticed before that you were using a hash index.  I'll bet
there's one tuple in the table that has a NULL hostname.  Hash doesn't
index nulls ... but I don't think vacuum's count cross-check knows that.

            regards, tom lane