VACUUM writes totally bogus tuple counts into pg_class

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-bugs(at)postgreSQL(dot)org
Subject: VACUUM writes totally bogus tuple counts into pg_class
Date: 2009-06-04 21:16:01
Message-ID: 19271.1244150161@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

I happened to notice the following misbehavior in CVS HEAD while poking
at Tatsuo's GIN problem. Immediately after loading the data, a
manual VACUUM behaves reasonably:

ishii=# select count(*) from msginfo;
count
-------
10108
(1 row)

ishii=# vacuum verbose msginfo;
INFO: vacuuming "public.msginfo"
INFO: index "msginfo_hdr_msgid_key" now contains 10108 row versions in 119 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.04 sec.
INFO: index "msginfo_pkey" now contains 10108 row versions in 44 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.04 sec.
INFO: index "msginfo_body_index" now contains 10108 row versions in 5356 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 2870 are currently reusable.
CPU 0.12s/0.03u sec elapsed 1.34 sec.
INFO: index "msginfo_msg_date_index" now contains 10108 row versions in 44 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.03 sec.
INFO: "msginfo": found 0 removable, 10108 nonremovable row versions in 1751 out of 1751 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.22s/0.06u sec elapsed 2.19 sec.
INFO: vacuuming "pg_toast.pg_toast_24216"
INFO: index "pg_toast_24216_index" now contains 15041 row versions in 43 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_24216": found 0 removable, 15041 nonremovable row versions in 3045 out of 3045 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.11s/0.05u sec elapsed 1.87 sec.
VACUUM

But try doing it a second time:

ishii=# vacuum verbose msginfo;
INFO: vacuuming "public.msginfo"
INFO: index "msginfo_hdr_msgid_key" now contains 196 row versions in 119 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.04 sec.
INFO: index "msginfo_pkey" now contains 196 row versions in 44 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.03 sec.
INFO: index "msginfo_body_index" now contains 196 row versions in 5356 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 2870 are currently reusable.
CPU 0.13s/0.03u sec elapsed 1.79 sec.
INFO: index "msginfo_msg_date_index" now contains 196 row versions in 44 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.03 sec.
INFO: "msginfo": found 0 removable, 196 nonremovable row versions in 31 out of 1751 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.13s/0.04u sec elapsed 1.90 sec.
INFO: vacuuming "pg_toast.pg_toast_24216"
INFO: index "pg_toast_24216_index" now contains 134 row versions in 43 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_24216": found 0 removable, 134 nonremovable row versions in 31 out of 3045 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.02 sec.
VACUUM

and these ridiculous numbers have also been stuck into
pg_class.reltuples, at least in the case of the indexes:

ishii=# select relname,relpages,reltuples from pg_class where relnamespace = 2200;
relname | relpages | reltuples
------------------------+----------+-----------
msg_folderinfo | 95 | 10108
msg_folderinfo_pkey | 75 | 10108
msg_sid_index | 30 | 10108
msginfo | 1751 | 10108
msginfo_pkey | 44 | 196
msginfo_hdr_msgid_key | 119 | 196
msginfo_body_index | 5356 | 196
msginfo_msg_date_index | 44 | 196
msginfo_msg_sid_seq | 1 | 1
(9 rows)

I assume this is some side-effect of partial vacuum. The misleading
VACUUM VERBOSE output is bad enough, but messing up the reltuples
counts is going to confuse the heck out of the planner.

regards, tom lane

Browse pgsql-bugs by date

  From Date Subject
Next Message jeewan 2009-06-06 06:38:48 BUG #4841: like and trim queries
Previous Message Tom Lane 2009-06-04 20:53:24 Re: BUG #4840: Different output from Transform function when used in a function vs. directly