Grokbase › Groups › PostgreSQL › pgsql-novice › December 2000
FAQ
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.

Search Discussions

  • Tom Lane at Dec 7, 2000 at 3:43 pm ⇧

    Helge Bahmann 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
  • Helge Bahmann at Dec 7, 2000 at 5:15 pm ⇧

    On Thu, 7 Dec 2000, Tom Lane wrote:

    Helge Bahmann <[email protected]> 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.
  • Tom Lane at Dec 7, 2000 at 5:53 pm ⇧

    Helge Bahmann 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedDec 7, '00 at 2:13p
activeDec 7, '00 at 5:53p
posts4
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Tom Lane: 2 posts Helge Bahmann: 2 posts

Content

People

Support

Translate

site design / logo © 2023 Grokbase