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