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