Re: FW: deleted records

On Wed, Dec 28, 2005 at 02:15:24PM +0100, H.J. Sanders wrote:
> is there a way to find out how many deleted records there are in
> a table (this gives an indication that it is time to vacuum)?

Use the pgstattuple() function from contrib/pgstattuple.

test=> CREATE TABLE foo (x integer);
CREATE TABLE
test=> INSERT INTO foo SELECT 1 FROM generate_series(1, 10000);
INSERT 0 10000
test=> DELETE FROM foo WHERE random() < 0.5;
DELETE 4957
test=> \x
Expanded display is on.
test=> SELECT * FROM pgstattuple('foo');
-[ RECORD 1 ]------+-------
table_len | 368640
tuple_count | 5043
tuple_len | 161376
tuple_percent | 43.78
dead_tuple_count | 4957
dead_tuple_len | 158624
dead_tuple_percent | 43.03
free_space | 7736
free_percent | 2.1

--
Michael Fuhr