VACUUM ANALYZE differs under 7.2.3 from 7.2.1

Started by Jeff Boesover 23 years ago2 messagesbugs
Jump to latest
#1Jeff Boes
jboes@nexcerpt.com

The "VACUUM ANALYZE tablename" command does not have the same effect on
table metadata under 7.2.1 and 7.2.3. In particular, I've noted that
pg_class.reltuples is not updated by vacuuming after a delete.

Here is a sequence of SQL commands to demonstrate the difference. Under
7.2.1, the resulting last three displays of the pg_class.reltuples value
will be zero (0), while under 7.2.3 it will be 10.

create table foo (a char(1));

copy foo from stdin;

a

a

a

a

a

a

a

a

a

a

\.

\x

\t

select count(*) from foo;

select reltuples from pg_class where relname = 'foo';

vacuum foo;

select reltuples from pg_class where relname = 'foo';

vacuum analyze foo;

select reltuples from pg_class where relname = 'foo';

analyze foo;

select reltuples from pg_class where relname = 'foo';

delete from foo;

select reltuples from pg_class where relname = 'foo';

select count(*) from foo;

select reltuples from pg_class where relname = 'foo';

vacuum foo;

select reltuples from pg_class where relname = 'foo';

vacuum analyze foo;

select reltuples from pg_class where relname = 'foo';

analyze foo;

select reltuples from pg_class where relname = 'foo';

drop table foo;

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Jeff Boes (#1)
Re: VACUUM ANALYZE differs under 7.2.3 from 7.2.1

Jeff Boes <jboes@nexcerpt.com> writes:

The "VACUUM ANALYZE tablename" command does not have the same effect on
table metadata under 7.2.1 and 7.2.3. In particular, I've noted that
pg_class.reltuples is not updated by vacuuming after a delete.

Hard to believe ... I see no changes in the 7.2.* CVS logs that might
cause such a problem.

Here is a sequence of SQL commands to demonstrate the difference. Under
7.2.1, the resulting last three displays of the pg_class.reltuples value
will be zero (0), while under 7.2.3 it will be 10.

Here I get zeroes, as you expect, with 7.2.3. Some problem with your
installation perhaps?

regards, tom lane