Thread: FW: deleted records

  Hi Folks.

  Couldn't find it in any mailing list so:

  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)?

  Many thanks

  Henk Sanders

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

On Wed, Jan 25, 2006 at 11:42:04AM +0100, H.J. Sanders wrote:
> When I do this I get the message
>
> relation pgstattuple does not exist.

pgstattuple is a function, not a relation, so I suspect the query
isn't referring to it correctly.  What's the exact query you ran?
It should look like this:

SELECT * FROM pgstattuple('tablename');

Did you install the contrib/pgstattuple module?

--
Michael Fuhr