Thread: 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