Movatterモバイル変換

[0]ホーム
URL:

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

 Sign up
Appearance settings

Commitb754716

Browse files
committed
Fix multiple bugs in contrib/pgstattuple's pgstatindex() function.
Dead or half-dead index leaf pages were incorrectly reported as live, as aconsequence of a code rearrangement I made (during a moment of severe brainfade, evidently) in commitd287818.The index metapage was not counted in index_size, causing that result tonot agree with the actual index size on-disk.Index root pages were not counted in internal_pages, which is inconsistentcompared to the case of a root that's also a leaf (one-page index), wherethe root would be counted in leaf_pages. Aside from that inconsistency,this could lead to additional transient discrepancies between the reportedpage counts and index_size, since it's possible for pgstatindex's scan tosee zero or multiple pages marked as BTP_ROOT, if the root moves due toa split during the scan. With these fixes, index_size will always beexactly one page more than the sum of the displayed page counts.Also, the index_size result was incorrectly documented as being measured inpages; it's always been measured in bytes. (While fixing that, I couldn'tresist doing some small additional wordsmithing on the pgstattuple docs.)Including the metapage causes the reported index_size to not be zero foran empty index. To preserve the desired property that the pgstattupleregression test results are platform-independent (ie, BLCKSZ configurationindependent), scale the index_size result in the regression tests.The documentation issue was reported by Otsuka Kenji, and the inconsistentroot page counting by Peter Geoghegan; the other problems noted by me.Back-patch to all supported branches, because this has been broken fora long time.
1 parentf461fa7 commitb754716

File tree

4 files changed

+67
-46
lines changed

4 files changed

+67
-46
lines changed

‎contrib/pgstattuple/expected/pgstattuple.out

Lines 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

Lines changed: 6 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -78,7 +78,6 @@ typedef struct BTIndexStat
7878
uint32level;
7979
BlockNumberroot_blkno;
8080

81-
uint64root_pages;
8281
uint64internal_pages;
8382
uint64leaf_pages;
8483
uint64empty_pages;
@@ -184,7 +183,6 @@ pgstatindex_impl(Relation rel, FunctionCallInfo fcinfo)
184183
}
185184

186185
/* -- init counters -- */
187-
indexStat.root_pages=0;
188186
indexStat.internal_pages=0;
189187
indexStat.leaf_pages=0;
190188
indexStat.empty_pages=0;
@@ -217,7 +215,11 @@ pgstatindex_impl(Relation rel, FunctionCallInfo fcinfo)
217215

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

220-
if (P_ISLEAF(opaque))
218+
if (P_ISDELETED(opaque))
219+
indexStat.deleted_pages++;
220+
elseif (P_IGNORE(opaque))
221+
indexStat.empty_pages++;/* this is the "half dead" state */
222+
elseif (P_ISLEAF(opaque))
221223
{
222224
intmax_avail;
223225

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

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

‎contrib/pgstattuple/sql/pgstattuple.sql

Lines 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_classwhere relname='test';
1616
select pgstattuple(relname)from pg_classwhere 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_classwhere relname='test_pkey';
23-
select pgstatindex(relname)from pg_classwhere relname='test_pkey';
18+
select version, tree_level,
19+
index_size/ current_setting('block_size')::intas 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')::intas 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')::intas 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')::intas 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

Lines changed: 17 additions & 10 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 specifiedby TEXT. This function is kept
133+
that the target relation is specifiedas 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=> 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>Totalnumber of pagesinindex</entry>
197+
<entry>Totalindex sizeinbytes</entry>
198198
</row>
199199

200200
<row>
201201
<entry><structfield>root_block_no</structfield></entry>
202202
<entry><type>bigint</type></entry>
203-
<entry>Location of rootblock</entry>
203+
<entry>Location of rootpage (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 specifiedby TEXT. This function is kept
270+
that the target index is specifiedas 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 specifiedby TEXT. This function is kept
361+
that the target relation is specifiedas 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>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp