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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
