From 86cf9a565069755189e08290343d2d62afdd1f52 Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Fri, 14 Nov 2014 16:27:26 -0300 Subject: [PATCH] Reduce disk footprint of brin regression test Per complaint from Tom. While at it, throw in some extra tests for nulls as well, and make sure that the set of data we insert on the second round is not identical to the first one. Both measures are intended to improve coverage of the test. Also uncomment the ON COMMIT DROP clause on the CREATE TEMP TABLE commands. This doesn't have any effect for someone examining the regression database after the tests are done, but it reduces clutter for those that execute the script directly. --- src/test/regress/expected/brin.out | 21 ++++++++++++--------- src/test/regress/sql/brin.sql | 22 ++++++++++++---------- 2 files changed, 24 insertions(+), 19 deletions(-) diff --git a/src/test/regress/expected/brin.out b/src/test/regress/expected/brin.out index f8be27e5e3..61a544efed 100644 --- a/src/test/regress/expected/brin.out +++ b/src/test/regress/expected/brin.out @@ -1,4 +1,3 @@ -SET synchronous_commit = 0; CREATE TABLE brintest (byteacol bytea, charcol "char", namecol name, @@ -24,7 +23,7 @@ CREATE TABLE brintest (byteacol bytea, numericcol numeric, uuidcol uuid, lsncol pg_lsn -) WITH (fillfactor=50); +) WITH (fillfactor=10); INSERT INTO brintest SELECT repeat(stringu1, 42)::bytea, substr(stringu1, 1, 1)::"char", @@ -50,7 +49,9 @@ INSERT INTO brintest SELECT tenthous::numeric(36,30) * fivethous * even / (hundred + 1), format('%s%s-%s-%s-%s-%s%s%s', to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'))::uuid, format('%s/%s%s', odd, even, tenthous)::pg_lsn -FROM tenk1; +FROM tenk1 LIMIT 5; +-- throw in some NULL-only tuples too +INSERT INTO brintest SELECT NULL FROM tenk1 LIMIT 25; CREATE INDEX brinidx ON brintest USING brin ( byteacol, charcol, @@ -78,6 +79,7 @@ CREATE INDEX brinidx ON brintest USING brin ( uuidcol, lsncol ) with (pages_per_range = 1); +BEGIN; CREATE TABLE brinopers (colname name, op text[], value text[], check (cardinality(op) = cardinality(value))); INSERT INTO brinopers VALUES ('byteacol', '{>, >=, =, <=, <}', '{ZZAAAA, ZZAAAA, AAAAAA, AAAAAA, AAAAAA}'); @@ -104,7 +106,8 @@ INSERT INTO brinopers VALUES ('macaddrcol', '{>, >=, =, <=, <}', '{ff:fe:00:00:0 INSERT INTO brinopers VALUES ('bitcol', '{>, >=, =, <=, <}', '{1111111000, 1111111000, 0000000010, 0000000010, 0000000010}'); INSERT INTO brinopers VALUES ('varbitcol', '{>, >=, =, <=, <}', '{1111111111111000, 1111111111111000, 0000000000000100, 0000000000000100, 0000000000000100}'); INSERT INTO brinopers VALUES ('uuidcol', '{>, >=, =, <=, <}', '{99989998-9998-9998-9998-999899989998, 99989998-9998-9998-9998-999899989998, 00040004-0004-0004-0004-000400040004, 00040004-0004-0004-0004-000400040004, 00040004-0004-0004-0004-000400040005}'); -INSERT INTO brinopers VALUES ('lsncol', '{>, >=, =, <=, <}', '{198/1999799, 198/1999799, 30/312815, 0/1200, 0/1200}'); +INSERT INTO brinopers VALUES ('lsncol', '{>, >=, =, <=, <, IS, IS NOT}', '{198/1999799, 198/1999799, 30/312815, 0/1200, 0/1200, NULL, NULL}'); +COMMIT; DO $x$ DECLARE r record; @@ -122,7 +125,7 @@ BEGIN -- run the query using the brin index SET enable_seqscan = 0; SET enable_bitmapscan = 1; - EXECUTE format('create temp table %s (tid tid) /* ON COMMIT DROP*/', tabname); + EXECUTE format('create temp table %s (tid tid) ON COMMIT DROP', tabname); EXECUTE query; -- run the query using a seqscan @@ -130,7 +133,7 @@ BEGIN SET enable_bitmapscan = 0; query = format($y$INSERT INTO %s SELECT ctid FROM brintest WHERE %s %s %L $y$, tabname_ss, r.colname, r.oper, r.value); - EXECUTE format('create temp table %s (tid tid) /* ON COMMIT DROP */', tabname_ss); + EXECUTE format('create temp table %s (tid tid) ON COMMIT DROP', tabname_ss); EXECUTE query; -- make sure both return the same results @@ -168,12 +171,12 @@ INSERT INTO brintest SELECT tenthous::numeric(36,30) * fivethous * even / (hundred + 1), format('%s%s-%s-%s-%s-%s%s%s', to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'))::uuid, format('%s/%s%s', odd, even, tenthous)::pg_lsn -FROM tenk1; +FROM tenk1 LIMIT 5 OFFSET 5; SELECT brin_summarize_new_values('brinidx'::regclass); brin_summarize_new_values --------------------------- - 2000 + 5 (1 row) UPDATE brintest SET int8col = int8col * int4col; -SET synchronous_commit = 1; +UPDATE brintest SET textcol = '' WHERE textcol IS NOT NULL; diff --git a/src/test/regress/sql/brin.sql b/src/test/regress/sql/brin.sql index 244652f4c4..b1d2c5c8ea 100644 --- a/src/test/regress/sql/brin.sql +++ b/src/test/regress/sql/brin.sql @@ -1,5 +1,3 @@ -SET synchronous_commit = 0; - CREATE TABLE brintest (byteacol bytea, charcol "char", namecol name, @@ -25,7 +23,7 @@ CREATE TABLE brintest (byteacol bytea, numericcol numeric, uuidcol uuid, lsncol pg_lsn -) WITH (fillfactor=50); +) WITH (fillfactor=10); INSERT INTO brintest SELECT repeat(stringu1, 42)::bytea, @@ -52,7 +50,10 @@ INSERT INTO brintest SELECT tenthous::numeric(36,30) * fivethous * even / (hundred + 1), format('%s%s-%s-%s-%s-%s%s%s', to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'))::uuid, format('%s/%s%s', odd, even, tenthous)::pg_lsn -FROM tenk1; +FROM tenk1 LIMIT 5; + +-- throw in some NULL-only tuples too +INSERT INTO brintest SELECT NULL FROM tenk1 LIMIT 25; CREATE INDEX brinidx ON brintest USING brin ( byteacol, @@ -82,6 +83,7 @@ CREATE INDEX brinidx ON brintest USING brin ( lsncol ) with (pages_per_range = 1); +BEGIN; CREATE TABLE brinopers (colname name, op text[], value text[], check (cardinality(op) = cardinality(value))); @@ -109,7 +111,8 @@ INSERT INTO brinopers VALUES ('macaddrcol', '{>, >=, =, <=, <}', '{ff:fe:00:00:0 INSERT INTO brinopers VALUES ('bitcol', '{>, >=, =, <=, <}', '{1111111000, 1111111000, 0000000010, 0000000010, 0000000010}'); INSERT INTO brinopers VALUES ('varbitcol', '{>, >=, =, <=, <}', '{1111111111111000, 1111111111111000, 0000000000000100, 0000000000000100, 0000000000000100}'); INSERT INTO brinopers VALUES ('uuidcol', '{>, >=, =, <=, <}', '{99989998-9998-9998-9998-999899989998, 99989998-9998-9998-9998-999899989998, 00040004-0004-0004-0004-000400040004, 00040004-0004-0004-0004-000400040004, 00040004-0004-0004-0004-000400040005}'); -INSERT INTO brinopers VALUES ('lsncol', '{>, >=, =, <=, <}', '{198/1999799, 198/1999799, 30/312815, 0/1200, 0/1200}'); +INSERT INTO brinopers VALUES ('lsncol', '{>, >=, =, <=, <, IS, IS NOT}', '{198/1999799, 198/1999799, 30/312815, 0/1200, 0/1200, NULL, NULL}'); +COMMIT; DO $x$ DECLARE @@ -128,7 +131,7 @@ BEGIN -- run the query using the brin index SET enable_seqscan = 0; SET enable_bitmapscan = 1; - EXECUTE format('create temp table %s (tid tid) /* ON COMMIT DROP*/', tabname); + EXECUTE format('create temp table %s (tid tid) ON COMMIT DROP', tabname); EXECUTE query; -- run the query using a seqscan @@ -136,7 +139,7 @@ BEGIN SET enable_bitmapscan = 0; query = format($y$INSERT INTO %s SELECT ctid FROM brintest WHERE %s %s %L $y$, tabname_ss, r.colname, r.oper, r.value); - EXECUTE format('create temp table %s (tid tid) /* ON COMMIT DROP */', tabname_ss); + EXECUTE format('create temp table %s (tid tid) ON COMMIT DROP', tabname_ss); EXECUTE query; -- make sure both return the same results @@ -175,10 +178,9 @@ INSERT INTO brintest SELECT tenthous::numeric(36,30) * fivethous * even / (hundred + 1), format('%s%s-%s-%s-%s-%s%s%s', to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'), to_char(tenthous, 'FM0000'))::uuid, format('%s/%s%s', odd, even, tenthous)::pg_lsn -FROM tenk1; +FROM tenk1 LIMIT 5 OFFSET 5; SELECT brin_summarize_new_values('brinidx'::regclass); UPDATE brintest SET int8col = int8col * int4col; - -SET synchronous_commit = 1; +UPDATE brintest SET textcol = '' WHERE textcol IS NOT NULL; -- 2.40.0