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

Commit857f9c3

Browse files
committed
Skip full index scan during cleanup of B-tree indexes when possible
Vacuum of index consists from two stages: multiple (zero of more) ambulkdeletecalls and one amvacuumcleanup call. When workload on particular tableis append-only, then autovacuum isn't intended to touch this table. However,user may run vacuum manually in order to fill visibility map and get benefitsof index-only scans. Then ambulkdelete wouldn't be called for indexesof such table (because no heap tuples were deleted), only amvacuumcleanup wouldbe called In this case, amvacuumcleanup would perform full index scan fortwo objectives: put recyclable pages into free space map and update indexstatistics.This patch allows btvacuumclanup to skip full index scan when two conditionsare satisfied: no pages are going to be put into free space map and indexstatistics isn't stalled. In order to check first condition, we storeoldest btpo_xact in the meta-page. When it's precedes RecentGlobalXmin, thenthere are some recyclable pages. In order to check second condition we storenumber of heap tuples observed during previous full index scan by cleanup.If fraction of newly inserted tuples is less thanvacuum_cleanup_index_scale_factor, then statistics isn't considered to bestalled. vacuum_cleanup_index_scale_factor can be defined as both reloption and GUC (default).This patch bumps B-tree meta-page version. Upgrade of meta-page is performed"on the fly": during VACUUM meta-page is rewritten with new version. No specialhandling in pg_upgrade is required.Author: Masahiko Sawada, Alexander KorotkovReview by: Peter Geoghegan, Kyotaro Horiguchi, Alexander Korotkov, Yura SokolovDiscussion:https://www.postgresql.org/message-id/flat/CAD21AoAX+d2oD_nrd9O2YkpzHaFr=uQeGr9s1rKC3O4ENc568g@mail.gmail.com
1 parenteac93e2 commit857f9c3

File tree

23 files changed

+458
-45
lines changed

23 files changed

+458
-45
lines changed

‎contrib/amcheck/verify_nbtree.c

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1500,12 +1500,14 @@ palloc_btree_page(BtreeCheckState *state, BlockNumber blocknum)
15001500
errmsg("index \"%s\" meta page is corrupt",
15011501
RelationGetRelationName(state->rel))));
15021502

1503-
if (metad->btm_version!=BTREE_VERSION)
1503+
if (metad->btm_version<BTREE_MIN_VERSION||
1504+
metad->btm_version>BTREE_VERSION)
15041505
ereport(ERROR,
15051506
(errcode(ERRCODE_INDEX_CORRUPTED),
1506-
errmsg("version mismatch in index \"%s\": file version %d, code version %d",
1507+
errmsg("version mismatch in index \"%s\": file version %d, "
1508+
"current version %d, minimal supported version %d",
15071509
RelationGetRelationName(state->rel),
1508-
metad->btm_version,BTREE_VERSION)));
1510+
metad->btm_version,BTREE_VERSION,BTREE_MIN_VERSION)));
15091511
}
15101512

15111513
/*

‎contrib/pageinspect/Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -5,7 +5,8 @@ OBJS= rawpage.o heapfuncs.o btreefuncs.o fsmfuncs.o \
55
brinfuncs.o ginfuncs.o hashfuncs.o$(WIN32RES)
66

77
EXTENSION = pageinspect
8-
DATA = pageinspect--1.5.sql pageinspect--1.5--1.6.sql\
8+
DATA = pageinspect--1.6--1.7.sql\
9+
pageinspect--1.5.sql pageinspect--1.5--1.6.sql\
910
pageinspect--1.4--1.5.sql pageinspect--1.3--1.4.sql\
1011
pageinspect--1.2--1.3.sql pageinspect--1.1--1.2.sql\
1112
pageinspect--1.0--1.1.sql pageinspect--unpackaged--1.0.sql

‎contrib/pageinspect/btreefuncs.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -511,7 +511,7 @@ bt_metap(PG_FUNCTION_ARGS)
511511
BTMetaPageData*metad;
512512
TupleDesctupleDesc;
513513
intj;
514-
char*values[6];
514+
char*values[8];
515515
Bufferbuffer;
516516
Pagepage;
517517
HeapTupletuple;
@@ -555,6 +555,8 @@ bt_metap(PG_FUNCTION_ARGS)
555555
values[j++]=psprintf("%d",metad->btm_level);
556556
values[j++]=psprintf("%d",metad->btm_fastroot);
557557
values[j++]=psprintf("%d",metad->btm_fastlevel);
558+
values[j++]=psprintf("%u",metad->btm_oldest_btpo_xact);
559+
values[j++]=psprintf("%lf",metad->btm_last_cleanup_num_heap_tuples);
558560

559561
tuple=BuildTupleFromCStrings(TupleDescGetAttInMetadata(tupleDesc),
560562
values);

‎contrib/pageinspect/expected/btree.out

Lines changed: 9 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -3,13 +3,15 @@ INSERT INTO test1 VALUES (72057594037927937, 'text');
33
CREATE INDEX test1_a_idx ON test1 USING btree (a);
44
\x
55
SELECT * FROM bt_metap('test1_a_idx');
6-
-[ RECORD 1 ]-----
7-
magic | 340322
8-
version | 2
9-
root | 1
10-
level | 0
11-
fastroot | 1
12-
fastlevel | 0
6+
-[ RECORD 1 ]-----------+-------
7+
magic | 340322
8+
version | 3
9+
root | 1
10+
level | 0
11+
fastroot | 1
12+
fastlevel | 0
13+
oldest_xact | 0
14+
last_cleanup_num_tuples | -1
1315

1416
SELECT * FROM bt_page_stats('test1_a_idx', 0);
1517
ERROR: block 0 is a meta page
Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,26 @@
1+
/* contrib/pageinspect/pageinspect--1.6--1.7.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
4+
\echo Use"ALTER EXTENSION pageinspect UPDATE TO '1.7'" to load this file. \quit
5+
6+
--
7+
-- bt_metap()
8+
--
9+
DROPFUNCTION bt_metap(IN relnametext,
10+
OUT magic int4,
11+
OUT version int4,
12+
OUT root int4,
13+
OUT level int4,
14+
OUT fastroot int4,
15+
OUT fastlevel int4);
16+
CREATEFUNCTIONbt_metap(IN relnametext,
17+
OUT magic int4,
18+
OUT version int4,
19+
OUT root int4,
20+
OUT level int4,
21+
OUT fastroot int4,
22+
OUT fastlevel int4,
23+
OUT oldest_xact int4,
24+
OUT last_cleanup_num_tuplesreal)
25+
AS'MODULE_PATHNAME','bt_metap'
26+
LANGUAGE C STRICT PARALLEL SAFE;
Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
# pageinspect extension
22
comment = 'inspect the contents of database pages at a low level'
3-
default_version = '1.6'
3+
default_version = '1.7'
44
module_pathname = '$libdir/pageinspect'
55
relocatable = true

‎contrib/pgstattuple/expected/pgstattuple.out

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -48,7 +48,7 @@ select version, tree_level,
4848
from pgstatindex('test_pkey');
4949
version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
5050
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
51-
2 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | NaN | NaN
51+
3 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | NaN | NaN
5252
(1 row)
5353

5454
select version, tree_level,
@@ -58,7 +58,7 @@ select version, tree_level,
5858
from pgstatindex('test_pkey'::text);
5959
version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
6060
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
61-
2 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | NaN | NaN
61+
3 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | NaN | NaN
6262
(1 row)
6363

6464
select version, tree_level,
@@ -68,7 +68,7 @@ select version, tree_level,
6868
from pgstatindex('test_pkey'::name);
6969
version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
7070
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
71-
2 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | NaN | NaN
71+
3 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | NaN | NaN
7272
(1 row)
7373

7474
select version, tree_level,
@@ -78,7 +78,7 @@ select version, tree_level,
7878
from pgstatindex('test_pkey'::regclass);
7979
version | tree_level | index_size | root_block_no | internal_pages | leaf_pages | empty_pages | deleted_pages | avg_leaf_density | leaf_fragmentation
8080
---------+------------+------------+---------------+----------------+------------+-------------+---------------+------------------+--------------------
81-
2 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | NaN | NaN
81+
3 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | NaN | NaN
8282
(1 row)
8383

8484
select pg_relpages('test');
@@ -229,7 +229,7 @@ create index test_partition_hash_idx on test_partition using hash (a);
229229
select pgstatindex('test_partition_idx');
230230
pgstatindex
231231
------------------------------
232-
(2,0,8192,0,0,0,0,0,NaN,NaN)
232+
(3,0,8192,0,0,0,0,0,NaN,NaN)
233233
(1 row)
234234

235235
select pgstathashindex('test_partition_hash_idx');

‎doc/src/sgml/config.sgml

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1882,6 +1882,31 @@ include_dir 'conf.d'
18821882
</note>
18831883
</sect2>
18841884

1885+
<sect2 id="runtime-config-index-vacuum">
1886+
<title>Index Vacuum</title>
1887+
<variablelist>
1888+
<varlistentry id="guc-vacuum-cleanup-index-scale-factor" xreflabel="vacuum_cleanup_index_scale_factor">
1889+
<term><varname>vacuum_cleanup_index_scale_factor</varname> (<type>floating point</type>)
1890+
<indexterm>
1891+
<primary><varname>vacuum_cleanup_index_scale_factor</varname> configuration parameter</primary>
1892+
</indexterm>
1893+
</term>
1894+
<listitem>
1895+
<para>
1896+
When no tuples were deleted from the heap, B-tree indexes might still
1897+
be scanned during <command>VACUUM</command> cleanup stage by two
1898+
reasons. The first reason is that B-tree index contains deleted pages
1899+
which can be recycled during cleanup. The second reason is that B-tree
1900+
index statistics is stalled. The criterion of stalled index statistics
1901+
is number of inserted tuples since previous statistics collection
1902+
is greater than <varname>vacuum_cleanup_index_scale_factor</varname>
1903+
fraction of total number of heap tuples.
1904+
</para>
1905+
</listitem>
1906+
</varlistentry>
1907+
</variablelist>
1908+
</sect2>
1909+
18851910
<sect2 id="runtime-config-resource-background-writer">
18861911
<title>Background Writer</title>
18871912

‎doc/src/sgml/pageinspect.sgml

Lines changed: 9 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -247,13 +247,15 @@ test=# SELECT * FROM heap_page_item_attrs(get_raw_page('pg_class', 0), 'pg_class
247247
index's metapage. For example:
248248
<screen>
249249
test=# SELECT * FROM bt_metap('pg_cast_oid_index');
250-
-[ RECORD 1 ]-----
251-
magic | 340322
252-
version | 2
253-
root | 1
254-
level | 0
255-
fastroot | 1
256-
fastlevel | 0
250+
-[ RECORD 1 ]-----------+-------
251+
magic | 340322
252+
version | 3
253+
root | 1
254+
level | 0
255+
fastroot | 1
256+
fastlevel | 0
257+
oldest_xact | 582
258+
last_cleanup_num_tuples | 1000
257259
</screen>
258260
</para>
259261
</listitem>

‎doc/src/sgml/ref/create_index.sgml

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -369,6 +369,21 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
369369
</varlistentry>
370370
</variablelist>
371371

372+
<para>
373+
B-tree indexes additionally accept this parameter:
374+
</para>
375+
376+
<variablelist>
377+
<varlistentry>
378+
<term><literal>vacuum_cleanup_index_scale_factor</literal></term>
379+
<listitem>
380+
<para>
381+
Per-table value for <xref linkend="guc-vacuum-cleanup-index-scale-factor"/>.
382+
</para>
383+
</listitem>
384+
</varlistentry>
385+
</variablelist>
386+
372387
<para>
373388
GiST indexes additionally accept this parameter:
374389
</para>

‎src/backend/access/common/reloptions.c

Lines changed: 12 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -409,6 +409,15 @@ static relopt_real realRelOpts[] =
409409
},
410410
0,-1.0,DBL_MAX
411411
},
412+
{
413+
{
414+
"vacuum_cleanup_index_scale_factor",
415+
"Number of tuple inserts prior to index cleanup as a fraction of reltuples.",
416+
RELOPT_KIND_BTREE,
417+
ShareUpdateExclusiveLock
418+
},
419+
-1,0.0,100.0
420+
},
412421
/* list terminator */
413422
{{NULL}}
414423
};
@@ -1371,7 +1380,9 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
13711380
{"user_catalog_table",RELOPT_TYPE_BOOL,
13721381
offsetof(StdRdOptions,user_catalog_table)},
13731382
{"parallel_workers",RELOPT_TYPE_INT,
1374-
offsetof(StdRdOptions,parallel_workers)}
1383+
offsetof(StdRdOptions,parallel_workers)},
1384+
{"vacuum_cleanup_index_scale_factor",RELOPT_TYPE_REAL,
1385+
offsetof(StdRdOptions,vacuum_cleanup_index_scale_factor)}
13751386
};
13761387

13771388
options=parseRelOptions(reloptions,validate,kind,&numoptions);

‎src/backend/access/nbtree/nbtinsert.c

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -939,6 +939,9 @@ _bt_insertonpg(Relation rel,
939939

940940
if (BufferIsValid(metabuf))
941941
{
942+
/* upgrade meta-page if needed */
943+
if (metad->btm_version<BTREE_VERSION)
944+
_bt_upgrademetapage(metapg);
942945
metad->btm_fastroot=itup_blkno;
943946
metad->btm_fastlevel=lpageop->btpo.level;
944947
MarkBufferDirty(metabuf);
@@ -997,6 +1000,9 @@ _bt_insertonpg(Relation rel,
9971000
xlmeta.level=metad->btm_level;
9981001
xlmeta.fastroot=metad->btm_fastroot;
9991002
xlmeta.fastlevel=metad->btm_fastlevel;
1003+
xlmeta.oldest_btpo_xact=metad->btm_oldest_btpo_xact;
1004+
xlmeta.last_cleanup_num_heap_tuples=
1005+
metad->btm_last_cleanup_num_heap_tuples;
10001006

10011007
XLogRegisterBuffer(2,metabuf,REGBUF_WILL_INIT |REGBUF_STANDARD);
10021008
XLogRegisterBufData(2, (char*)&xlmeta,sizeof(xl_btree_metadata));
@@ -2049,6 +2055,10 @@ _bt_newroot(Relation rel, Buffer lbuf, Buffer rbuf)
20492055
metapg=BufferGetPage(metabuf);
20502056
metad=BTPageGetMeta(metapg);
20512057

2058+
/* upgrade metapage if needed */
2059+
if (metad->btm_version<BTREE_VERSION)
2060+
_bt_upgrademetapage(metapg);
2061+
20522062
/*
20532063
* Create downlink item for left page (old root). Since this will be the
20542064
* first item in a non-leaf page, it implicitly has minus-infinity key
@@ -2138,6 +2148,8 @@ _bt_newroot(Relation rel, Buffer lbuf, Buffer rbuf)
21382148
md.level=metad->btm_level;
21392149
md.fastroot=rootblknum;
21402150
md.fastlevel=metad->btm_level;
2151+
md.oldest_btpo_xact=metad->btm_oldest_btpo_xact;
2152+
md.last_cleanup_num_heap_tuples=metad->btm_last_cleanup_num_heap_tuples;
21412153

21422154
XLogRegisterBufData(2, (char*)&md,sizeof(xl_btree_metadata));
21432155

0 commit comments

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