1CREATE TABLE brintest (byteacol bytea, 2 charcol "char", 3 namecol name, 4 int8col bigint, 5 int2col smallint, 6 int4col integer, 7 textcol text, 8 oidcol oid, 9 tidcol tid, 10 float4col real, 11 float8col double precision, 12 macaddrcol macaddr, 13 inetcol inet, 14 cidrcol cidr, 15 bpcharcol character, 16 datecol date, 17 timecol time without time zone, 18 timestampcol timestamp without time zone, 19 timestamptzcol timestamp with time zone, 20 intervalcol interval, 21 timetzcol time with time zone, 22 bitcol bit(10), 23 varbitcol bit varying(16), 24 numericcol numeric, 25 uuidcol uuid, 26 int4rangecol int4range, 27 lsncol pg_lsn, 28 boxcol box 29) WITH (fillfactor=10, autovacuum_enabled=off); 30 31INSERT INTO brintest SELECT 32 repeat(stringu1, 8)::bytea, 33 substr(stringu1, 1, 1)::"char", 34 stringu1::name, 142857 * tenthous, 35 thousand, 36 twothousand, 37 repeat(stringu1, 8), 38 unique1::oid, 39 format('(%s,%s)', tenthous, twenty)::tid, 40 (four + 1.0)/(hundred+1), 41 odd::float8 / (tenthous + 1), 42 format('%s:00:%s:00:%s:00', to_hex(odd), to_hex(even), to_hex(hundred))::macaddr, 43 inet '10.2.3.4/24' + tenthous, 44 cidr '10.2.3/24' + tenthous, 45 substr(stringu1, 1, 1)::bpchar, 46 date '1995-08-15' + tenthous, 47 time '01:20:30' + thousand * interval '18.5 second', 48 timestamp '1942-07-23 03:05:09' + tenthous * interval '36.38 hours', 49 timestamptz '1972-10-10 03:00' + thousand * interval '1 hour', 50 justify_days(justify_hours(tenthous * interval '12 minutes')), 51 timetz '01:30:20+02' + hundred * interval '15 seconds', 52 thousand::bit(10), 53 tenthous::bit(16)::varbit, 54 tenthous::numeric(36,30) * fivethous * even / (hundred + 1), 55 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, 56 int4range(thousand, twothousand), 57 format('%s/%s%s', odd, even, tenthous)::pg_lsn, 58 box(point(odd, even), point(thousand, twothousand)) 59FROM tenk1 ORDER BY unique2 LIMIT 100; 60 61-- throw in some NULL's and different values 62INSERT INTO brintest (inetcol, cidrcol, int4rangecol) SELECT 63 inet 'fe80::6e40:8ff:fea9:8c46' + tenthous, 64 cidr 'fe80::6e40:8ff:fea9:8c46' + tenthous, 65 'empty'::int4range 66FROM tenk1 ORDER BY thousand, tenthous LIMIT 25; 67 68CREATE INDEX brinidx ON brintest USING brin ( 69 byteacol, 70 charcol, 71 namecol, 72 int8col, 73 int2col, 74 int4col, 75 textcol, 76 oidcol, 77 tidcol, 78 float4col, 79 float8col, 80 macaddrcol, 81 inetcol inet_inclusion_ops, 82 inetcol inet_minmax_ops, 83 cidrcol inet_inclusion_ops, 84 cidrcol inet_minmax_ops, 85 bpcharcol, 86 datecol, 87 timecol, 88 timestampcol, 89 timestamptzcol, 90 intervalcol, 91 timetzcol, 92 bitcol, 93 varbitcol, 94 numericcol, 95 uuidcol, 96 int4rangecol, 97 lsncol, 98 boxcol 99) with (pages_per_range = 1); 100 101CREATE TABLE brinopers (colname name, typ text, 102 op text[], value text[], matches int[], 103 check (cardinality(op) = cardinality(value)), 104 check (cardinality(op) = cardinality(matches))); 105 106INSERT INTO brinopers VALUES 107 ('byteacol', 'bytea', 108 '{>, >=, =, <=, <}', 109 '{AAAAAA, AAAAAA, BNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAA, ZZZZZZ, ZZZZZZ}', 110 '{100, 100, 1, 100, 100}'), 111 ('charcol', '"char"', 112 '{>, >=, =, <=, <}', 113 '{A, A, M, Z, Z}', 114 '{97, 100, 6, 100, 98}'), 115 ('namecol', 'name', 116 '{>, >=, =, <=, <}', 117 '{AAAAAA, AAAAAA, MAAAAA, ZZAAAA, ZZAAAA}', 118 '{100, 100, 2, 100, 100}'), 119 ('int2col', 'int2', 120 '{>, >=, =, <=, <}', 121 '{0, 0, 800, 999, 999}', 122 '{100, 100, 1, 100, 100}'), 123 ('int2col', 'int4', 124 '{>, >=, =, <=, <}', 125 '{0, 0, 800, 999, 1999}', 126 '{100, 100, 1, 100, 100}'), 127 ('int2col', 'int8', 128 '{>, >=, =, <=, <}', 129 '{0, 0, 800, 999, 1428427143}', 130 '{100, 100, 1, 100, 100}'), 131 ('int4col', 'int2', 132 '{>, >=, =, <=, <}', 133 '{0, 0, 800, 1999, 1999}', 134 '{100, 100, 1, 100, 100}'), 135 ('int4col', 'int4', 136 '{>, >=, =, <=, <}', 137 '{0, 0, 800, 1999, 1999}', 138 '{100, 100, 1, 100, 100}'), 139 ('int4col', 'int8', 140 '{>, >=, =, <=, <}', 141 '{0, 0, 800, 1999, 1428427143}', 142 '{100, 100, 1, 100, 100}'), 143 ('int8col', 'int2', 144 '{>, >=}', 145 '{0, 0}', 146 '{100, 100}'), 147 ('int8col', 'int4', 148 '{>, >=}', 149 '{0, 0}', 150 '{100, 100}'), 151 ('int8col', 'int8', 152 '{>, >=, =, <=, <}', 153 '{0, 0, 1257141600, 1428427143, 1428427143}', 154 '{100, 100, 1, 100, 100}'), 155 ('textcol', 'text', 156 '{>, >=, =, <=, <}', 157 '{ABABAB, ABABAB, BNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAA, ZZAAAA, ZZAAAA}', 158 '{100, 100, 1, 100, 100}'), 159 ('oidcol', 'oid', 160 '{>, >=, =, <=, <}', 161 '{0, 0, 8800, 9999, 9999}', 162 '{100, 100, 1, 100, 100}'), 163 ('tidcol', 'tid', 164 '{>, >=, =, <=, <}', 165 '{"(0,0)", "(0,0)", "(8800,0)", "(9999,19)", "(9999,19)"}', 166 '{100, 100, 1, 100, 100}'), 167 ('float4col', 'float4', 168 '{>, >=, =, <=, <}', 169 '{0.0103093, 0.0103093, 1, 1, 1}', 170 '{100, 100, 4, 100, 96}'), 171 ('float4col', 'float8', 172 '{>, >=, =, <=, <}', 173 '{0.0103093, 0.0103093, 1, 1, 1}', 174 '{100, 100, 4, 100, 96}'), 175 ('float8col', 'float4', 176 '{>, >=, =, <=, <}', 177 '{0, 0, 0, 1.98, 1.98}', 178 '{99, 100, 1, 100, 100}'), 179 ('float8col', 'float8', 180 '{>, >=, =, <=, <}', 181 '{0, 0, 0, 1.98, 1.98}', 182 '{99, 100, 1, 100, 100}'), 183 ('macaddrcol', 'macaddr', 184 '{>, >=, =, <=, <}', 185 '{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}', 186 '{99, 100, 2, 100, 100}'), 187 ('inetcol', 'inet', 188 '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}', 189 '{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}', 190 '{100, 1, 100, 100, 125, 125, 2, 2, 100, 100}'), 191 ('inetcol', 'inet', 192 '{&&, >>=, <<=, =}', 193 '{fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46, fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46}', 194 '{25, 1, 25, 1}'), 195 ('inetcol', 'cidr', 196 '{&&, <, <=, >, >=, >>=, >>, <<=, <<}', 197 '{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}', 198 '{100, 100, 100, 125, 125, 2, 2, 100, 100}'), 199 ('inetcol', 'cidr', 200 '{&&, >>=, <<=, =}', 201 '{fe80::/32, fe80::6e40:8ff:fea9:8c46, fe80::/32, fe80::6e40:8ff:fea9:8c46}', 202 '{25, 1, 25, 1}'), 203 ('cidrcol', 'inet', 204 '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}', 205 '{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}', 206 '{100, 2, 100, 100, 125, 125, 2, 2, 100, 100}'), 207 ('cidrcol', 'inet', 208 '{&&, >>=, <<=, =}', 209 '{fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46, fe80::6e40:8ff:fea9:a673/32, fe80::6e40:8ff:fea9:8c46}', 210 '{25, 1, 25, 1}'), 211 ('cidrcol', 'cidr', 212 '{&&, =, <, <=, >, >=, >>=, >>, <<=, <<}', 213 '{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}', 214 '{100, 2, 100, 100, 125, 125, 2, 2, 100, 100}'), 215 ('cidrcol', 'cidr', 216 '{&&, >>=, <<=, =}', 217 '{fe80::/32, fe80::6e40:8ff:fea9:8c46, fe80::/32, fe80::6e40:8ff:fea9:8c46}', 218 '{25, 1, 25, 1}'), 219 ('bpcharcol', 'bpchar', 220 '{>, >=, =, <=, <}', 221 '{A, A, W, Z, Z}', 222 '{97, 100, 6, 100, 98}'), 223 ('datecol', 'date', 224 '{>, >=, =, <=, <}', 225 '{1995-08-15, 1995-08-15, 2009-12-01, 2022-12-30, 2022-12-30}', 226 '{100, 100, 1, 100, 100}'), 227 ('timecol', 'time', 228 '{>, >=, =, <=, <}', 229 '{01:20:30, 01:20:30, 02:28:57, 06:28:31.5, 06:28:31.5}', 230 '{100, 100, 1, 100, 100}'), 231 ('timestampcol', 'timestamp', 232 '{>, >=, =, <=, <}', 233 '{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}', 234 '{100, 100, 1, 100, 100}'), 235 ('timestampcol', 'timestamptz', 236 '{>, >=, =, <=, <}', 237 '{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}', 238 '{100, 100, 1, 100, 100}'), 239 ('timestamptzcol', 'timestamptz', 240 '{>, >=, =, <=, <}', 241 '{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}', 242 '{100, 100, 1, 100, 100}'), 243 ('intervalcol', 'interval', 244 '{>, >=, =, <=, <}', 245 '{00:00:00, 00:00:00, 1 mons 13 days 12:24, 2 mons 23 days 07:48:00, 1 year}', 246 '{100, 100, 1, 100, 100}'), 247 ('timetzcol', 'timetz', 248 '{>, >=, =, <=, <}', 249 '{01:30:20+02, 01:30:20+02, 01:35:50+02, 23:55:05+02, 23:55:05+02}', 250 '{99, 100, 2, 100, 100}'), 251 ('bitcol', 'bit(10)', 252 '{>, >=, =, <=, <}', 253 '{0000000010, 0000000010, 0011011110, 1111111000, 1111111000}', 254 '{100, 100, 1, 100, 100}'), 255 ('varbitcol', 'varbit(16)', 256 '{>, >=, =, <=, <}', 257 '{0000000000000100, 0000000000000100, 0001010001100110, 1111111111111000, 1111111111111000}', 258 '{100, 100, 1, 100, 100}'), 259 ('numericcol', 'numeric', 260 '{>, >=, =, <=, <}', 261 '{0.00, 0.01, 2268164.347826086956521739130434782609, 99470151.9, 99470151.9}', 262 '{100, 100, 1, 100, 100}'), 263 ('uuidcol', 'uuid', 264 '{>, >=, =, <=, <}', 265 '{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}', 266 '{100, 100, 1, 100, 100}'), 267 ('int4rangecol', 'int4range', 268 '{<<, &<, &&, &>, >>, @>, <@, =, <, <=, >, >=}', 269 '{"[10000,)","[10000,)","(,]","[3,4)","[36,44)","(1500,1501]","[3,4)","[222,1222)","[36,44)","[43,1043)","[367,4466)","[519,)"}', 270 '{53, 53, 53, 53, 50, 22, 72, 1, 74, 75, 34, 21}'), 271 ('int4rangecol', 'int4range', 272 '{@>, <@, =, <=, >, >=}', 273 '{empty, empty, empty, empty, empty, empty}', 274 '{125, 72, 72, 72, 53, 125}'), 275 ('int4rangecol', 'int4', 276 '{@>}', 277 '{1500}', 278 '{22}'), 279 ('lsncol', 'pg_lsn', 280 '{>, >=, =, <=, <, IS, IS NOT}', 281 '{0/1200, 0/1200, 44/455222, 198/1999799, 198/1999799, NULL, NULL}', 282 '{100, 100, 1, 100, 100, 25, 100}'), 283 ('boxcol', 'point', 284 '{@>}', 285 '{"(500,43)"}', 286 '{11}'), 287 ('boxcol', 'box', 288 '{<<, &<, &&, &>, >>, <<|, &<|, |&>, |>>, @>, <@, ~=}', 289 '{"((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))"}', 290 '{100, 100, 100, 99, 96, 100, 100, 99, 96, 1, 99, 1}'); 291 292DO $x$ 293DECLARE 294 r record; 295 r2 record; 296 cond text; 297 idx_ctids tid[]; 298 ss_ctids tid[]; 299 count int; 300 plan_ok bool; 301 plan_line text; 302BEGIN 303 FOR r IN SELECT colname, oper, typ, value[ordinality], matches[ordinality] FROM brinopers, unnest(op) WITH ORDINALITY AS oper LOOP 304 305 -- prepare the condition 306 IF r.value IS NULL THEN 307 cond := format('%I %s %L', r.colname, r.oper, r.value); 308 ELSE 309 cond := format('%I %s %L::%s', r.colname, r.oper, r.value, r.typ); 310 END IF; 311 312 -- run the query using the brin index 313 SET enable_seqscan = 0; 314 SET enable_bitmapscan = 1; 315 316 plan_ok := false; 317 FOR plan_line IN EXECUTE format($y$EXPLAIN SELECT array_agg(ctid) FROM brintest WHERE %s $y$, cond) LOOP 318 IF plan_line LIKE '%Bitmap Heap Scan on brintest%' THEN 319 plan_ok := true; 320 END IF; 321 END LOOP; 322 IF NOT plan_ok THEN 323 RAISE WARNING 'did not get bitmap indexscan plan for %', r; 324 END IF; 325 326 EXECUTE format($y$SELECT array_agg(ctid) FROM brintest WHERE %s $y$, cond) 327 INTO idx_ctids; 328 329 -- run the query using a seqscan 330 SET enable_seqscan = 1; 331 SET enable_bitmapscan = 0; 332 333 plan_ok := false; 334 FOR plan_line IN EXECUTE format($y$EXPLAIN SELECT array_agg(ctid) FROM brintest WHERE %s $y$, cond) LOOP 335 IF plan_line LIKE '%Seq Scan on brintest%' THEN 336 plan_ok := true; 337 END IF; 338 END LOOP; 339 IF NOT plan_ok THEN 340 RAISE WARNING 'did not get seqscan plan for %', r; 341 END IF; 342 343 EXECUTE format($y$SELECT array_agg(ctid) FROM brintest WHERE %s $y$, cond) 344 INTO ss_ctids; 345 346 -- make sure both return the same results 347 count := array_length(idx_ctids, 1); 348 349 IF NOT (count = array_length(ss_ctids, 1) AND 350 idx_ctids @> ss_ctids AND 351 idx_ctids <@ ss_ctids) THEN 352 -- report the results of each scan to make the differences obvious 353 RAISE WARNING 'something not right in %: count %', r, count; 354 SET enable_seqscan = 1; 355 SET enable_bitmapscan = 0; 356 FOR r2 IN EXECUTE 'SELECT ' || r.colname || ' FROM brintest WHERE ' || cond LOOP 357 RAISE NOTICE 'seqscan: %', r2; 358 END LOOP; 359 360 SET enable_seqscan = 0; 361 SET enable_bitmapscan = 1; 362 FOR r2 IN EXECUTE 'SELECT ' || r.colname || ' FROM brintest WHERE ' || cond LOOP 363 RAISE NOTICE 'bitmapscan: %', r2; 364 END LOOP; 365 END IF; 366 367 -- make sure we found expected number of matches 368 IF count != r.matches THEN RAISE WARNING 'unexpected number of results % for %', count, r; END IF; 369 END LOOP; 370END; 371$x$; 372 373RESET enable_seqscan; 374RESET enable_bitmapscan; 375 376INSERT INTO brintest SELECT 377 repeat(stringu1, 42)::bytea, 378 substr(stringu1, 1, 1)::"char", 379 stringu1::name, 142857 * tenthous, 380 thousand, 381 twothousand, 382 repeat(stringu1, 42), 383 unique1::oid, 384 format('(%s,%s)', tenthous, twenty)::tid, 385 (four + 1.0)/(hundred+1), 386 odd::float8 / (tenthous + 1), 387 format('%s:00:%s:00:%s:00', to_hex(odd), to_hex(even), to_hex(hundred))::macaddr, 388 inet '10.2.3.4' + tenthous, 389 cidr '10.2.3/24' + tenthous, 390 substr(stringu1, 1, 1)::bpchar, 391 date '1995-08-15' + tenthous, 392 time '01:20:30' + thousand * interval '18.5 second', 393 timestamp '1942-07-23 03:05:09' + tenthous * interval '36.38 hours', 394 timestamptz '1972-10-10 03:00' + thousand * interval '1 hour', 395 justify_days(justify_hours(tenthous * interval '12 minutes')), 396 timetz '01:30:20' + hundred * interval '15 seconds', 397 thousand::bit(10), 398 tenthous::bit(16)::varbit, 399 tenthous::numeric(36,30) * fivethous * even / (hundred + 1), 400 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, 401 int4range(thousand, twothousand), 402 format('%s/%s%s', odd, even, tenthous)::pg_lsn, 403 box(point(odd, even), point(thousand, twothousand)) 404FROM tenk1 ORDER BY unique2 LIMIT 5 OFFSET 5; 405 406SELECT brin_desummarize_range('brinidx', 0); 407VACUUM brintest; -- force a summarization cycle in brinidx 408 409UPDATE brintest SET int8col = int8col * int4col; 410UPDATE brintest SET textcol = '' WHERE textcol IS NOT NULL; 411 412-- Tests for brin_summarize_new_values 413SELECT brin_summarize_new_values('brintest'); -- error, not an index 414SELECT brin_summarize_new_values('tenk1_unique1'); -- error, not a BRIN index 415SELECT brin_summarize_new_values('brinidx'); -- ok, no change expected 416 417-- Tests for brin_desummarize_range 418SELECT brin_desummarize_range('brinidx', -1); -- error, invalid range 419SELECT brin_desummarize_range('brinidx', 0); 420SELECT brin_desummarize_range('brinidx', 0); 421SELECT brin_desummarize_range('brinidx', 100000000); 422 423-- Test brin_summarize_range 424CREATE TABLE brin_summarize ( 425 value int 426) WITH (fillfactor=10, autovacuum_enabled=false); 427CREATE INDEX brin_summarize_idx ON brin_summarize USING brin (value) WITH (pages_per_range=2); 428-- Fill a few pages 429DO $$ 430DECLARE curtid tid; 431BEGIN 432 LOOP 433 INSERT INTO brin_summarize VALUES (1) RETURNING ctid INTO curtid; 434 EXIT WHEN curtid > tid '(2, 0)'; 435 END LOOP; 436END; 437$$; 438 439-- summarize one range 440SELECT brin_summarize_range('brin_summarize_idx', 0); 441-- nothing: already summarized 442SELECT brin_summarize_range('brin_summarize_idx', 1); 443-- summarize one range 444SELECT brin_summarize_range('brin_summarize_idx', 2); 445-- nothing: page doesn't exist in table 446SELECT brin_summarize_range('brin_summarize_idx', 4294967295); 447-- invalid block number values 448SELECT brin_summarize_range('brin_summarize_idx', -1); 449SELECT brin_summarize_range('brin_summarize_idx', 4294967296); 450 451 452-- test brin cost estimates behave sanely based on correlation of values 453CREATE TABLE brin_test (a INT, b INT); 454INSERT INTO brin_test SELECT x/100,x%100 FROM generate_series(1,10000) x(x); 455CREATE INDEX brin_test_a_idx ON brin_test USING brin (a) WITH (pages_per_range = 2); 456CREATE INDEX brin_test_b_idx ON brin_test USING brin (b) WITH (pages_per_range = 2); 457VACUUM ANALYZE brin_test; 458 459-- Ensure brin index is used when columns are perfectly correlated 460EXPLAIN (COSTS OFF) SELECT * FROM brin_test WHERE a = 1; 461-- Ensure brin index is not used when values are not correlated 462EXPLAIN (COSTS OFF) SELECT * FROM brin_test WHERE b = 1; 463 464-- make sure data are properly de-toasted in BRIN index 465CREATE TABLE brintest_3 (a text, b text, c text, d text); 466 467-- long random strings (~2000 chars each, so ~6kB for min/max on two 468-- columns) to trigger toasting 469WITH rand_value AS (SELECT string_agg(md5(i::text),'') AS val FROM generate_series(1,60) s(i)) 470INSERT INTO brintest_3 471SELECT val, val, val, val FROM rand_value; 472 473CREATE INDEX brin_test_toast_idx ON brintest_3 USING brin (b, c); 474DELETE FROM brintest_3; 475 476-- We need to wait a bit for all transactions to complete, so that the 477-- vacuum actually removes the TOAST rows. Creating an index concurrently 478-- is a one way to achieve that, because it does exactly such wait. 479CREATE INDEX CONCURRENTLY brin_test_temp_idx ON brintest_3(a); 480DROP INDEX brin_test_temp_idx; 481 482-- vacuum the table, to discard TOAST data 483VACUUM brintest_3; 484 485-- retry insert with a different random-looking (but deterministic) value 486-- the value is different, and so should replace either min or max in the 487-- brin summary 488WITH rand_value AS (SELECT string_agg(md5((-i)::text),'') AS val FROM generate_series(1,60) s(i)) 489INSERT INTO brintest_3 490SELECT val, val, val, val FROM rand_value; 491 492-- now try some queries, accessing the brin index 493SET enable_seqscan = off; 494 495EXPLAIN (COSTS OFF) 496SELECT * FROM brintest_3 WHERE b < '0'; 497 498SELECT * FROM brintest_3 WHERE b < '0'; 499 500DROP TABLE brintest_3; 501RESET enable_seqscan; 502