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
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
|
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
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
4 | 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
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
4 | 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
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
4 | 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
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
4 | 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)
create index test_hashidx on test using hash (b);
select * from pgstathashindex('test_hashidx');
version | bucket_pages | overflow_pages | bitmap_pages | unused_pages | live_items | dead_items | free_percent
---------+--------------+----------------+--------------+--------------+------------+------------+--------------
4 | 4 | 0 | 1 | 0 | 0 | 0 | 100
(1 row)
-- these should error with the wrong type
select pgstatginindex('test_pkey');
ERROR: relation "test_pkey" is not a GIN index
select pgstathashindex('test_pkey');
ERROR: relation "test_pkey" is not a hash index
select pgstatindex('test_ginidx');
ERROR: relation "test_ginidx" is not a btree index
select pgstathashindex('test_ginidx');
ERROR: relation "test_ginidx" is not a hash index
select pgstatindex('test_hashidx');
ERROR: relation "test_hashidx" is not a btree index
select pgstatginindex('test_hashidx');
ERROR: relation "test_hashidx" is not a GIN index
-- check that using any of these functions with unsupported relations will fail
create table test_partitioned (a int) partition by range (a);
create index test_partitioned_index on test_partitioned(a);
create index test_partitioned_hash_index on test_partitioned using hash(a);
-- these should all fail
select pgstattuple('test_partitioned');
ERROR: cannot get tuple-level statistics for relation "test_partitioned"
DETAIL: This operation is not supported for partitioned tables.
select pgstattuple('test_partitioned_index');
ERROR: cannot get tuple-level statistics for relation "test_partitioned_index"
DETAIL: This operation is not supported for partitioned indexes.
select pgstattuple_approx('test_partitioned');
ERROR: relation "test_partitioned" is of wrong relation kind
DETAIL: This operation is not supported for partitioned tables.
select pg_relpages('test_partitioned');
ERROR: cannot get page count of relation "test_partitioned"
DETAIL: This operation is not supported for partitioned tables.
select pgstatindex('test_partitioned');
ERROR: relation "test_partitioned" is not a btree index
select pgstatginindex('test_partitioned');
ERROR: relation "test_partitioned" is not a GIN index
select pgstathashindex('test_partitioned');
ERROR: relation "test_partitioned" is not a hash index
select pgstathashindex('test_partitioned_hash_index');
ERROR: relation "test_partitioned_hash_index" is not a hash index
create view test_view as select 1;
-- these should all fail
select pgstattuple('test_view');
ERROR: cannot get tuple-level statistics for relation "test_view"
DETAIL: This operation is not supported for views.
select pgstattuple_approx('test_view');
ERROR: relation "test_view" is of wrong relation kind
DETAIL: This operation is not supported for views.
select pg_relpages('test_view');
ERROR: cannot get page count of relation "test_view"
DETAIL: This operation is not supported for views.
select pgstatindex('test_view');
ERROR: relation "test_view" is not a btree index
select pgstatginindex('test_view');
ERROR: relation "test_view" is not a GIN index
select pgstathashindex('test_view');
ERROR: relation "test_view" is not a hash index
create foreign data wrapper dummy;
create server dummy_server foreign data wrapper dummy;
create foreign table test_foreign_table () server dummy_server;
-- these should all fail
select pgstattuple('test_foreign_table');
ERROR: cannot get tuple-level statistics for relation "test_foreign_table"
DETAIL: This operation is not supported for foreign tables.
select pgstattuple_approx('test_foreign_table');
ERROR: relation "test_foreign_table" is of wrong relation kind
DETAIL: This operation is not supported for foreign tables.
select pg_relpages('test_foreign_table');
ERROR: cannot get page count of relation "test_foreign_table"
DETAIL: This operation is not supported for foreign tables.
select pgstatindex('test_foreign_table');
ERROR: relation "test_foreign_table" is not a btree index
select pgstatginindex('test_foreign_table');
ERROR: relation "test_foreign_table" is not a GIN index
select pgstathashindex('test_foreign_table');
ERROR: relation "test_foreign_table" is not a hash index
-- a partition of a partitioned table should work though
create table test_partition partition of test_partitioned for values from (1) to (100);
select pgstattuple('test_partition');
pgstattuple
---------------------
(0,0,0,0,0,0,0,0,0)
(1 row)
select pgstattuple_approx('test_partition');
pgstattuple_approx
-----------------------
(0,0,0,0,0,0,0,0,0,0)
(1 row)
select pg_relpages('test_partition');
pg_relpages
-------------
0
(1 row)
-- toast tables should work
select pgstattuple((select reltoastrelid from pg_class where relname = 'test'));
pgstattuple
---------------------
(0,0,0,0,0,0,0,0,0)
(1 row)
select pgstattuple_approx((select reltoastrelid from pg_class where relname = 'test'));
pgstattuple_approx
-----------------------
(0,0,0,0,0,0,0,0,0,0)
(1 row)
select pg_relpages((select reltoastrelid from pg_class where relname = 'test'));
pg_relpages
-------------
0
(1 row)
-- not for the index calls though, of course
select pgstatindex('test_partition');
ERROR: relation "test_partition" is not a btree index
select pgstatginindex('test_partition');
ERROR: relation "test_partition" is not a GIN index
select pgstathashindex('test_partition');
ERROR: relation "test_partition" is not a hash index
-- an actual index of a partitioned table should work though
create index test_partition_idx on test_partition(a);
create index test_partition_hash_idx on test_partition using hash (a);
-- these should work
select pgstatindex('test_partition_idx');
pgstatindex
------------------------------
(4,0,8192,0,0,0,0,0,NaN,NaN)
(1 row)
select pgstathashindex('test_partition_hash_idx');
pgstathashindex
---------------------
(4,8,0,1,0,0,0,100)
(1 row)
drop table test_partitioned;
drop view test_view;
drop foreign table test_foreign_table;
drop server dummy_server;
drop foreign data wrapper dummy;
|
