Re: pg_class.reltuples not reset by VACUUM? - Mailing list pgsql-admin

On Tue, 2003-04-08 at 15:10, Tom Lane wrote:

> Yup, you definitely have a big problem with dead-but-unreclaimed tuples.
> Look for some client that's holding an open transaction for long
> periods.

It's even weirder than that:  note in the following how VACUUM sets the
tuple count high, and ANALYZE sets it low.  This appears to happen even
if there are no transactions open.

# explain select count(*) from job_queue;
NOTICE:  QUERY PLAN:

Aggregate  (cost=7064.26..7064.26 rows=1 width=0)
  ->  Seq Scan on job_queue  (cost=0.00..7056.81 rows=2981 width=0)

EXPLAIN
# vacuum job_queue;
VACUUM

# explain select count(*) from job_queue;
NOTICE:  QUERY PLAN:

Aggregate  (cost=11690.88..11690.88 rows=1 width=0)
  ->  Seq Scan on job_queue  (cost=0.00..10764.70 rows=370470 width=0)

EXPLAIN

# analyze job_queue;
ANALYZE

# explain select count(*) from job_queue;
NOTICE:  QUERY PLAN:

Aggregate  (cost=7097.34..7097.34 rows=1 width=0)
  ->  Seq Scan on job_queue  (cost=0.00..7089.87 rows=2987 width=0)

EXPLAIN


--
Jeff Boes                                      vox 269.226.9550 ext 24
Database Engineer                                     fax 269.349.9076
Nexcerpt, Inc.                                 http://www.nexcerpt.com
           ...Nexcerpt... Extend your Expertise