CREATE TABLE brintest_bloom (byteacol bytea, charcol "char", namecol name, int8col bigint, int2col smallint, int4col integer, textcol text, oidcol oid, 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, numericcol numeric, uuidcol uuid, lsncol pg_lsn ) WITH (fillfactor=10); INSERT INTO brintest_bloom SELECT repeat(stringu1, 8)::bytea, substr(stringu1, 1, 1)::"char", stringu1::name, 142857 * tenthous, thousand, twothousand, repeat(stringu1, 8), unique1::oid, (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', 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 ORDER BY unique2 LIMIT 100; -- throw in some NULL's and different values INSERT INTO brintest_bloom (inetcol, cidrcol) SELECT inet 'fe80::6e40:8ff:fea9:8c46' + tenthous, cidr 'fe80::6e40:8ff:fea9:8c46' + tenthous FROM tenk1 ORDER BY thousand, tenthous LIMIT 25; -- test bloom specific index options -- ndistinct must be >= -1.0 CREATE INDEX brinidx_bloom ON brintest_bloom USING brin ( byteacol bytea_bloom_ops(n_distinct_per_range = -1.1) ); -- false_positive_rate must be between 0.0001 and 0.25 CREATE INDEX brinidx_bloom ON brintest_bloom USING brin ( byteacol bytea_bloom_ops(false_positive_rate = 0.00009) ); CREATE INDEX brinidx_bloom ON brintest_bloom USING brin ( byteacol bytea_bloom_ops(false_positive_rate = 0.26) ); CREATE INDEX brinidx_bloom ON brintest_bloom USING brin ( byteacol bytea_bloom_ops, charcol char_bloom_ops, namecol name_bloom_ops, int8col int8_bloom_ops, int2col int2_bloom_ops, int4col int4_bloom_ops, textcol text_bloom_ops, oidcol oid_bloom_ops, float4col float4_bloom_ops, float8col float8_bloom_ops, macaddrcol macaddr_bloom_ops, inetcol inet_bloom_ops, cidrcol inet_bloom_ops, bpcharcol bpchar_bloom_ops, datecol date_bloom_ops, timecol time_bloom_ops, timestampcol timestamp_bloom_ops, timestamptzcol timestamptz_bloom_ops, intervalcol interval_bloom_ops, timetzcol timetz_bloom_ops, numericcol numeric_bloom_ops, uuidcol uuid_bloom_ops, lsncol pg_lsn_bloom_ops ) with (pages_per_range = 1); CREATE TABLE brinopers_bloom (colname name, typ text, op text[], value text[], matches int[], check (cardinality(op) = cardinality(value)), check (cardinality(op) = cardinality(matches))); INSERT INTO brinopers_bloom VALUES ('byteacol', 'bytea', '{=}', '{BNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAA}', '{1}'), ('charcol', '"char"', '{=}', '{M}', '{6}'), ('namecol', 'name', '{=}', '{MAAAAA}', '{2}'), ('int2col', 'int2', '{=}', '{800}', '{1}'), ('int4col', 'int4', '{=}', '{800}', '{1}'), ('int8col', 'int8', '{=}', '{1257141600}', '{1}'), ('textcol', 'text', '{=}', '{BNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAA}', '{1}'), ('oidcol', 'oid', '{=}', '{8800}', '{1}'), ('float4col', 'float4', '{=}', '{1}', '{4}'), ('float8col', 'float8', '{=}', '{0}', '{1}'), ('macaddrcol', 'macaddr', '{=}', '{2c:00:2d:00:16:00}', '{2}'), ('inetcol', 'inet', '{=}', '{10.2.14.231/24}', '{1}'), ('inetcol', 'cidr', '{=}', '{fe80::6e40:8ff:fea9:8c46}', '{1}'), ('cidrcol', 'inet', '{=}', '{10.2.14/24}', '{2}'), ('cidrcol', 'inet', '{=}', '{fe80::6e40:8ff:fea9:8c46}', '{1}'), ('cidrcol', 'cidr', '{=}', '{10.2.14/24}', '{2}'), ('cidrcol', 'cidr', '{=}', '{fe80::6e40:8ff:fea9:8c46}', '{1}'), ('bpcharcol', 'bpchar', '{=}', '{W}', '{6}'), ('datecol', 'date', '{=}', '{2009-12-01}', '{1}'), ('timecol', 'time', '{=}', '{02:28:57}', '{1}'), ('timestampcol', 'timestamp', '{=}', '{1964-03-24 19:26:45}', '{1}'), ('timestamptzcol', 'timestamptz', '{=}', '{1972-10-19 09:00:00-07}', '{1}'), ('intervalcol', 'interval', '{=}', '{1 mons 13 days 12:24}', '{1}'), ('timetzcol', 'timetz', '{=}', '{01:35:50+02}', '{2}'), ('numericcol', 'numeric', '{=}', '{2268164.347826086956521739130434782609}', '{1}'), ('uuidcol', 'uuid', '{=}', '{52225222-5222-5222-5222-522252225222}', '{1}'), ('lsncol', 'pg_lsn', '{=, IS, IS NOT}', '{44/455222, NULL, NULL}', '{1, 25, 100}'); 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_bloom, 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_bloom WHERE %s $y$, cond) LOOP IF plan_line LIKE '%Bitmap Heap Scan on brintest_bloom%' 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_bloom 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_bloom WHERE %s $y$, cond) LOOP IF plan_line LIKE '%Seq Scan on brintest_bloom%' 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_bloom 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_bloom 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_bloom 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_bloom SELECT repeat(stringu1, 42)::bytea, substr(stringu1, 1, 1)::"char", stringu1::name, 142857 * tenthous, thousand, twothousand, repeat(stringu1, 42), unique1::oid, (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', 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 ORDER BY unique2 LIMIT 5 OFFSET 5; SELECT brin_desummarize_range('brinidx_bloom', 0); VACUUM brintest_bloom; -- force a summarization cycle in brinidx UPDATE brintest_bloom SET int8col = int8col * int4col; UPDATE brintest_bloom SET textcol = '' WHERE textcol IS NOT NULL; -- Tests for brin_summarize_new_values SELECT brin_summarize_new_values('brintest_bloom'); -- error, not an index SELECT brin_summarize_new_values('tenk1_unique1'); -- error, not a BRIN index SELECT brin_summarize_new_values('brinidx_bloom'); -- ok, no change expected -- Tests for brin_desummarize_range SELECT brin_desummarize_range('brinidx_bloom', -1); -- error, invalid range SELECT brin_desummarize_range('brinidx_bloom', 0); SELECT brin_desummarize_range('brinidx_bloom', 0); SELECT brin_desummarize_range('brinidx_bloom', 100000000); -- Test brin_summarize_range CREATE TABLE brin_summarize_bloom ( value int ) WITH (fillfactor=10, autovacuum_enabled=false); CREATE INDEX brin_summarize_bloom_idx ON brin_summarize_bloom USING brin (value) WITH (pages_per_range=2); -- Fill a few pages DO $$ DECLARE curtid tid; BEGIN LOOP INSERT INTO brin_summarize_bloom VALUES (1) RETURNING ctid INTO curtid; EXIT WHEN curtid > tid '(2, 0)'; END LOOP; END; $$; -- summarize one range SELECT brin_summarize_range('brin_summarize_bloom_idx', 0); -- nothing: already summarized SELECT brin_summarize_range('brin_summarize_bloom_idx', 1); -- summarize one range SELECT brin_summarize_range('brin_summarize_bloom_idx', 2); -- nothing: page doesn't exist in table SELECT brin_summarize_range('brin_summarize_bloom_idx', 4294967295); -- invalid block number values SELECT brin_summarize_range('brin_summarize_bloom_idx', -1); SELECT brin_summarize_range('brin_summarize_bloom_idx', 4294967296); -- test brin cost estimates behave sanely based on correlation of values CREATE TABLE brin_test_bloom (a INT, b INT); INSERT INTO brin_test_bloom SELECT x/100,x%100 FROM generate_series(1,10000) x(x); CREATE INDEX brin_test_bloom_a_idx ON brin_test_bloom USING brin (a) WITH (pages_per_range = 2); CREATE INDEX brin_test_bloom_b_idx ON brin_test_bloom USING brin (b) WITH (pages_per_range = 2); VACUUM ANALYZE brin_test_bloom; -- Ensure brin index is used when columns are perfectly correlated EXPLAIN (COSTS OFF) SELECT * FROM brin_test_bloom WHERE a = 1; -- Ensure brin index is not used when values are not correlated EXPLAIN (COSTS OFF) SELECT * FROM brin_test_bloom WHERE b = 1;