VACUUM writes totally bogus tuple counts into pg_class

Поиск
Список
Период
Сортировка
От Tom Lane
Тема VACUUM writes totally bogus tuple counts into pg_class
Дата
Msg-id 19271.1244150161@sss.pgh.pa.us
обсуждение исходный текст
Список 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

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: BUG #4840: Different output from Transform function when used in a function vs. directly
Следующее
От: "jeewan"
Дата:
Сообщение: BUG #4841: like and trim queries