summaryrefslogtreecommitdiffstats
path: root/src/test/regress/sql/brin.sql
diff options
context:
space:
mode:
Diffstat (limited to 'src/test/regress/sql/brin.sql')
-rw-r--r--src/test/regress/sql/brin.sql517
1 files changed, 517 insertions, 0 deletions
diff --git a/src/test/regress/sql/brin.sql b/src/test/regress/sql/brin.sql
new file mode 100644
index 0000000..e68e9e1
--- /dev/null
+++ b/src/test/regress/sql/brin.sql
@@ -0,0 +1,517 @@
+CREATE TABLE brintest (byteacol bytea,
+ charcol "char",
+ namecol name,
+ int8col bigint,
+ int2col smallint,
+ int4col integer,
+ textcol text,
+ oidcol oid,
+ tidcol tid,
+ float4col real,
+ float8col double precision,
+ macaddrcol macaddr,
+ inetcol inet,
+ cidrcol cidr,
+ bpcharcol character,
+ datecol date,
+ timecol time without time zone,
+ timestampcol timestamp without time zone,
+ timestamptzcol timestamp with time zone,
+ intervalcol interval,
+ timetzcol time with time zone,
+ bitcol bit(10),
+ varbitcol bit varying(16),
+ numericcol numeric,
+ uuidcol uuid,
+ int4rangecol int4range,
+ lsncol pg_lsn,
+ boxcol box
+) WITH (fillfactor=10, autovacuum_enabled=off);
+
+INSERT INTO brintest SELECT
+ repeat(stringu1, 8)::bytea,
+ substr(stringu1, 1, 1)::"char",
+ stringu1::name, 142857 * tenthous,
+ thousand,
+ twothousand,
+ repeat(stringu1, 8),
+ unique1::oid,
+ format('(%s,%s)', tenthous, twenty)::tid,
+ (four + 1.0)/(hundred+1),
+ odd::float8 / (tenthous + 1),
+ format('%s:00:%s:00:%s:00', to_hex(odd), to_hex(even), to_hex(hundred))::macaddr,
+ inet '10.2.3.4/24' + tenthous,
+ cidr '10.2.3/24' + tenthous,
+ substr(stringu1, 1, 1)::bpchar,
+ date '1995-08-15' + tenthous,
+ time '01:20:30' + thousand * interval '18.5 second',
+ timestamp '1942-07-23 03:05:09' + tenthous * interval '36.38 hours',
+ timestamptz '1972-10-10 03:00' + thousand * interval '1 hour',
+ justify_days(justify_hours(tenthous * interval '12 minutes')),
+ timetz '01:30:20+02' + hundred * interval '15 seconds',
+ thousand::bit(10),
+ tenthous::bit(16)::varbit,
+ 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,
+ int4range(thousand, twothousand),
+ format('%s/%s%s', odd, even, tenthous)::pg_lsn,
+ box(point(odd, even), point(thousand, twothousand))
+FROM tenk1 ORDER BY unique2 LIMIT 100;
+
+-- throw in some NULL's and different values
+INSERT INTO brintest (inetcol, cidrcol, int4rangecol) SELECT
+ inet 'fe80::6e40:8ff:fea9:8c46' + tenthous,
+ cidr 'fe80::6e40:8ff:fea9:8c46' + tenthous,
+ 'empty'::int4range
+FROM tenk1 ORDER BY thousand, tenthous LIMIT 25;
+
+CREATE INDEX brinidx ON brintest USING brin (
+ byteacol,
+ charcol,
+ namecol,
+ int8col,
+ int2col,
+ int4col,
+ textcol,
+ oidcol,
+ tidcol,
+ float4col,
+ float8col,
+ macaddrcol,
+ inetcol inet_inclusion_ops,
+ inetcol inet_minmax_ops,
+ cidrcol inet_inclusion_ops,
+ cidrcol inet_minmax_ops,
+ bpcharcol,
+ datecol,
+ timecol,
+ timestampcol,
+ timestamptzcol,
+ intervalcol,
+ timetzcol,
+ bitcol,
+ varbitcol,
+ numericcol,
+ uuidcol,
+ int4rangecol,
+ lsncol,
+ boxcol
+) with (pages_per_range = 1);
+
+CREATE TABLE brinopers (colname name, typ text,
+ op text[], value text[], matches int[],
+ check (cardinality(op) = cardinality(value)),
+ check (cardinality(op) = cardinality(matches)));
+
+INSERT INTO brinopers VALUES
+ ('byteacol', 'bytea',
+ '{>, >=, =, <=, <}',
+ '{AAAAAA, AAAAAA, BNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAA, ZZZZZZ, ZZZZZZ}',
+ '{100, 100, 1, 100, 100}'),
+ ('charcol', '"char"',
+ '{>, >=, =, <=, <}',
+ '{A, A, M, Z, Z}',
+ '{97, 100, 6, 100, 98}'),
+ ('namecol', 'name',
+ '{>, >=, =, <=, <}',
+ '{AAAAAA, AAAAAA, MAAAAA, ZZAAAA, ZZAAAA}',
+ '{100, 100, 2, 100, 100}'),
+ ('int2col', 'int2',
+ '{>, >=, =, <=, <}',
+ '{0, 0, 800, 999, 999}',
+ '{100, 100, 1, 100, 100}'),
+ ('int2col', 'int4',
+ '{>, >=, =, <=, <}',
+ '{0, 0, 800, 999, 1999}',
+ '{100, 100, 1, 100, 100}'),
+ ('int2col', 'int8',
+ '{>, >=, =, <=, <}',
+ '{0, 0, 800, 999, 1428427143}',
+ '{100, 100, 1, 100, 100}'),
+ ('int4col', 'int2',
+ '{>, >=, =, <=, <}',
+ '{0, 0, 800, 1999, 1999}',
+ '{100, 100, 1, 100, 100}'),
+ ('int4col', 'int4',
+ '{>, >=, =, <=, <}',
+ '{0, 0, 800, 1999, 1999}',
+ '{100, 100, 1, 100, 100}'),
+ ('int4col', 'int8',
+ '{>, >=, =, <=, <}',
+ '{0, 0, 800, 1999, 1428427143}',
+ '{100, 100, 1, 100, 100}'),
+ ('int8col', 'int2',
+ '{>, >=}',
+ '{0, 0}',
+ '{100, 100}'),
+ ('int8col', 'int4',
+ '{>, >=}',
+ '{0, 0}',
+ '{100, 100}'),
+ ('int8col', 'int8',
+ '{>, >=, =, <=, <}',
+ '{0, 0, 1257141600, 1428427143, 1428427143}',
+ '{100, 100, 1, 100, 100}'),
+ ('textcol', 'text',
+ '{>, >=, =, <=, <}',
+ '{ABABAB, ABABAB, BNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAA, ZZAAAA, ZZAAAA}',
+ '{100, 100, 1, 100, 100}'),
+ ('oidcol', 'oid',
+ '{>, >=, =, <=, <}',
+ '{0, 0, 8800, 9999, 9999}',
+ '{100, 100, 1, 100, 100}'),
+ ('tidcol', 'tid',
+ '{>, >=, =, <=, <}',
+ '{"(0,0)", "(0,0)", "(8800,0)", "(9999,19)", "(9999,19)"}',
+ '{100, 100, 1, 100, 100}'),
+ ('float4col', 'float4',
+ '{>, >=, =, <=, <}',
+ '{0.0103093, 0.0103093, 1, 1, 1}',
+ '{100, 100, 4, 100, 96}'),
+ ('float4col', 'float8',
+ '{>, >=, =, <=, <}',
+ '{0.0103093, 0.0103093, 1, 1, 1}',
+ '{100, 100, 4, 100, 96}'),
+ ('float8col', 'float4',
+ '{>, >=, =, <=, <}',
+ '{0, 0, 0, 1.98, 1.98}',
+ '{99, 100, 1, 100, 100}'),
+ ('float8col', 'float8',
+ '{>, >=, =, <=, <}',
+ '{0, 0, 0, 1.98, 1.98}',
+ '{99, 100, 1, 100, 100}'),
+ ('macaddrcol', 'macaddr',
+ '{>, >=, =, <=, <}',
+ '{00:00:01:00:00:00, 00:00:01:00:00:00, 2c:00:2d:00:16:00, ff:fe:00:00:00:00, ff:fe:00:00:00:00}',
+ '{99, 100, 2, 100, 100}'),
+ ('inetcol', 'inet',
+ '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}',
+ '{10/8, 10.2.14.231/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14.231/24, 10.2.14.231/25, 10.2.14.231/8, 0/0}',
+ '{100, 1, 100, 100, 125, 125, 2, 2, 100, 100}'),
+ ('inetcol', 'inet',
+ '{&&, >>=, <<=, =}',
+ '{fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46, fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46}',
+ '{25, 1, 25, 1}'),
+ ('inetcol', 'cidr',
+ '{&&, <, <=, >, >=, >>=, >>, <<=, <<}',
+ '{10/8, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14/24, 10.2.14/25, 10/8, 0/0}',
+ '{100, 100, 100, 125, 125, 2, 2, 100, 100}'),
+ ('inetcol', 'cidr',
+ '{&&, >>=, <<=, =}',
+ '{fe80::/32, fe80::6e40:8ff:fea9:8c46, fe80::/32, fe80::6e40:8ff:fea9:8c46}',
+ '{25, 1, 25, 1}'),
+ ('cidrcol', 'inet',
+ '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}',
+ '{10/8, 10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14.231/24, 10.2.14.231/25, 10.2.14.231/8, 0/0}',
+ '{100, 2, 100, 100, 125, 125, 2, 2, 100, 100}'),
+ ('cidrcol', 'inet',
+ '{&&, >>=, <<=, =}',
+ '{fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46, fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46}',
+ '{25, 1, 25, 1}'),
+ ('cidrcol', 'cidr',
+ '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}',
+ '{10/8, 10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0, 10.2.14/24, 10.2.14/25, 10/8, 0/0}',
+ '{100, 2, 100, 100, 125, 125, 2, 2, 100, 100}'),
+ ('cidrcol', 'cidr',
+ '{&&, >>=, <<=, =}',
+ '{fe80::/32, fe80::6e40:8ff:fea9:8c46, fe80::/32, fe80::6e40:8ff:fea9:8c46}',
+ '{25, 1, 25, 1}'),
+ ('bpcharcol', 'bpchar',
+ '{>, >=, =, <=, <}',
+ '{A, A, W, Z, Z}',
+ '{97, 100, 6, 100, 98}'),
+ ('datecol', 'date',
+ '{>, >=, =, <=, <}',
+ '{1995-08-15, 1995-08-15, 2009-12-01, 2022-12-30, 2022-12-30}',
+ '{100, 100, 1, 100, 100}'),
+ ('timecol', 'time',
+ '{>, >=, =, <=, <}',
+ '{01:20:30, 01:20:30, 02:28:57, 06:28:31.5, 06:28:31.5}',
+ '{100, 100, 1, 100, 100}'),
+ ('timestampcol', 'timestamp',
+ '{>, >=, =, <=, <}',
+ '{1942-07-23 03:05:09, 1942-07-23 03:05:09, 1964-03-24 19:26:45, 1984-01-20 22:42:21, 1984-01-20 22:42:21}',
+ '{100, 100, 1, 100, 100}'),
+ ('timestampcol', 'timestamptz',
+ '{>, >=, =, <=, <}',
+ '{1942-07-23 03:05:09, 1942-07-23 03:05:09, 1964-03-24 19:26:45, 1984-01-20 22:42:21, 1984-01-20 22:42:21}',
+ '{100, 100, 1, 100, 100}'),
+ ('timestamptzcol', 'timestamptz',
+ '{>, >=, =, <=, <}',
+ '{1972-10-10 03:00:00-04, 1972-10-10 03:00:00-04, 1972-10-19 09:00:00-07, 1972-11-20 19:00:00-03, 1972-11-20 19:00:00-03}',
+ '{100, 100, 1, 100, 100}'),
+ ('intervalcol', 'interval',
+ '{>, >=, =, <=, <}',
+ '{00:00:00, 00:00:00, 1 mons 13 days 12:24, 2 mons 23 days 07:48:00, 1 year}',
+ '{100, 100, 1, 100, 100}'),
+ ('timetzcol', 'timetz',
+ '{>, >=, =, <=, <}',
+ '{01:30:20+02, 01:30:20+02, 01:35:50+02, 23:55:05+02, 23:55:05+02}',
+ '{99, 100, 2, 100, 100}'),
+ ('bitcol', 'bit(10)',
+ '{>, >=, =, <=, <}',
+ '{0000000010, 0000000010, 0011011110, 1111111000, 1111111000}',
+ '{100, 100, 1, 100, 100}'),
+ ('varbitcol', 'varbit(16)',
+ '{>, >=, =, <=, <}',
+ '{0000000000000100, 0000000000000100, 0001010001100110, 1111111111111000, 1111111111111000}',
+ '{100, 100, 1, 100, 100}'),
+ ('numericcol', 'numeric',
+ '{>, >=, =, <=, <}',
+ '{0.00, 0.01, 2268164.347826086956521739130434782609, 99470151.9, 99470151.9}',
+ '{100, 100, 1, 100, 100}'),
+ ('uuidcol', 'uuid',
+ '{>, >=, =, <=, <}',
+ '{00040004-0004-0004-0004-000400040004, 00040004-0004-0004-0004-000400040004, 52225222-5222-5222-5222-522252225222, 99989998-9998-9998-9998-999899989998, 99989998-9998-9998-9998-999899989998}',
+ '{100, 100, 1, 100, 100}'),
+ ('int4rangecol', 'int4range',
+ '{<<, &<, &&, &>, >>, @>, <@, =, <, <=, >, >=}',
+ '{"[10000,)","[10000,)","(,]","[3,4)","[36,44)","(1500,1501]","[3,4)","[222,1222)","[36,44)","[43,1043)","[367,4466)","[519,)"}',
+ '{53, 53, 53, 53, 50, 22, 72, 1, 74, 75, 34, 21}'),
+ ('int4rangecol', 'int4range',
+ '{@>, <@, =, <=, >, >=}',
+ '{empty, empty, empty, empty, empty, empty}',
+ '{125, 72, 72, 72, 53, 125}'),
+ ('int4rangecol', 'int4',
+ '{@>}',
+ '{1500}',
+ '{22}'),
+ ('lsncol', 'pg_lsn',
+ '{>, >=, =, <=, <, IS, IS NOT}',
+ '{0/1200, 0/1200, 44/455222, 198/1999799, 198/1999799, NULL, NULL}',
+ '{100, 100, 1, 100, 100, 25, 100}'),
+ ('boxcol', 'point',
+ '{@>}',
+ '{"(500,43)"}',
+ '{11}'),
+ ('boxcol', 'box',
+ '{<<, &<, &&, &>, >>, <<|, &<|, |&>, |>>, @>, <@, ~=}',
+ '{"((1000,2000),(3000,4000))","((1,2),(3000,4000))","((1,2),(3000,4000))","((1,2),(3000,4000))","((1,2),(3,4))","((1000,2000),(3000,4000))","((1,2000),(3,4000))","((1000,2),(3000,4))","((1,2),(3,4))","((1,2),(300,400))","((1,2),(3000,4000))","((222,1222),(44,45))"}',
+ '{100, 100, 100, 99, 96, 100, 100, 99, 96, 1, 99, 1}');
+
+DO $x$
+DECLARE
+ r record;
+ r2 record;
+ cond text;
+ idx_ctids tid[];
+ ss_ctids tid[];
+ count int;
+ plan_ok bool;
+ plan_line text;
+BEGIN
+ FOR r IN SELECT colname, oper, typ, value[ordinality], matches[ordinality] FROM brinopers, unnest(op) WITH ORDINALITY AS oper LOOP
+
+ -- prepare the condition
+ IF r.value IS NULL THEN
+ cond := format('%I %s %L', r.colname, r.oper, r.value);
+ ELSE
+ cond := format('%I %s %L::%s', r.colname, r.oper, r.value, r.typ);
+ END IF;
+
+ -- run the query using the brin index
+ SET enable_seqscan = 0;
+ SET enable_bitmapscan = 1;
+
+ plan_ok := false;
+ FOR plan_line IN EXECUTE format($y$EXPLAIN SELECT array_agg(ctid) FROM brintest WHERE %s $y$, cond) LOOP
+ IF plan_line LIKE '%Bitmap Heap Scan on brintest%' THEN
+ plan_ok := true;
+ END IF;
+ END LOOP;
+ IF NOT plan_ok THEN
+ RAISE WARNING 'did not get bitmap indexscan plan for %', r;
+ END IF;
+
+ EXECUTE format($y$SELECT array_agg(ctid) FROM brintest WHERE %s $y$, cond)
+ INTO idx_ctids;
+
+ -- run the query using a seqscan
+ SET enable_seqscan = 1;
+ SET enable_bitmapscan = 0;
+
+ plan_ok := false;
+ FOR plan_line IN EXECUTE format($y$EXPLAIN SELECT array_agg(ctid) FROM brintest WHERE %s $y$, cond) LOOP
+ IF plan_line LIKE '%Seq Scan on brintest%' THEN
+ plan_ok := true;
+ END IF;
+ END LOOP;
+ IF NOT plan_ok THEN
+ RAISE WARNING 'did not get seqscan plan for %', r;
+ END IF;
+
+ EXECUTE format($y$SELECT array_agg(ctid) FROM brintest WHERE %s $y$, cond)
+ INTO ss_ctids;
+
+ -- make sure both return the same results
+ count := array_length(idx_ctids, 1);
+
+ IF NOT (count = array_length(ss_ctids, 1) AND
+ idx_ctids @> ss_ctids AND
+ idx_ctids <@ ss_ctids) THEN
+ -- report the results of each scan to make the differences obvious
+ RAISE WARNING 'something not right in %: count %', r, count;
+ SET enable_seqscan = 1;
+ SET enable_bitmapscan = 0;
+ FOR r2 IN EXECUTE 'SELECT ' || r.colname || ' FROM brintest WHERE ' || cond LOOP
+ RAISE NOTICE 'seqscan: %', r2;
+ END LOOP;
+
+ SET enable_seqscan = 0;
+ SET enable_bitmapscan = 1;
+ FOR r2 IN EXECUTE 'SELECT ' || r.colname || ' FROM brintest WHERE ' || cond LOOP
+ RAISE NOTICE 'bitmapscan: %', r2;
+ END LOOP;
+ END IF;
+
+ -- make sure we found expected number of matches
+ IF count != r.matches THEN RAISE WARNING 'unexpected number of results % for %', count, r; END IF;
+ END LOOP;
+END;
+$x$;
+
+RESET enable_seqscan;
+RESET enable_bitmapscan;
+
+INSERT INTO brintest SELECT
+ repeat(stringu1, 42)::bytea,
+ substr(stringu1, 1, 1)::"char",
+ stringu1::name, 142857 * tenthous,
+ thousand,
+ twothousand,
+ repeat(stringu1, 42),
+ unique1::oid,
+ format('(%s,%s)', tenthous, twenty)::tid,
+ (four + 1.0)/(hundred+1),
+ odd::float8 / (tenthous + 1),
+ format('%s:00:%s:00:%s:00', to_hex(odd), to_hex(even), to_hex(hundred))::macaddr,
+ inet '10.2.3.4' + tenthous,
+ cidr '10.2.3/24' + tenthous,
+ substr(stringu1, 1, 1)::bpchar,
+ date '1995-08-15' + tenthous,
+ time '01:20:30' + thousand * interval '18.5 second',
+ timestamp '1942-07-23 03:05:09' + tenthous * interval '36.38 hours',
+ timestamptz '1972-10-10 03:00' + thousand * interval '1 hour',
+ justify_days(justify_hours(tenthous * interval '12 minutes')),
+ timetz '01:30:20' + hundred * interval '15 seconds',
+ thousand::bit(10),
+ tenthous::bit(16)::varbit,
+ 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,
+ int4range(thousand, twothousand),
+ format('%s/%s%s', odd, even, tenthous)::pg_lsn,
+ box(point(odd, even), point(thousand, twothousand))
+FROM tenk1 ORDER BY unique2 LIMIT 5 OFFSET 5;
+
+SELECT brin_desummarize_range('brinidx', 0);
+VACUUM brintest; -- force a summarization cycle in brinidx
+
+UPDATE brintest SET int8col = int8col * int4col;
+UPDATE brintest SET textcol = '' WHERE textcol IS NOT NULL;
+
+-- Tests for brin_summarize_new_values
+SELECT brin_summarize_new_values('brintest'); -- error, not an index
+SELECT brin_summarize_new_values('tenk1_unique1'); -- error, not a BRIN index
+SELECT brin_summarize_new_values('brinidx'); -- ok, no change expected
+
+-- Tests for brin_desummarize_range
+SELECT brin_desummarize_range('brinidx', -1); -- error, invalid range
+SELECT brin_desummarize_range('brinidx', 0);
+SELECT brin_desummarize_range('brinidx', 0);
+SELECT brin_desummarize_range('brinidx', 100000000);
+
+-- Test brin_summarize_range
+CREATE TABLE brin_summarize (
+ value int
+) WITH (fillfactor=10, autovacuum_enabled=false);
+CREATE INDEX brin_summarize_idx ON brin_summarize USING brin (value) WITH (pages_per_range=2);
+-- Fill a few pages
+DO $$
+DECLARE curtid tid;
+BEGIN
+ LOOP
+ INSERT INTO brin_summarize VALUES (1) RETURNING ctid INTO curtid;
+ EXIT WHEN curtid > tid '(2, 0)';
+ END LOOP;
+END;
+$$;
+
+-- summarize one range
+SELECT brin_summarize_range('brin_summarize_idx', 0);
+-- nothing: already summarized
+SELECT brin_summarize_range('brin_summarize_idx', 1);
+-- summarize one range
+SELECT brin_summarize_range('brin_summarize_idx', 2);
+-- nothing: page doesn't exist in table
+SELECT brin_summarize_range('brin_summarize_idx', 4294967295);
+-- invalid block number values
+SELECT brin_summarize_range('brin_summarize_idx', -1);
+SELECT brin_summarize_range('brin_summarize_idx', 4294967296);
+
+-- test value merging in add_value
+CREATE TABLE brintest_2 (n numrange);
+CREATE INDEX brinidx_2 ON brintest_2 USING brin (n);
+INSERT INTO brintest_2 VALUES ('empty');
+INSERT INTO brintest_2 VALUES (numrange(0, 2^1000::numeric));
+INSERT INTO brintest_2 VALUES ('(-1, 0)');
+
+SELECT brin_desummarize_range('brinidx', 0);
+SELECT brin_summarize_range('brinidx', 0);
+DROP TABLE brintest_2;
+
+-- test brin cost estimates behave sanely based on correlation of values
+CREATE TABLE brin_test (a INT, b INT);
+INSERT INTO brin_test SELECT x/100,x%100 FROM generate_series(1,10000) x(x);
+CREATE INDEX brin_test_a_idx ON brin_test USING brin (a) WITH (pages_per_range = 2);
+CREATE INDEX brin_test_b_idx ON brin_test USING brin (b) WITH (pages_per_range = 2);
+VACUUM ANALYZE brin_test;
+
+-- Ensure brin index is used when columns are perfectly correlated
+EXPLAIN (COSTS OFF) SELECT * FROM brin_test WHERE a = 1;
+-- Ensure brin index is not used when values are not correlated
+EXPLAIN (COSTS OFF) SELECT * FROM brin_test WHERE b = 1;
+
+-- make sure data are properly de-toasted in BRIN index
+CREATE TABLE brintest_3 (a text, b text, c text, d text);
+
+-- long random strings (~2000 chars each, so ~6kB for min/max on two
+-- columns) to trigger toasting
+WITH rand_value AS (SELECT string_agg(md5(i::text),'') AS val FROM generate_series(1,60) s(i))
+INSERT INTO brintest_3
+SELECT val, val, val, val FROM rand_value;
+
+CREATE INDEX brin_test_toast_idx ON brintest_3 USING brin (b, c);
+DELETE FROM brintest_3;
+
+-- We need to wait a bit for all transactions to complete, so that the
+-- vacuum actually removes the TOAST rows. Creating an index concurrently
+-- is a one way to achieve that, because it does exactly such wait.
+CREATE INDEX CONCURRENTLY brin_test_temp_idx ON brintest_3(a);
+DROP INDEX brin_test_temp_idx;
+
+-- vacuum the table, to discard TOAST data
+VACUUM brintest_3;
+
+-- retry insert with a different random-looking (but deterministic) value
+-- the value is different, and so should replace either min or max in the
+-- brin summary
+WITH rand_value AS (SELECT string_agg(md5((-i)::text),'') AS val FROM generate_series(1,60) s(i))
+INSERT INTO brintest_3
+SELECT val, val, val, val FROM rand_value;
+
+-- now try some queries, accessing the brin index
+SET enable_seqscan = off;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM brintest_3 WHERE b < '0';
+
+SELECT * FROM brintest_3 WHERE b < '0';
+
+DROP TABLE brintest_3;
+RESET enable_seqscan;
+
+-- test an unlogged table, mostly to get coverage of brinbuildempty
+CREATE UNLOGGED TABLE brintest_unlogged (n numrange);
+CREATE INDEX brinidx_unlogged ON brintest_unlogged USING brin (n);
+INSERT INTO brintest_unlogged VALUES (numrange(0, 2^1000::numeric));
+DROP TABLE brintest_unlogged;
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-04 02:35:48 +0000