Skip site navigation (1) Skip section navigation (2)

Re: "analyze" putting wrong reltuples in pg_class

From: Ron Mayer <ron(at)intervideo(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: "analyze" putting wrong reltuples in pg_class
Date: 2002-08-03 01:21:03
Message-ID: Pine.LNX.4.33.0208021740280.13301-100000@ron (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
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
	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.

> 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
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. :-)


In response to


pgsql-bugs by date

Next:From: Tom LaneDate: 2002-08-03 03:28:54
Subject: Re: "analyze" putting wrong reltuples in pg_class
Previous:From: Tom LaneDate: 2002-08-02 22:50:44
Subject: Re: "analyze" putting wrong reltuples in pg_class

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group