Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 48e6c94

Browse filesBrowse files
tglsfdctglsfdc
committed
Fix multiple bugs in contrib/pgstattuple's pgstatindex() function.
Dead or half-dead index leaf pages were incorrectly reported as live, as a consequence of a code rearrangement I made (during a moment of severe brain fade, evidently) in commit d287818. The index metapage was not counted in index_size, causing that result to not agree with the actual index size on-disk. Index root pages were not counted in internal_pages, which is inconsistent compared to the case of a root that's also a leaf (one-page index), where the root would be counted in leaf_pages. Aside from that inconsistency, this could lead to additional transient discrepancies between the reported page counts and index_size, since it's possible for pgstatindex's scan to see zero or multiple pages marked as BTP_ROOT, if the root moves due to a split during the scan. With these fixes, index_size will always be exactly one page more than the sum of the displayed page counts. Also, the index_size result was incorrectly documented as being measured in pages; it's always been measured in bytes. (While fixing that, I couldn't resist doing some small additional wordsmithing on the pgstattuple docs.) Including the metapage causes the reported index_size to not be zero for an empty index. To preserve the desired property that the pgstattuple regression test results are platform-independent (ie, BLCKSZ configuration independent), scale the index_size result in the regression tests. The documentation issue was reported by Otsuka Kenji, and the inconsistent root page counting by Peter Geoghegan; the other problems noted by me. Back-patch to all supported branches, because this has been broken for a long time.
1 parent 18777c3 commit 48e6c94
Copy full SHA for 48e6c94

File tree

Expand file treeCollapse file tree

4 files changed

+68
-47
lines changed
Filter options
Expand file treeCollapse file tree

4 files changed

+68
-47
lines changed

‎contrib/pgstattuple/expected/pgstattuple.out

Copy file name to clipboardExpand all lines: contrib/pgstattuple/expected/pgstattuple.out
+24-20Lines changed: 24 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -41,40 +41,44 @@ select pgstattuple(relname) from pg_class where relname = 'test';
4141
(0,0,0,0,0,0,0,0,0)
4242
(1 row)
4343

44-
select * from pgstatindex('test_pkey');
44+
select version, tree_level,
45+
index_size / current_setting('block_size')::int as index_size,
46+
root_block_no, internal_pages, leaf_pages, empty_pages, deleted_pages,
47+
avg_leaf_density, leaf_fragmentation
48+
from pgstatindex('test_pkey');
4549
version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
4650
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
47-
2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NaN | NaN
51+
2 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | NaN | NaN
4852
(1 row)
4953

50-
select * from pgstatindex('test_pkey'::text);
54+
select version, tree_level,
55+
index_size / current_setting('block_size')::int as index_size,
56+
root_block_no, internal_pages, leaf_pages, empty_pages, deleted_pages,
57+
avg_leaf_density, leaf_fragmentation
58+
from pgstatindex('test_pkey'::text);
5159
version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
5260
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
53-
2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NaN | NaN
61+
2 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | NaN | NaN
5462
(1 row)
5563

56-
select * from pgstatindex('test_pkey'::name);
64+
select version, tree_level,
65+
index_size / current_setting('block_size')::int as index_size,
66+
root_block_no, internal_pages, leaf_pages, empty_pages, deleted_pages,
67+
avg_leaf_density, leaf_fragmentation
68+
from pgstatindex('test_pkey'::name);
5769
version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
5870
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
59-
2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NaN | NaN
71+
2 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | NaN | NaN
6072
(1 row)
6173

62-
select * from pgstatindex('test_pkey'::regclass);
74+
select version, tree_level,
75+
index_size / current_setting('block_size')::int as index_size,
76+
root_block_no, internal_pages, leaf_pages, empty_pages, deleted_pages,
77+
avg_leaf_density, leaf_fragmentation
78+
from pgstatindex('test_pkey'::regclass);
6379
version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
6480
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
65-
2 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NaN | NaN
66-
(1 row)
67-
68-
select pgstatindex(oid) from pg_class where relname = 'test_pkey';
69-
pgstatindex
70-
---------------------------
71-
(2,0,0,0,0,0,0,0,NaN,NaN)
72-
(1 row)
73-
74-
select pgstatindex(relname) from pg_class where relname = 'test_pkey';
75-
pgstatindex
76-
---------------------------
77-
(2,0,0,0,0,0,0,0,NaN,NaN)
81+
2 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | NaN | NaN
7882
(1 row)
7983

8084
select pg_relpages('test');

‎contrib/pgstattuple/pgstatindex.c

Copy file name to clipboardExpand all lines: contrib/pgstattuple/pgstatindex.c
+6-10Lines changed: 6 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -79,7 +79,6 @@ typedef struct BTIndexStat
7979
uint32 level;
8080
BlockNumber root_blkno;
8181

82-
uint64 root_pages;
8382
uint64 internal_pages;
8483
uint64 leaf_pages;
8584
uint64 empty_pages;
@@ -185,7 +184,6 @@ pgstatindex_impl(Relation rel, FunctionCallInfo fcinfo)
185184
}
186185

187186
/* -- init counters -- */
188-
indexStat.root_pages = 0;
189187
indexStat.internal_pages = 0;
190188
indexStat.leaf_pages = 0;
191189
indexStat.empty_pages = 0;
@@ -218,7 +216,11 @@ pgstatindex_impl(Relation rel, FunctionCallInfo fcinfo)
218216

219217
/* Determine page type, and update totals */
220218

221-
if (P_ISLEAF(opaque))
219+
if (P_ISDELETED(opaque))
220+
indexStat.deleted_pages++;
221+
else if (P_IGNORE(opaque))
222+
indexStat.empty_pages++; /* this is the "half dead" state */
223+
else if (P_ISLEAF(opaque))
222224
{
223225
int max_avail;
224226

@@ -235,12 +237,6 @@ pgstatindex_impl(Relation rel, FunctionCallInfo fcinfo)
235237
if (opaque->btpo_next != P_NONE && opaque->btpo_next < blkno)
236238
indexStat.fragments++;
237239
}
238-
else if (P_ISDELETED(opaque))
239-
indexStat.deleted_pages++;
240-
else if (P_IGNORE(opaque))
241-
indexStat.empty_pages++;
242-
else if (P_ISROOT(opaque))
243-
indexStat.root_pages++;
244240
else
245241
indexStat.internal_pages++;
246242

@@ -269,7 +265,7 @@ pgstatindex_impl(Relation rel, FunctionCallInfo fcinfo)
269265
values[j++] = psprintf("%d", indexStat.version);
270266
values[j++] = psprintf("%d", indexStat.level);
271267
values[j++] = psprintf(INT64_FORMAT,
272-
(indexStat.root_pages +
268+
(1 + /* include the metapage in index_size */
273269
indexStat.leaf_pages +
274270
indexStat.internal_pages +
275271
indexStat.deleted_pages +

‎contrib/pgstattuple/sql/pgstattuple.sql

Copy file name to clipboardExpand all lines: contrib/pgstattuple/sql/pgstattuple.sql
+20-6Lines changed: 20 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -15,12 +15,26 @@ select * from pgstattuple('test'::regclass);
1515
select pgstattuple(oid) from pg_class where relname = 'test';
1616
select pgstattuple(relname) from pg_class where relname = 'test';
1717

18-
select * from pgstatindex('test_pkey');
19-
select * from pgstatindex('test_pkey'::text);
20-
select * from pgstatindex('test_pkey'::name);
21-
select * from pgstatindex('test_pkey'::regclass);
22-
select pgstatindex(oid) from pg_class where relname = 'test_pkey';
23-
select pgstatindex(relname) from pg_class where relname = 'test_pkey';
18+
select version, tree_level,
19+
index_size / current_setting('block_size')::int as index_size,
20+
root_block_no, internal_pages, leaf_pages, empty_pages, deleted_pages,
21+
avg_leaf_density, leaf_fragmentation
22+
from pgstatindex('test_pkey');
23+
select version, tree_level,
24+
index_size / current_setting('block_size')::int as index_size,
25+
root_block_no, internal_pages, leaf_pages, empty_pages, deleted_pages,
26+
avg_leaf_density, leaf_fragmentation
27+
from pgstatindex('test_pkey'::text);
28+
select version, tree_level,
29+
index_size / current_setting('block_size')::int as index_size,
30+
root_block_no, internal_pages, leaf_pages, empty_pages, deleted_pages,
31+
avg_leaf_density, leaf_fragmentation
32+
from pgstatindex('test_pkey'::name);
33+
select version, tree_level,
34+
index_size / current_setting('block_size')::int as index_size,
35+
root_block_no, internal_pages, leaf_pages, empty_pages, deleted_pages,
36+
avg_leaf_density, leaf_fragmentation
37+
from pgstatindex('test_pkey'::regclass);
2438

2539
select pg_relpages('test');
2640
select pg_relpages('test_pkey');

‎doc/src/sgml/pgstattuple.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/pgstattuple.sgml
+18-11Lines changed: 18 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -130,9 +130,9 @@ free_percent | 1.95
130130
<listitem>
131131
<para>
132132
This is the same as <function>pgstattuple(regclass)</function>, except
133-
that the target relation is specified by TEXT. This function is kept
133+
that the target relation is specified as TEXT. This function is kept
134134
because of backward-compatibility so far, and will be deprecated in
135-
the future release.
135+
some future release.
136136
</para>
137137
</listitem>
138138
</varlistentry>
@@ -154,13 +154,13 @@ test=&gt; SELECT * FROM pgstatindex('pg_cast_oid_index');
154154
-[ RECORD 1 ]------+------
155155
version | 2
156156
tree_level | 0
157-
index_size | 8192
157+
index_size | 16384
158158
root_block_no | 1
159159
internal_pages | 0
160160
leaf_pages | 1
161161
empty_pages | 0
162162
deleted_pages | 0
163-
avg_leaf_density | 50.27
163+
avg_leaf_density | 54.27
164164
leaf_fragmentation | 0
165165
</programlisting>
166166
</para>
@@ -194,13 +194,13 @@ leaf_fragmentation | 0
194194
<row>
195195
<entry><structfield>index_size</structfield></entry>
196196
<entry><type>bigint</type></entry>
197-
<entry>Total number of pages in index</entry>
197+
<entry>Total index size in bytes</entry>
198198
</row>
199199

200200
<row>
201201
<entry><structfield>root_block_no</structfield></entry>
202202
<entry><type>bigint</type></entry>
203-
<entry>Location of root block</entry>
203+
<entry>Location of root page (zero if none)</entry>
204204
</row>
205205

206206
<row>
@@ -244,6 +244,13 @@ leaf_fragmentation | 0
244244
</informaltable>
245245
</para>
246246

247+
<para>
248+
The reported <literal>index_size</> will normally correspond to one more
249+
page than is accounted for by <literal>internal_pages + leaf_pages +
250+
empty_pages + deleted_pages</literal>, because it also includes the
251+
index's metapage.
252+
</para>
253+
247254
<para>
248255
As with <function>pgstattuple</>, the results are accumulated
249256
page-by-page, and should not be expected to represent an
@@ -260,9 +267,9 @@ leaf_fragmentation | 0
260267
<listitem>
261268
<para>
262269
This is the same as <function>pgstatindex(regclass)</function>, except
263-
that the target index is specified by TEXT. This function is kept
270+
that the target index is specified as TEXT. This function is kept
264271
because of backward-compatibility so far, and will be deprecated in
265-
the future release.
272+
some future release.
266273
</para>
267274
</listitem>
268275
</varlistentry>
@@ -351,9 +358,9 @@ pending_tuples | 0
351358
<listitem>
352359
<para>
353360
This is the same as <function>pg_relpages(regclass)</function>, except
354-
that the target relation is specified by TEXT. This function is kept
361+
that the target relation is specified as TEXT. This function is kept
355362
because of backward-compatibility so far, and will be deprecated in
356-
the future release.
363+
some future release.
357364
</para>
358365
</listitem>
359366
</varlistentry>
@@ -370,7 +377,7 @@ pending_tuples | 0
370377
<para>
371378
<function>pgstattuple_approx</function> is a faster alternative to
372379
<function>pgstattuple</function> that returns approximate results.
373-
The argument is the target relation's OID.
380+
The argument is the target relation's name or OID.
374381
For example:
375382
<programlisting>
376383
test=&gt; SELECT * FROM pgstattuple_approx('pg_catalog.pg_proc'::regclass);

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.