Re: "analyze" putting wrong reltuples in pg_class

On Fri, 2 Aug 2002, Tom Lane wrote:
>
> Ron Mayer <ron(at)intervideo(dot)com> writes:
> > On a number of my tables, "analyze" seems to be putting the wrong value of
> > "reltuples" in pg_class. "vacuum" seems to be doing the right thing.
>
> Hmm. analyze by itself generates only an approximate estimate of the
> row count (since it only examines a random sample of the rows). But I'd
> not have expected it to be off by a factor of 200. Need more info.
> What does VACUUM VERBOSE show?

logs2=# vacuum verbose e_ip_full;
NOTICE: --Relation e_ip_full--
NOTICE: Index e_ip__ip_obsolete_dat: Pages 15905; Tuples 1697755: Deleted
654680.
CPU 5.54s/9.96u sec elapsed 183.97 sec.
NOTICE: Index e_ip__domain: Pages 15891; Tuples 1697755: Deleted 654680.
CPU 10.51s/8.59u sec elapsed 255.68 sec.
NOTICE: Removed 654680 tuples in 8324 pages.
CPU 7.91s/1.91u sec elapsed 52.01 sec.
NOTICE: Pages 37612: Changed 0, Empty 0; Tup 1697755: Vac 654680, Keep 0,
UnUsed 454059.
Total CPU 42.91s/20.83u sec elapsed 570.05 sec.
NOTICE: --Relation pg_toast_110790174--
NOTICE: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
logs2=#

> Also, it would be interesting to see what contrib/pgstattuple shows,
> if you can run that conveniently.

Gladly, if I'm shown where to find it. Google search for pgstattuple shows
http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/pgstatuple/
which serves an error page rigth now.

> Can you say anything about your typical usage pattern on these tables?
> (eg, numbers of inserts vs updates vs deletes)

Every few days, 10,000 - 200,000 entries (new IP addresses) are loaded.

After loading, an update is run once for each entry (filling in the domain
name that goes with the IP address).

Then "Vacuum Analyze" is run, and no updates or loads happen until
the next large batch.

> BTW, it's quite likely that VACUUM FULL will make the problem go away,
> so don't do that until we fully understand what's happening ...

OK... I have 2 tables that have the same problem, so we can
experiment once. :-)

Ron