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 value merging in add_value 487CREATE TABLE brintest_2 (n numrange); 488CREATE INDEX brinidx_2 ON brintest_2 USING brin (n); 489INSERT INTO brintest_2 VALUES ('empty'); 490INSERT INTO brintest_2 VALUES (numrange(0, 2^1000::numeric)); 491INSERT INTO brintest_2 VALUES ('(-1, 0)'); 492SELECT brin_desummarize_range('brinidx', 0); 493 brin_desummarize_range 494------------------------ 495 496(1 row) 497 498SELECT brin_summarize_range('brinidx', 0); 499 brin_summarize_range 500---------------------- 501 1 502(1 row) 503 504DROP TABLE brintest_2; 505-- test brin cost estimates behave sanely based on correlation of values 506CREATE TABLE brin_test (a INT, b INT); 507INSERT INTO brin_test SELECT x/100,x%100 FROM generate_series(1,10000) x(x); 508CREATE INDEX brin_test_a_idx ON brin_test USING brin (a) WITH (pages_per_range = 2); 509CREATE INDEX brin_test_b_idx ON brin_test USING brin (b) WITH (pages_per_range = 2); 510VACUUM ANALYZE brin_test; 511-- Ensure brin index is used when columns are perfectly correlated 512EXPLAIN (COSTS OFF) SELECT * FROM brin_test WHERE a = 1; 513 QUERY PLAN 514-------------------------------------------- 515 Bitmap Heap Scan on brin_test 516 Recheck Cond: (a = 1) 517 -> Bitmap Index Scan on brin_test_a_idx 518 Index Cond: (a = 1) 519(4 rows) 520 521-- Ensure brin index is not used when values are not correlated 522EXPLAIN (COSTS OFF) SELECT * FROM brin_test WHERE b = 1; 523 QUERY PLAN 524----------------------- 525 Seq Scan on brin_test 526 Filter: (b = 1) 527(2 rows) 528 529-- make sure data are properly de-toasted in BRIN index 530CREATE TABLE brintest_3 (a text, b text, c text, d text); 531-- long random strings (~2000 chars each, so ~6kB for min/max on two 532-- columns) to trigger toasting 533WITH rand_value AS (SELECT string_agg(md5(i::text),'') AS val FROM generate_series(1,60) s(i)) 534INSERT INTO brintest_3 535SELECT val, val, val, val FROM rand_value; 536CREATE INDEX brin_test_toast_idx ON brintest_3 USING brin (b, c); 537DELETE FROM brintest_3; 538-- We need to wait a bit for all transactions to complete, so that the 539-- vacuum actually removes the TOAST rows. Creating an index concurrently 540-- is a one way to achieve that, because it does exactly such wait. 541CREATE INDEX CONCURRENTLY brin_test_temp_idx ON brintest_3(a); 542DROP INDEX brin_test_temp_idx; 543-- vacuum the table, to discard TOAST data 544VACUUM brintest_3; 545-- retry insert with a different random-looking (but deterministic) value 546-- the value is different, and so should replace either min or max in the 547-- brin summary 548WITH rand_value AS (SELECT string_agg(md5((-i)::text),'') AS val FROM generate_series(1,60) s(i)) 549INSERT INTO brintest_3 550SELECT val, val, val, val FROM rand_value; 551-- now try some queries, accessing the brin index 552SET enable_seqscan = off; 553EXPLAIN (COSTS OFF) 554SELECT * FROM brintest_3 WHERE b < '0'; 555 QUERY PLAN 556------------------------------------------------ 557 Bitmap Heap Scan on brintest_3 558 Recheck Cond: (b < '0'::text) 559 -> Bitmap Index Scan on brin_test_toast_idx 560 Index Cond: (b < '0'::text) 561(4 rows) 562 563SELECT * FROM brintest_3 WHERE b < '0'; 564 a | b | c | d 565---+---+---+--- 566(0 rows) 567 568DROP TABLE brintest_3; 569RESET enable_seqscan; 570