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
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
|
$PostgreSQL: pgsql/contrib/pgstattuple/README.pgstattuple.euc_jp,v 1.8 2006/09/04 03:49:05 momjian Exp $
pgstattuple README 2002/08/22 �а�ã��
1. ���ݡ��Ȥ���Ƥ���ؿ�
pgstattuple
-----------
pgstattuple�ϡ�UPDATE��DELETE�Ǻ��줿��졼�����Υ����ΰ���礭����
��졼������Τ�ʪ��Ū���礭�����Ф���ѡ�����ơ������ֵѤ��ޤ�����
�ޤꡤ�ֵ��ͤ��礭����С�����������ߤ�¿���Τǡ�vacuum����ɬ
�פ�����Ȥ���Ƚ�Ǥν����ˤʤ�櫓�Ǥ�������ʳ��ˤ⤤�������ʾ���
���֤�ޤ���
pgstattuple() �ϡ���졼������Ĺ���䡢���ץ��"�����ΰ�"�γ��ʤɤ�
������ֵѤ��ޤ��������ξ���ϡ�vacuum ��ɬ�פ��ɤ������桼����Ƚ��
����Τ���Ω�ĤǤ��礦���㤨�аʲ��Τ褦�ʷ��ˤʤ�ޤ���
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
�ʲ����ƹ��ܤ����ƤǤ���
table_len -- ��졼������ʪ��Ū���礭��(�Х���)
tuple_count -- ���ץ��
tuple_len -- ���ץ�Ĺ�ι��(�Х���)
tuple_percent -- ���ץ�γ�硥table_len���Ф���tuple_len����Ψ��
dead_tuple_len -- �ǥåɥ��ץ��
dead_tuple_percent -- �ǥåɥ��ץ�γ�硥table_len���Ф���tuple_len����Ψ��
free_space -- �����Ѳ�ǽ���ΰ�(�Х���)
free_percent -- �����Ѳ�ǽ���ΰ补table_len���Ф���free_space����Ψ��
pg_relpages
-----------
pg_relpages() �ϥ�졼�����Υڡ��������ֵѤ��ޤ���
pgstatindex
-----------
pgstatindex() �ϥ���ǥå����˴ؤ��������ֵѤ��ޤ���
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
bt_metap
--------
bt_metap() ��btree����ǥå����Υ�ڡ����˴ؤ��������ֵѤ��ޤ���
test=> SELECT * FROM bt_metap('pg_cast_oid_index');
-[ RECORD 1 ]-----
magic | 340322
version | 2
root | 1
level | 0
fastroot | 1
fastlevel | 0
bt_page_stats
-------------
bt_page_stats() �ϡ�btree��ñ��Υڡ����˴ؤ�������ɽ�����ޤ���
test=> SELECT * FROM bt_page_stats('pg_cast_oid_index', 1);
-[ RECORD 1 ]-+-----
blkno | 1
type | l
live_items | 256
dead_items | 0
avg_item_size | 12
page_size | 8192
free_size | 4056
btpo_prev | 0
btpo_next | 0
btpo | 0
btpo_flags | 3
bt_page_items
-------------
bt_page_items() �ϡ���btree�ڡ����θ��̤Υ����ƥ�˴ؤ�������ɽ�����ޤ���
test=> SELECT * FROM bt_page_items('pg_cast_oid_index', 1);
itemoffset | ctid | itemlen | nulls | vars | data
------------+---------+---------+-------+------+-------------
1 | (0,1) | 12 | f | f | 23 27 00 00
2 | (0,2) | 12 | f | f | 24 27 00 00
3 | (0,3) | 12 | f | f | 25 27 00 00
4 | (0,4) | 12 | f | f | 26 27 00 00
5 | (0,5) | 12 | f | f | 27 27 00 00
6 | (0,6) | 12 | f | f | 28 27 00 00
7 | (0,7) | 12 | f | f | 29 27 00 00
8 | (0,8) | 12 | f | f | 2a 27 00 00
2. pgstattuple�Υ��ȡ���
PostgreSQL��/usr/local/pgsql�˥��ȡ���ѤǤ��ꡤtest�ǡ����١�
����pgstattuple�ȡ��뤹����μ����ޤ���
$ make
$ make install
�桼������ؿ�����Ͽ���ޤ���
$ psql -e -f /usr/local/pgsql/share/contrib/pgstattuple.sql test
3. pgstattuple�λȤ���
pgstattuple�θƤӽФ������ϰʲ��Ǥ���
CREATE OR REPLACE FUNCTION pgstattuple(text) RETURNS pgstattuple_type
AS 'MODULE_PATHNAME', 'pgstattuple'
LANGUAGE C STRICT;
������: ��졼�����̾
�ؿ�������pgstattuple_type���Ǥ���
pgstattuple�ϥ�졼������AccessShareLock���������ʤ��Τǡ�
pgstattuple ��¹���˳�����졼�����˹���������ȯ������ȡ�������
�ʤ���̤��֤���ǽ��������ޤ���
pgstattuple�����ץ��֥��ߡפ�Ƚ�Ǥ�����ϡ�
HeapTupleSatisfiesNow()�������֤����Ȥ��Ǥ���
4. pgstattuple�Υ饤�����ˤĤ���
pgstattuple.c����Ƭ�˽Ƥ����̤�Ǥ����ޤ���pgstattuple �ϴ�����̵��
�ڤǤ���pgstattuple ����Ѥ������Ȥˤ�ä������뤤���ʤ��̤˴ؤ���
����Ǥ���餤�ޤ���
5. ��������
2006/06/28
����ǥå������Ф��Ƥ�ư���褦�˳�ĥ��
2002/09/04
SRF�ѹ���ȼ����Tom Lane �����������ե������ν�����Ԥä���
���Τ��Ȥ�README�ˤ�ȿ�ǡ�
2002/08/23
SRF(Set Returning Function)��Ȥä�7.3�Ѥ˽�����
2001/12/20 Tom Lane�ˤ�뽤��
Fix pgstattuple to acquire a read lock on the target table. This
prevents embarassments such as having the table dropped or truncated
partway through the scan. Also, fix free space calculation to include
pages that currently contain no tuples.
2001/10/01 PostgreSQL 7.2 ��contrib module����Ͽ
2001/08/30 pgstattuple ������� 0.1����
|
