Обсуждение: 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