blob: 8b35ae32a1281bf00fe4fca59bdc01b47a1fe07f (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
|
pgstattuple README 2002/08/29 Tatsuo Ishii
1. Functions supported:
pgstattuple
-----------
pgstattuple() returns the relation length, percentage of the "dead"
tuples of a relation and other info. This may help users to determine
whether vacuum is necessary or not. Here is an example session:
test=> \x
Expanded display is on.
test=> SELECT * FROM pgstattuple('pg_catalog.pg_proc');
-[ RECORD 1 ]------+-------
table_len | 458752
tuple_count | 1470
tuple_len | 438896
tuple_percent | 95.67
dead_tuple_count | 11
dead_tuple_len | 3157
dead_tuple_percent | 0.69
free_space | 8932
free_percent | 1.95
Here are explanations for each column:
table_len -- physical relation length in bytes
tuple_count -- number of live tuples
tuple_len -- total tuples length in bytes
tuple_percent -- live tuples in %
dead_tuple_len -- total dead tuples length in bytes
dead_tuple_percent -- dead tuples in %
free_space -- free space in bytes
free_percent -- free space in %
pg_relpages
-----------
pg_relpages() returns the number of pages in the relation.
pgstatindex
-----------
pgstatindex() returns an array showing the information about an index:
test=> \x
Expanded display is on.
test=> SELECT * FROM pgstatindex('pg_cast_oid_index');
-[ RECORD 1 ]------+------
version | 2
tree_level | 0
index_size | 8192
root_block_no | 1
internal_pages | 0
leaf_pages | 1
empty_pages | 0
deleted_pages | 0
avg_leaf_density | 50.27
leaf_fragmentation | 0
2. Installing pgstattuple
$ make
$ make install
$ psql -e -f /usr/local/pgsql/share/contrib/pgstattuple.sql test
3. Using pgstattuple
pgstattuple may be called as a relation function and is
defined as follows:
CREATE OR REPLACE FUNCTION pgstattuple(text) RETURNS pgstattuple_type
AS 'MODULE_PATHNAME', 'pgstattuple'
LANGUAGE C STRICT;
CREATE OR REPLACE FUNCTION pgstattuple(oid) RETURNS pgstattuple_type
AS 'MODULE_PATHNAME', 'pgstattuplebyid'
LANGUAGE C STRICT;
The argument is the relation name (optionally it may be qualified)
or the OID of the relation. Note that pgstattuple only returns
one row.
4. Notes
pgstattuple acquires only a read lock on the relation. So concurrent
update may affect the result.
pgstattuple judges a tuple is "dead" if HeapTupleSatisfiesNow()
returns false.
5. History
2007/05/17
Moved page-level functions to contrib/pageinspect.
2006/06/28
Extended to work against indexes.
|
