Heavily fragmented table and index data in 8.0.3

Hi,

we have a customer with PostgreSQL 8.0.3 with a quite interesting problem.
They have around 24 identical databases and all but one is working nicely.
The one that doesn't work nicely show this problem: INSERT "hangs"
on an apparently empty table where "select count(*)" returns 0 quite
quickly.

The relfilenodes of the table and its only (non-unique) index are below:

> ls -l ./17230/20387 ./17230/20382
-rw------- 1 postgres postgres 2727936 Jun 6 03:31 ./17230/20382
-rw------- 1 postgres postgres 630784 May 24 13:18 ./17230/20387

The machine is:

> uname -a
SunOS ihds00 5.10 Generic_125100-10 sun4u sparc SUNW,Netra-T12

The realtime trace I captured from the hung INSERT shows that it
enters two functions repeatedly: _bt_relandgetbuf() and _bt_compare().
The pattern in which these functions entered match either _bt_moveright() or
_bt_insertonpg(). Also, VACUUM FULL also takes too much time,
on an otherwise idle database, I worked on a copy of their live database.
During VACUUM, _bt_getbuf() was also called repeatedly with the
block number jumping up and down. Obviously the table data is very
fragmented. The total database size is around 366MB, the only client
at the time was VACUUM, both the table and the index fit easily into
shared_buffers at the same time.

I know, 8.0.3 is quite old. But nothing jumped out from the changelog
up to 8.0.15 that would explain this excessive slowness. SELECTs are
pretty fast on any of the tables I tried, but INSERT hangs on this table.
How does this fragmentation happen and how can we prevent this situation?

Best regards,
Zoltán Böszörményi

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/