Re: pg_class.reltuples not reset by VACUUM?

On Tue, 2003-04-08 at 13:52, Tom Lane wrote:
> Jeff Boes <jboes(at)nexcerpt(dot)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