Re: pg_class.reltuples not reset by VACUUM?

On Tue, 2003-04-08 at 13:52, Tom Lane wrote:
> Jeff Boes <jboes@nexcerpt.com> writes:
> > I thought that VACUUM ANALYZE would always restore some sense of reality
> > to the internal statistics for a table.  However ...
>
> Could we see the output of VACUUM VERBOSE for that table?

No, not since I've dropped and recreated it...

>
> I suspect you have lots of dead-but-not-yet-reclaimable tuples in the
> table, presumably because there is some very old transaction lurking
> in the background.
>

Well, here's the present state of the table, 24 hours later:

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

Aggregate  (cost=11248.76..11248.76 rows=1 width=0)
  ->  Seq Scan on job_queue  (cost=0.00..10357.81 rows=356381 width=0)

EXPLAIN

# select count(*) from job_queue;
 count
-------
  2369
(1 row)

# vacuum analyze verbose job_queue;

NOTICE:  --Relation job_queue--
NOTICE:  Pages 6831: Changed 2, Empty 0; Tup 358441: Vac 0, Keep 356048,
UnUsed 5.
    Total CPU 0.00s/0.15u sec elapsed 0.16 sec.
NOTICE:  --Relation pg_toast_292377168--
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.
NOTICE:  Analyzing job_queue
VACUUM

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

Aggregate  (cost=11311.51..11311.51 rows=1 width=0)
  ->  Seq Scan on job_queue  (cost=0.00..10415.41 rows=358441 width=0)

EXPLAIN

# analyze verbose job_queue;
NOTICE:  Analyzing job_queue
ANALYZE
xifos:~ # explain select count(*) from job_queue;
NOTICE:  QUERY PLAN:

Aggregate  (cost=6861.41..6861.41 rows=1 width=0)
  ->  Seq Scan on job_queue  (cost=0.00..6855.33 rows=2433 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