Vacuum, analyze, and setting reltuples of pg_class

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Short version: is it optimal for vacuum to always populate reltuples
with live rows + dead rows?

I came across a problem in which I noticed that a vacuum did not change
the reltuples value as I expected. A vacuum analyze indicated a correct
estimated number of rows, but the number put into reltuples was not
similar. Running analyze alone did put a more accurate number. After
some IRC talk and digging through the code, it appears that because the
system is busy, the dead rows could not be removed at that time, and
vacuum (and vacuum analyze) (and vacuum full analyze) uses the number of
live rows + dead rows to populate reltuples. Are there any alternatives to
running analyze outside of vacuum every time to ensure a better count? Is
there serious drawbacks in vacuum using the live versus the live vs. dead?
Is there any way to encourage those dead rows to go away, or to figure out what
is preventing them from being reaped? This is cluster-wide, and happens
even on newly created tables, but here is a real-life example on a busy table:

greg=# select reltuples, relpages from pg_class where relname = 'q';reltuples | relpages
- -----------+----------      970 |     5724

greg=# select count(*) from q;count
- -------  979

greg=# vacuum q;
VACUUM

greg=# select reltuples, relpages from pg_class where relname = 'q';reltuples | relpages
- -----------+----------     2100 |     5724

greg=# vacuum full analyze q;
VACUUM

greg=# select reltuples, relpages from pg_class where relname = 'q';reltuples | relpages
- -----------+----------     2116 |     5724

greg=# analyze q;
ANALYZE

greg=# select reltuples, relpages from pg_class where relname = 'q';reltuples | relpages
- -----------+----------      897 |     5724


We've got much bigger tables that are affected worse than the example
above, of course. I'm pretty sure this is what Jeff Boes was experiencing
in 7.2, from this old thread:

http://svr5.postgresql.org/pgsql-bugs/2002-10/msg00138.php

I presume that the non-duplication was because Tom's database was not
so busy as to have dead rows laying around at the end of the vacuum
runs.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200612111128
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFFfYq4vJuQZxSWSsgRAtoZAKDngqVnt77SLXmp/nvuOnUGfoEMOgCcD8lE
jjB7atW6824o6vd85wl6+ps=
=O7N/
-----END PGP SIGNATURE-----