Tom Lane wrote:
Scott Shattuck <[EMAIL PROTECTED]> writes:Willing to learn here but skipping a vacuum full has caused some issues for us. Here's some data from a recent 3 day test run that was done with regular vacuums but not vacuum fulls. When running with vacuum full the indexes remain in line:
nsuite-10=# select relname, relpages, reltuples from pg_class where relname not like 'pg_%' order by reltuples desc;
-[ RECORD 1 ]------------------------------
relname | directory_fullpath_ix
relpages | 96012
reltuples | 1.38114e+06
-[ RECORD 2 ]------------------------------
relname | directory_pkey
relpages | 16618
reltuples | 1.38114e+06
-[ RECORD 3 ]------------------------------
relname | directory
relpages | 23924
reltuples | 59578
<snip>
<<blink>> There's no way that the index and table tuple counts should get that far out of line; in the absence of any concurrent updates, they should be *equal* (or index < table, if you have a partial index, which I assume these are not). I would credit the recorded index count exceeding the recorded table count by the number of tuples inserted/ updated while a (plain) VACUUM is in process on that table --- but this doesn't look like it meets that situation. There was a bug a long time ago wherein vacuum would forget to update pg_class.reltuples for indexes in some cases, but according to the CVS logs that was fixed before 7.2 release. What version are you running exactly?
test=# select version();
version
-------------------------------------------------------------
PostgreSQL 7.2.2 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)
test=#
In any case, you seem to be dealing with some kind of bug here. It might be helpful to look at the output of "vacuum verbose directory" if you still have it available.
NOTICE: --Relation directory--
NOTICE: Index directory_pkey: Pages 15628; Tuples 4988848: Deleted 35407.
CPU 0.73s/3.00u sec elapsed 40.53 sec.
NOTICE: Index directory_fullpath_ix: Pages 80808; Tuples 4989317: Deleted 35407.
CPU 4.84s/3.91u sec elapsed 275.66 sec.
NOTICE: Removed 35407 tuples in 786 pages.
CPU 0.13s/0.11u sec elapsed 1.80 sec.
NOTICE: Pages 80156: Changed 18, Empty 0; Tup 4988787: Vac 35407, Keep 4977704, UnUsed 348422.
Total CPU 7.85s/7.58u sec elapsed 343.84 sec.
regards, tom lane
Thanks for any insight you can offer here. ss ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
