From a79446bdc46c85ae9e9386e55bc3f7da43523106 Mon Sep 17 00:00:00 2001 From: Tomas Vondra Date: Sat, 13 Apr 2024 22:50:53 +0200 Subject: [PATCH v20240413 4/4] add minmax-multi to the regression test --- contrib/pageinspect/expected/brin.out | 25 ++++++++++++++----------- contrib/pageinspect/sql/brin.sql | 25 ++++++++++++++----------- 2 files changed, 28 insertions(+), 22 deletions(-) diff --git a/contrib/pageinspect/expected/brin.out b/contrib/pageinspect/expected/brin.out index 66b199dba8a..33e565f8300 100644 --- a/contrib/pageinspect/expected/brin.out +++ b/contrib/pageinspect/expected/brin.out @@ -109,24 +109,27 @@ SELECT (COUNT(*) = (SELECT relpages FROM pg_class WHERE relname = 'test2')) AS r DROP TABLE test1; DROP TABLE test2; -- test that parallel index build produces the same BRIN index as serial build -CREATE TABLE brin_parallel_test (a int, b text) WITH (fillfactor=25); +CREATE TABLE brin_parallel_test (a int, b text, c bigint) WITH (fillfactor=40); -- a mix of NULLs and non-NULL values INSERT INTO brin_parallel_test SELECT (CASE WHEN (mod(i,231) = 0) OR (i BETWEEN 3500 AND 4000) THEN NULL ELSE i END), - (CASE WHEN (mod(i,233) = 0) OR (i BETWEEN 3750 AND 4250) THEN NULL ELSE md5(i::text) END) + (CASE WHEN (mod(i,233) = 0) OR (i BETWEEN 3750 AND 4250) THEN NULL ELSE md5(i::text) END), + (CASE WHEN (mod(i,233) = 0) OR (i BETWEEN 3750 AND 4250) THEN NULL ELSE (i/40) + mod(i,8) END) FROM generate_series(1,10000) S(i); -- delete a couple pages, to make a range empty DELETE FROM brin_parallel_test WHERE a BETWEEN 1000 and 2000; -- vacuum to make the ranges actually empty VACUUM brin_parallel_test; --- We build index with different BRIN opclasses, but we can't use minmax-multi --- because the summary is not deterministic (i.e. the order of values affects --- how ranges get merged, and if workers see random subsets between runs, that --- affects the resulting summaries too). The summaries are equivalent in the --- sense that should give the same query results, but look different enough to --- not be very useful/convenient for this test. +-- We build index with different BRIN opclasses - minmax, bloom and minmax-multi. +-- With minmax-multi we need to be more careful, because the summary is not +-- entirely deterministic - the order of values affects how ranges get merged, +-- and if workers see random subsets between runs, that affects the resulting +-- summaries too). The summaries would be equivalent in the sense that should +-- give the same query results, but are not equal for EXCEPT to work. To work +-- around that, we generate only small number of values to keep exact values, +-- in which case the ordering does not matter. SET max_parallel_maintenance_workers = 0; -CREATE INDEX brin_test_serial_idx ON brin_parallel_test USING brin (a int4_minmax_ops, a int4_bloom_ops, b) WITH (pages_per_range=7); +CREATE INDEX brin_test_serial_idx ON brin_parallel_test USING brin (a int4_minmax_ops, a int4_bloom_ops, b, c int8_minmax_multi_ops) WITH (pages_per_range=7); -- XXX we have to set some parameters to force parallel build, both because -- there's a requirement for table size and plan_create_index_workers assumes -- each worker will use work_mem=32MB for sorting (which works for btree, but @@ -134,7 +137,7 @@ CREATE INDEX brin_test_serial_idx ON brin_parallel_test USING brin (a int4_minma SET min_parallel_table_scan_size = 0; SET max_parallel_maintenance_workers = 4; SET maintenance_work_mem = '128MB'; -CREATE INDEX brin_test_parallel_idx ON brin_parallel_test USING brin (a int4_minmax_ops, a int4_bloom_ops, b) WITH (pages_per_range=7); +CREATE INDEX brin_test_parallel_idx ON brin_parallel_test USING brin (a int4_minmax_ops, a int4_bloom_ops, b, c int8_minmax_multi_ops) WITH (pages_per_range=7); SELECT relname, relpages FROM pg_class WHERE relname IN ('brin_test_serial_idx', 'brin_test_parallel_idx') ORDER BY relname; relname | relpages ------------------------+---------- @@ -174,7 +177,7 @@ SELECT * FROM brin_page_items(get_raw_page('brin_test_parallel_idx', 3), 'brin_t -- to force fallback to serial build, and repeat the checks DROP INDEX brin_test_parallel_idx; SET max_parallel_workers = 0; -CREATE INDEX brin_test_parallel_idx ON brin_parallel_test USING brin (a int4_minmax_ops, a int4_bloom_ops, b) WITH (pages_per_range=7); +CREATE INDEX brin_test_parallel_idx ON brin_parallel_test USING brin (a int4_minmax_ops, a int4_bloom_ops, b, c int8_minmax_multi_ops) WITH (pages_per_range=7); SELECT relname, relpages FROM pg_class WHERE relname IN ('brin_test_serial_idx', 'brin_test_parallel_idx') ORDER BY relname; relname | relpages ------------------------+---------- diff --git a/contrib/pageinspect/sql/brin.sql b/contrib/pageinspect/sql/brin.sql index affd805bb27..f113de39c5d 100644 --- a/contrib/pageinspect/sql/brin.sql +++ b/contrib/pageinspect/sql/brin.sql @@ -56,12 +56,13 @@ DROP TABLE test1; DROP TABLE test2; -- test that parallel index build produces the same BRIN index as serial build -CREATE TABLE brin_parallel_test (a int, b text) WITH (fillfactor=25); +CREATE TABLE brin_parallel_test (a int, b text, c bigint) WITH (fillfactor=40); -- a mix of NULLs and non-NULL values INSERT INTO brin_parallel_test SELECT (CASE WHEN (mod(i,231) = 0) OR (i BETWEEN 3500 AND 4000) THEN NULL ELSE i END), - (CASE WHEN (mod(i,233) = 0) OR (i BETWEEN 3750 AND 4250) THEN NULL ELSE md5(i::text) END) + (CASE WHEN (mod(i,233) = 0) OR (i BETWEEN 3750 AND 4250) THEN NULL ELSE md5(i::text) END), + (CASE WHEN (mod(i,233) = 0) OR (i BETWEEN 3750 AND 4250) THEN NULL ELSE (i/40) + mod(i,8) END) FROM generate_series(1,10000) S(i); -- delete a couple pages, to make a range empty @@ -70,14 +71,16 @@ DELETE FROM brin_parallel_test WHERE a BETWEEN 1000 and 2000; -- vacuum to make the ranges actually empty VACUUM brin_parallel_test; --- We build index with different BRIN opclasses, but we can't use minmax-multi --- because the summary is not deterministic (i.e. the order of values affects --- how ranges get merged, and if workers see random subsets between runs, that --- affects the resulting summaries too). The summaries are equivalent in the --- sense that should give the same query results, but look different enough to --- not be very useful/convenient for this test. +-- We build index with different BRIN opclasses - minmax, bloom and minmax-multi. +-- With minmax-multi we need to be more careful, because the summary is not +-- entirely deterministic - the order of values affects how ranges get merged, +-- and if workers see random subsets between runs, that affects the resulting +-- summaries too). The summaries would be equivalent in the sense that should +-- give the same query results, but are not equal for EXCEPT to work. To work +-- around that, we generate only small number of values to keep exact values, +-- in which case the ordering does not matter. SET max_parallel_maintenance_workers = 0; -CREATE INDEX brin_test_serial_idx ON brin_parallel_test USING brin (a int4_minmax_ops, a int4_bloom_ops, b) WITH (pages_per_range=7); +CREATE INDEX brin_test_serial_idx ON brin_parallel_test USING brin (a int4_minmax_ops, a int4_bloom_ops, b, c int8_minmax_multi_ops) WITH (pages_per_range=7); -- XXX we have to set some parameters to force parallel build, both because -- there's a requirement for table size and plan_create_index_workers assumes @@ -86,7 +89,7 @@ CREATE INDEX brin_test_serial_idx ON brin_parallel_test USING brin (a int4_minma SET min_parallel_table_scan_size = 0; SET max_parallel_maintenance_workers = 4; SET maintenance_work_mem = '128MB'; -CREATE INDEX brin_test_parallel_idx ON brin_parallel_test USING brin (a int4_minmax_ops, a int4_bloom_ops, b) WITH (pages_per_range=7); +CREATE INDEX brin_test_parallel_idx ON brin_parallel_test USING brin (a int4_minmax_ops, a int4_bloom_ops, b, c int8_minmax_multi_ops) WITH (pages_per_range=7); SELECT relname, relpages FROM pg_class WHERE relname IN ('brin_test_serial_idx', 'brin_test_parallel_idx') ORDER BY relname; @@ -113,7 +116,7 @@ SELECT * FROM brin_page_items(get_raw_page('brin_test_parallel_idx', 3), 'brin_t DROP INDEX brin_test_parallel_idx; SET max_parallel_workers = 0; -CREATE INDEX brin_test_parallel_idx ON brin_parallel_test USING brin (a int4_minmax_ops, a int4_bloom_ops, b) WITH (pages_per_range=7); +CREATE INDEX brin_test_parallel_idx ON brin_parallel_test USING brin (a int4_minmax_ops, a int4_bloom_ops, b, c int8_minmax_multi_ops) WITH (pages_per_range=7); SELECT relname, relpages FROM pg_class WHERE relname IN ('brin_test_serial_idx', 'brin_test_parallel_idx') ORDER BY relname; -- 2.44.0