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