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

Commit0bd4b06

Browse files
committed
Stabilize test of BRIN parallel create
As explained in4d916dd, the test instability is caused by delayedcleanup of deleted rows. This commit removes the DELETE, stabilizing thetest without accidentally disabling parallel builds.The intent of the delete however was to produce empty ranges, and testthat the parallel index build populates those correctly. But there'sanother way to create empty ranges - partial indexes, which does notrely on cleanup of deleted rows.Idea to use partial indexes by Matthias van de Meent, patch by me.Discussion:https://postgr.es/m/95d9cd43-5a92-407c-b7e4-54cd303630fe%40enterprisedb.com
1 parenta89cd7b commit0bd4b06

File tree

2 files changed

+12
-16
lines changed

2 files changed

+12
-16
lines changed

‎contrib/pageinspect/expected/brin.out

Lines changed: 6 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -117,10 +117,6 @@ SELECT (CASE WHEN (mod(i,231) = 0) OR (i BETWEEN 3500 AND 4000) THEN NULL ELSE i
117117
(CASE WHEN (mod(i,233) = 0) OR (i BETWEEN 3750 AND 4250) THEN NULL ELSE md5(i::text) END),
118118
(CASE WHEN (mod(i,233) = 0) OR (i BETWEEN 3850 AND 4500) THEN NULL ELSE (i/100) + mod(i,8) END)
119119
FROM generate_series(1,5000) S(i);
120-
-- Delete a couple pages, to make the ranges empty.
121-
DELETE FROM brin_parallel_test WHERE a BETWEEN 1000 and 1500;
122-
-- Vacuum to remove the tuples and make the ranges actually empty.
123-
VACUUM brin_parallel_test;
124120
-- Build an index with different opclasses - minmax, bloom and minmax-multi.
125121
--
126122
-- For minmax and opclass this is simple, but for minmax-multi we need to be
@@ -135,7 +131,8 @@ VACUUM brin_parallel_test;
135131
SET max_parallel_maintenance_workers = 0;
136132
CREATE INDEX brin_test_serial_idx ON brin_parallel_test
137133
USING brin (a int4_minmax_ops, a int4_bloom_ops, b, c int8_minmax_multi_ops)
138-
WITH (pages_per_range=7);
134+
WITH (pages_per_range=7)
135+
WHERE NOT (a BETWEEN 1000 and 1500);
139136
-- build index using parallelism
140137
--
141138
-- Set a couple parameters to force parallel build for small table. There's a
@@ -147,7 +144,8 @@ SET max_parallel_maintenance_workers = 4;
147144
SET maintenance_work_mem = '128MB';
148145
CREATE INDEX brin_test_parallel_idx ON brin_parallel_test
149146
USING brin (a int4_minmax_ops, a int4_bloom_ops, b, c int8_minmax_multi_ops)
150-
WITH (pages_per_range=7);
147+
WITH (pages_per_range=7)
148+
WHERE NOT (a BETWEEN 1000 and 1500);
151149
SELECT relname, relpages
152150
FROM pg_class
153151
WHERE relname IN ('brin_test_serial_idx', 'brin_test_parallel_idx')
@@ -180,7 +178,8 @@ DROP INDEX brin_test_parallel_idx;
180178
SET max_parallel_workers = 0;
181179
CREATE INDEX brin_test_parallel_idx ON brin_parallel_test
182180
USING brin (a int4_minmax_ops, a int4_bloom_ops, b, c int8_minmax_multi_ops)
183-
WITH (pages_per_range=7);
181+
WITH (pages_per_range=7)
182+
WHERE NOT (a BETWEEN 1000 and 1500);
184183
SELECT relname, relpages
185184
FROM pg_class
186185
WHERE relname IN ('brin_test_serial_idx', 'brin_test_parallel_idx')

‎contrib/pageinspect/sql/brin.sql

Lines changed: 6 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -66,12 +66,6 @@ SELECT (CASE WHEN (mod(i,231) = 0) OR (i BETWEEN 3500 AND 4000) THEN NULL ELSE i
6666
(CASE WHEN (mod(i,233)=0)OR (i BETWEEN3850AND4500) THENNULL ELSE (i/100)+ mod(i,8) END)
6767
FROM generate_series(1,5000) S(i);
6868

69-
-- Delete a couple pages, to make the ranges empty.
70-
DELETEFROM brin_parallel_testWHERE a BETWEEN1000and1500;
71-
72-
-- Vacuum to remove the tuples and make the ranges actually empty.
73-
VACUUM brin_parallel_test;
74-
7569
-- Build an index with different opclasses - minmax, bloom and minmax-multi.
7670
--
7771
-- For minmax and opclass this is simple, but for minmax-multi we need to be
@@ -87,7 +81,8 @@ VACUUM brin_parallel_test;
8781
SET max_parallel_maintenance_workers=0;
8882
CREATEINDEXbrin_test_serial_idxON brin_parallel_test
8983
USING brin (a int4_minmax_ops, a int4_bloom_ops, b, c int8_minmax_multi_ops)
90-
WITH (pages_per_range=7);
84+
WITH (pages_per_range=7)
85+
WHERE NOT (a BETWEEN1000and1500);
9186

9287
-- build index using parallelism
9388
--
@@ -100,7 +95,8 @@ SET max_parallel_maintenance_workers = 4;
10095
SET maintenance_work_mem='128MB';
10196
CREATEINDEXbrin_test_parallel_idxON brin_parallel_test
10297
USING brin (a int4_minmax_ops, a int4_bloom_ops, b, c int8_minmax_multi_ops)
103-
WITH (pages_per_range=7);
98+
WITH (pages_per_range=7)
99+
WHERE NOT (a BETWEEN1000and1500);
104100

105101
SELECT relname, relpages
106102
FROM pg_class
@@ -126,7 +122,8 @@ DROP INDEX brin_test_parallel_idx;
126122
SET max_parallel_workers=0;
127123
CREATEINDEXbrin_test_parallel_idxON brin_parallel_test
128124
USING brin (a int4_minmax_ops, a int4_bloom_ops, b, c int8_minmax_multi_ops)
129-
WITH (pages_per_range=7);
125+
WITH (pages_per_range=7)
126+
WHERE NOT (a BETWEEN1000and1500);
130127

131128
SELECT relname, relpages
132129
FROM pg_class

0 commit comments

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