Message-ID: <4DD28F73.6010108@25th-floor.com>
Date: Tue, 17 May 2011 17:08:35 +0200
From: Florian Helmberger <fh@25th-floor.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US;
	rv:1.9.2.17) Gecko/20110414 Lightning/1.0b3pre Thunderbird/3.1.10
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: pg_class reltuples/relpages not updated by autovacuum/vacuum
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi.

I'm running a production database with PostgreSQL 9.0.3 (64-bit) on 
Debian 5.0.4 and have an issue with a TOAST table and far to frequent 
autovacuum runs.

I think I've pinned the problem down to the values pg_class holds for 
the affected TOAST table:

relpages  | 433596
reltuples | 1868538

These values are significantly too low. Interestingly, the autovacuum 
logout reports the correct values:

	pages: 0 removed, 34788136 remain
	tuples: 932487 removed, 69599038 remain

but these aren't stored in pg_class after each run.

Currently, there are no long running transactions and/or dumps running.

I've confirmed that PostgreSQL is using the values stored in pg_class 
for it's calculations, it starts autovacuum for the table at around
375k dead rows (threshold is 50, scale_factor 0.2 (both default)).

Additionally I've done manual VACUUM ANALYZE of both the parent table 
and the TOAST table, which didn't help either.

Other databases with the same hardware, PostgreSQL and OS versions don't 
have this issue.

Currently I've worked around the issue by disabling autovacuum for the 
TOAST table and doing manual VACUUM ANALYZE once a week.

Any clue how to get PostgreSQL to store the correct values?

Side note: while trying to debug this I've noticed, that the TOAST
chunks on 32-bit systems have the documented size of 2000 bytes, on 
64-bit systems they have 1996 bytes. Is this normal/on purpose?

Regards,
Florian Helmberger

-- 

Florian Helmberger --------------------

25th-floor - Operating Custom Solutions
de Pretis & Helmberger KG

Gluckgasse 2/6, 1010 Wien, Austria

Mail: fh@25th-floor.com
Web : http://www.25th-floor.com
Tel.: +43 1 / 512 82 89 - 60
Fax : +43 1 / 512 82 89 - 76
Mob.: +43 699 / 109 24 24 5
---------------------------------------