Quelle  brin_bloom.sql

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;