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
|
CREATE EXTENSION pgstattuple;
--
-- It's difficult to come up with platform-independent test cases for
-- the pgstattuple functions, but the results for empty tables and
-- indexes should be that.
--
create table test (a int primary key, b int[]);
select * from pgstattuple('test');
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
(1 row)
select * from pgstattuple('test'::text);
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
(1 row)
select * from pgstattuple('test'::name);
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
(1 row)
select * from pgstattuple('test'::regclass);
table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
(1 row)
select pgstattuple(oid) from pg_class where relname = 'test';
pgstattuple
---------------------
(0,0,0,0,0,0,0,0,0)
(1 row)
select pgstattuple(relname) from pg_class where relname = 'test';
pgstattuple
---------------------
(0,0,0,0,0,0,0,0,0)
(1 row)
select version, tree_level,
index_size / current_setting('block_size')::int as index_size,
root_block_no, internal_pages, leaf_pages, empty_pages, deleted_pages,
avg_leaf_density, leaf_fragmentation
from pgstatindex('test_pkey');
version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
2 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | NaN | NaN
(1 row)
select version, tree_level,
index_size / current_setting('block_size')::int as index_size,
root_block_no, internal_pages, leaf_pages, empty_pages, deleted_pages,
avg_leaf_density, leaf_fragmentation
from pgstatindex('test_pkey'::text);
version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
2 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | NaN | NaN
(1 row)
select version, tree_level,
index_size / current_setting('block_size')::int as index_size,
root_block_no, internal_pages, leaf_pages, empty_pages, deleted_pages,
avg_leaf_density, leaf_fragmentation
from pgstatindex('test_pkey'::name);
version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
2 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | NaN | NaN
(1 row)
select version, tree_level,
index_size / current_setting('block_size')::int as index_size,
root_block_no, internal_pages, leaf_pages, empty_pages, deleted_pages,
avg_leaf_density, leaf_fragmentation
from pgstatindex('test_pkey'::regclass);
version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
2 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | NaN | NaN
(1 row)
select pg_relpages('test');
pg_relpages
-------------
0
(1 row)
select pg_relpages('test_pkey');
pg_relpages
-------------
1
(1 row)
select pg_relpages('test_pkey'::text);
pg_relpages
-------------
1
(1 row)
select pg_relpages('test_pkey'::name);
pg_relpages
-------------
1
(1 row)
select pg_relpages('test_pkey'::regclass);
pg_relpages
-------------
1
(1 row)
select pg_relpages(oid) from pg_class where relname = 'test_pkey';
pg_relpages
-------------
1
(1 row)
select pg_relpages(relname) from pg_class where relname = 'test_pkey';
pg_relpages
-------------
1
(1 row)
create index test_ginidx on test using gin (b);
select * from pgstatginindex('test_ginidx');
version | pending_pages | pending_tuples
---------+---------------+----------------
2 | 0 | 0
(1 row)
|
