| blob | d3d693e4649427b270828bfa47ea707a0615f1a0 |
1 CREATE TABLE brintest_multi (
2 int8col bigint,
3 int2col smallint,
4 int4col integer,
5 oidcol oid,
6 tidcol tid,
7 float4col real,
8 float8col double precision,
9 macaddrcol macaddr,
10 macaddr8col macaddr8,
11 inetcol inet,
12 cidrcol cidr,
13 datecol date,
14 timecol time without time zone,
15 timestampcol timestamp without time zone,
16 timestamptzcol timestamp with time zone,
17 intervalcol interval,
18 timetzcol time with time zone,
19 numericcol numeric,
20 uuidcol uuid,
21 lsncol pg_lsn
22 ) WITH (fillfactor=10);
23 INSERT INTO brintest_multi SELECT
24 142857 * tenthous,
25 thousand,
26 twothousand,
27 unique1::oid,
28 format('(%s,%s)', tenthous, twenty)::tid,
29 (four + 1.0)/(hundred+1),
30 odd::float8 / (tenthous + 1),
31 format('%s:00:%s:00:%s:00', to_hex(odd), to_hex(even), to_hex(hundred))::macaddr,
32 substr(md5(unique1::text), 1, 16)::macaddr8,
33 inet '10.2.3.4/24' + tenthous,
34 cidr '10.2.3/24' + tenthous,
35 date '1995-08-15' + tenthous,
36 time '01:20:30' + thousand * interval '18.5 second',
37 timestamp '1942-07-23 03:05:09' + tenthous * interval '36.38 hours',
38 timestamptz '1972-10-10 03:00' + thousand * interval '1 hour',
39 justify_days(justify_hours(tenthous * interval '12 minutes')),
40 timetz '01:30:20+02' + hundred * interval '15 seconds',
41 tenthous::numeric(36,30) * fivethous * even / (hundred + 1),
42 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,
43 format('%s/%s%s', odd, even, tenthous)::pg_lsn
44 FROM tenk1 ORDER BY unique2 LIMIT 100;
45 -- throw in some NULL's and different values
46 INSERT INTO brintest_multi (inetcol, cidrcol) SELECT
47 inet 'fe80::6e40:8ff:fea9:8c46' + tenthous,
48 cidr 'fe80::6e40:8ff:fea9:8c46' + tenthous
49 FROM tenk1 ORDER BY thousand, tenthous LIMIT 25;
50 -- test minmax-multi specific index options
51 -- number of values must be >= 16
52 CREATE INDEX brinidx_multi ON brintest_multi USING brin (
53 int8col int8_minmax_multi_ops(values_per_range = 7)
54 );
55 ERROR: value 7 out of bounds for option "values_per_range"
56 DETAIL: Valid values are between "8" and "256".
57 -- number of values must be <= 256
58 CREATE INDEX brinidx_multi ON brintest_multi USING brin (
59 int8col int8_minmax_multi_ops(values_per_range = 257)
60 );
61 ERROR: value 257 out of bounds for option "values_per_range"
62 DETAIL: Valid values are between "8" and "256".
63 -- first create an index with a single page range, to force compaction
64 -- due to exceeding the number of values per summary
65 CREATE INDEX brinidx_multi ON brintest_multi USING brin (
66 int8col int8_minmax_multi_ops,
67 int2col int2_minmax_multi_ops,
68 int4col int4_minmax_multi_ops,
69 oidcol oid_minmax_multi_ops,
70 tidcol tid_minmax_multi_ops,
71 float4col float4_minmax_multi_ops,
72 float8col float8_minmax_multi_ops,
73 macaddrcol macaddr_minmax_multi_ops,
74 macaddr8col macaddr8_minmax_multi_ops,
75 inetcol inet_minmax_multi_ops,
76 cidrcol inet_minmax_multi_ops,
77 datecol date_minmax_multi_ops,
78 timecol time_minmax_multi_ops,
79 timestampcol timestamp_minmax_multi_ops,
80 timestamptzcol timestamptz_minmax_multi_ops,
81 intervalcol interval_minmax_multi_ops,
82 timetzcol timetz_minmax_multi_ops,
83 numericcol numeric_minmax_multi_ops,
84 uuidcol uuid_minmax_multi_ops,
85 lsncol pg_lsn_minmax_multi_ops
86 );
87 DROP INDEX brinidx_multi;
88 CREATE INDEX brinidx_multi ON brintest_multi USING brin (
89 int8col int8_minmax_multi_ops,
90 int2col int2_minmax_multi_ops,
91 int4col int4_minmax_multi_ops,
92 oidcol oid_minmax_multi_ops,
93 tidcol tid_minmax_multi_ops,
94 float4col float4_minmax_multi_ops,
95 float8col float8_minmax_multi_ops,
96 macaddrcol macaddr_minmax_multi_ops,
97 macaddr8col macaddr8_minmax_multi_ops,
98 inetcol inet_minmax_multi_ops,
99 cidrcol inet_minmax_multi_ops,
100 datecol date_minmax_multi_ops,
101 timecol time_minmax_multi_ops,
102 timestampcol timestamp_minmax_multi_ops,
103 timestamptzcol timestamptz_minmax_multi_ops,
104 intervalcol interval_minmax_multi_ops,
105 timetzcol timetz_minmax_multi_ops,
106 numericcol numeric_minmax_multi_ops,
107 uuidcol uuid_minmax_multi_ops,
108 lsncol pg_lsn_minmax_multi_ops
109 ) with (pages_per_range = 1);
110 CREATE TABLE brinopers_multi (colname name, typ text,
111 op text[], value text[], matches int[],
112 check (cardinality(op) = cardinality(value)),
113 check (cardinality(op) = cardinality(matches)));
114 INSERT INTO brinopers_multi VALUES
115 ('int2col', 'int2',
116 '{>, >=, =, <=, <}',
117 '{0, 0, 800, 999, 999}',
118 '{100, 100, 1, 100, 100}'),
119 ('int2col', 'int4',
120 '{>, >=, =, <=, <}',
121 '{0, 0, 800, 999, 1999}',
122 '{100, 100, 1, 100, 100}'),
123 ('int2col', 'int8',
124 '{>, >=, =, <=, <}',
125 '{0, 0, 800, 999, 1428427143}',
126 '{100, 100, 1, 100, 100}'),
127 ('int4col', 'int2',
128 '{>, >=, =, <=, <}',
129 '{0, 0, 800, 1999, 1999}',
130 '{100, 100, 1, 100, 100}'),
131 ('int4col', 'int4',
132 '{>, >=, =, <=, <}',
133 '{0, 0, 800, 1999, 1999}',
134 '{100, 100, 1, 100, 100}'),
135 ('int4col', 'int8',
136 '{>, >=, =, <=, <}',
137 '{0, 0, 800, 1999, 1428427143}',
138 '{100, 100, 1, 100, 100}'),
139 ('int8col', 'int2',
140 '{>, >=}',
141 '{0, 0}',
142 '{100, 100}'),
143 ('int8col', 'int4',
144 '{>, >=}',
145 '{0, 0}',
146 '{100, 100}'),
147 ('int8col', 'int8',
148 '{>, >=, =, <=, <}',
149 '{0, 0, 1257141600, 1428427143, 1428427143}',
150 '{100, 100, 1, 100, 100}'),
151 ('oidcol', 'oid',
152 '{>, >=, =, <=, <}',
153 '{0, 0, 8800, 9999, 9999}',
154 '{100, 100, 1, 100, 100}'),
155 ('tidcol', 'tid',
156 '{>, >=, =, <=, <}',
157 '{"(0,0)", "(0,0)", "(8800,0)", "(9999,19)", "(9999,19)"}',
158 '{100, 100, 1, 100, 100}'),
159 ('float4col', 'float4',
160 '{>, >=, =, <=, <}',
161 '{0.0103093, 0.0103093, 1, 1, 1}',
162 '{100, 100, 4, 100, 96}'),
163 ('float4col', 'float8',
164 '{>, >=, =, <=, <}',
165 '{0.0103093, 0.0103093, 1, 1, 1}',
166 '{100, 100, 4, 100, 96}'),
167 ('float8col', 'float4',
168 '{>, >=, =, <=, <}',
169 '{0, 0, 0, 1.98, 1.98}',
170 '{99, 100, 1, 100, 100}'),
171 ('float8col', 'float8',
172 '{>, >=, =, <=, <}',
173 '{0, 0, 0, 1.98, 1.98}',
174 '{99, 100, 1, 100, 100}'),
175 ('macaddrcol', 'macaddr',
176 '{>, >=, =, <=, <}',
177 '{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}',
178 '{99, 100, 2, 100, 100}'),
179 ('macaddr8col', 'macaddr8',
180 '{>, >=, =, <=, <}',
181 '{b1:d1:0e:7b:af:a4:42:12, d9:35:91:bd:f7:86:0e:1e, 72:8f:20:6c:2a:01:bf:57, 23:e8:46:63:86:07:ad:cb, 13:16:8e:6a:2e:6c:84:b4}',
182 '{33, 15, 1, 13, 6}'),
183 ('inetcol', 'inet',
184 '{=, <, <=, >, >=}',
185 '{10.2.14.231/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
186 '{1, 100, 100, 125, 125}'),
187 ('inetcol', 'cidr',
188 '{<, <=, >, >=}',
189 '{255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
190 '{100, 100, 125, 125}'),
191 ('cidrcol', 'inet',
192 '{=, <, <=, >, >=}',
193 '{10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
194 '{2, 100, 100, 125, 125}'),
195 ('cidrcol', 'cidr',
196 '{=, <, <=, >, >=}',
197 '{10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
198 '{2, 100, 100, 125, 125}'),
199 ('datecol', 'date',
200 '{>, >=, =, <=, <}',
201 '{1995-08-15, 1995-08-15, 2009-12-01, 2022-12-30, 2022-12-30}',
202 '{100, 100, 1, 100, 100}'),
203 ('timecol', 'time',
204 '{>, >=, =, <=, <}',
205 '{01:20:30, 01:20:30, 02:28:57, 06:28:31.5, 06:28:31.5}',
206 '{100, 100, 1, 100, 100}'),
207 ('timestampcol', 'timestamp',
208 '{>, >=, =, <=, <}',
209 '{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}',
210 '{100, 100, 1, 100, 100}'),
211 ('timestampcol', 'timestamptz',
212 '{>, >=, =, <=, <}',
213 '{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}',
214 '{100, 100, 1, 100, 100}'),
215 ('timestamptzcol', 'timestamptz',
216 '{>, >=, =, <=, <}',
217 '{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}',
218 '{100, 100, 1, 100, 100}'),
219 ('intervalcol', 'interval',
220 '{>, >=, =, <=, <}',
221 '{00:00:00, 00:00:00, 1 mons 13 days 12:24, 2 mons 23 days 07:48:00, 1 year}',
222 '{100, 100, 1, 100, 100}'),
223 ('timetzcol', 'timetz',
224 '{>, >=, =, <=, <}',
225 '{01:30:20+02, 01:30:20+02, 01:35:50+02, 23:55:05+02, 23:55:05+02}',
226 '{99, 100, 2, 100, 100}'),
227 ('numericcol', 'numeric',
228 '{>, >=, =, <=, <}',
229 '{0.00, 0.01, 2268164.347826086956521739130434782609, 99470151.9, 99470151.9}',
230 '{100, 100, 1, 100, 100}'),
231 ('uuidcol', 'uuid',
232 '{>, >=, =, <=, <}',
233 '{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}',
234 '{100, 100, 1, 100, 100}'),
235 ('lsncol', 'pg_lsn',
236 '{>, >=, =, <=, <, IS, IS NOT}',
237 '{0/1200, 0/1200, 44/455222, 198/1999799, 198/1999799, NULL, NULL}',
238 '{100, 100, 1, 100, 100, 25, 100}');
239 DO $x$
240 DECLARE
241 r record;
242 r2 record;
243 cond text;
244 idx_ctids tid[];
245 ss_ctids tid[];
246 count int;
247 plan_ok bool;
248 plan_line text;
249 BEGIN
250 FOR r IN SELECT colname, oper, typ, value[ordinality], matches[ordinality] FROM brinopers_multi, unnest(op) WITH ORDINALITY AS oper LOOP
252 -- prepare the condition
253 IF r.value IS NULL THEN
254 cond := format('%I %s %L', r.colname, r.oper, r.value);
255 ELSE
256 cond := format('%I %s %L::%s', r.colname, r.oper, r.value, r.typ);
257 END IF;
259 -- run the query using the brin index
260 SET enable_seqscan = 0;
261 SET enable_bitmapscan = 1;
263 plan_ok := false;
264 FOR plan_line IN EXECUTE format($y$EXPLAIN SELECT array_agg(ctid) FROM brintest_multi WHERE %s $y$, cond) LOOP
265 IF plan_line LIKE '%Bitmap Heap Scan on brintest_multi%' THEN
266 plan_ok := true;
267 END IF;
268 END LOOP;
269 IF NOT plan_ok THEN
270 RAISE WARNING 'did not get bitmap indexscan plan for %', r;
271 END IF;
273 EXECUTE format($y$SELECT array_agg(ctid) FROM brintest_multi WHERE %s $y$, cond)
274 INTO idx_ctids;
276 -- run the query using a seqscan
277 SET enable_seqscan = 1;
278 SET enable_bitmapscan = 0;
280 plan_ok := false;
281 FOR plan_line IN EXECUTE format($y$EXPLAIN SELECT array_agg(ctid) FROM brintest_multi WHERE %s $y$, cond) LOOP
282 IF plan_line LIKE '%Seq Scan on brintest_multi%' THEN
283 plan_ok := true;
284 END IF;
285 END LOOP;
286 IF NOT plan_ok THEN
287 RAISE WARNING 'did not get seqscan plan for %', r;
288 END IF;
290 EXECUTE format($y$SELECT array_agg(ctid) FROM brintest_multi WHERE %s $y$, cond)
291 INTO ss_ctids;
293 -- make sure both return the same results
294 count := array_length(idx_ctids, 1);
296 IF NOT (count = array_length(ss_ctids, 1) AND
297 idx_ctids @> ss_ctids AND
298 idx_ctids <@ ss_ctids) THEN
299 -- report the results of each scan to make the differences obvious
300 RAISE WARNING 'something not right in %: count %', r, count;
301 SET enable_seqscan = 1;
302 SET enable_bitmapscan = 0;
303 FOR r2 IN EXECUTE 'SELECT ' || r.colname || ' FROM brintest_multi WHERE ' || cond LOOP
304 RAISE NOTICE 'seqscan: %', r2;
305 END LOOP;
307 SET enable_seqscan = 0;
308 SET enable_bitmapscan = 1;
309 FOR r2 IN EXECUTE 'SELECT ' || r.colname || ' FROM brintest_multi WHERE ' || cond LOOP
310 RAISE NOTICE 'bitmapscan: %', r2;
311 END LOOP;
312 END IF;
314 -- make sure we found expected number of matches
315 IF count != r.matches THEN RAISE WARNING 'unexpected number of results % for %', count, r; END IF;
316 END LOOP;
317 END;
318 $x$;
319 RESET enable_seqscan;
320 RESET enable_bitmapscan;
321 INSERT INTO brintest_multi SELECT
322 142857 * tenthous,
323 thousand,
324 twothousand,
325 unique1::oid,
326 format('(%s,%s)', tenthous, twenty)::tid,
327 (four + 1.0)/(hundred+1),
328 odd::float8 / (tenthous + 1),
329 format('%s:00:%s:00:%s:00', to_hex(odd), to_hex(even), to_hex(hundred))::macaddr,
330 substr(md5(unique1::text), 1, 16)::macaddr8,
331 inet '10.2.3.4' + tenthous,
332 cidr '10.2.3/24' + tenthous,
333 date '1995-08-15' + tenthous,
334 time '01:20:30' + thousand * interval '18.5 second',
335 timestamp '1942-07-23 03:05:09' + tenthous * interval '36.38 hours',
336 timestamptz '1972-10-10 03:00' + thousand * interval '1 hour',
337 justify_days(justify_hours(tenthous * interval '12 minutes')),
338 timetz '01:30:20' + hundred * interval '15 seconds',
339 tenthous::numeric(36,30) * fivethous * even / (hundred + 1),
340 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,
341 format('%s/%s%s', odd, even, tenthous)::pg_lsn
342 FROM tenk1 ORDER BY unique2 LIMIT 5 OFFSET 5;
343 SELECT brin_desummarize_range('brinidx_multi', 0);
344 brin_desummarize_range
345 ------------------------
347 (1 row)
349 VACUUM brintest_multi; -- force a summarization cycle in brinidx
350 UPDATE brintest_multi SET int8col = int8col * int4col;
351 -- Tests for brin_summarize_new_values
352 SELECT brin_summarize_new_values('brintest_multi'); -- error, not an index
353 ERROR: "brintest_multi" is not an index
354 SELECT brin_summarize_new_values('tenk1_unique1'); -- error, not a BRIN index
355 ERROR: "tenk1_unique1" is not a BRIN index
356 SELECT brin_summarize_new_values('brinidx_multi'); -- ok, no change expected
357 brin_summarize_new_values
358 ---------------------------
359 0
360 (1 row)
362 -- Tests for brin_desummarize_range
363 SELECT brin_desummarize_range('brinidx_multi', -1); -- error, invalid range
364 ERROR: block number out of range: -1
365 SELECT brin_desummarize_range('brinidx_multi', 0);
366 brin_desummarize_range
367 ------------------------
369 (1 row)
371 SELECT brin_desummarize_range('brinidx_multi', 0);
372 brin_desummarize_range
373 ------------------------
375 (1 row)
377 SELECT brin_desummarize_range('brinidx_multi', 100000000);
378 brin_desummarize_range
379 ------------------------
381 (1 row)
383 -- test building an index with many values, to force compaction of the buffer
384 CREATE TABLE brin_large_range (a int4);
385 INSERT INTO brin_large_range SELECT i FROM generate_series(1,10000) s(i);
386 CREATE INDEX brin_large_range_idx ON brin_large_range USING brin (a int4_minmax_multi_ops);
387 DROP TABLE brin_large_range;
388 -- Test brin_summarize_range
389 CREATE TABLE brin_summarize_multi (
390 value int
391 ) WITH (fillfactor=10, autovacuum_enabled=false);
392 CREATE INDEX brin_summarize_multi_idx ON brin_summarize_multi USING brin (value) WITH (pages_per_range=2);
393 -- Fill a few pages
394 DO $$
395 DECLARE curtid tid;
396 BEGIN
397 LOOP
398 INSERT INTO brin_summarize_multi VALUES (1) RETURNING ctid INTO curtid;
399 EXIT WHEN curtid > tid '(2, 0)';
400 END LOOP;
401 END;
402 $$;
403 -- summarize one range
404 SELECT brin_summarize_range('brin_summarize_multi_idx', 0);
405 brin_summarize_range
406 ----------------------
407 0
408 (1 row)
410 -- nothing: already summarized
411 SELECT brin_summarize_range('brin_summarize_multi_idx', 1);
412 brin_summarize_range
413 ----------------------
414 0
415 (1 row)
417 -- summarize one range
418 SELECT brin_summarize_range('brin_summarize_multi_idx', 2);
419 brin_summarize_range
420 ----------------------
421 1
422 (1 row)
424 -- nothing: page doesn't exist in table
425 SELECT brin_summarize_range('brin_summarize_multi_idx', 4294967295);
426 brin_summarize_range
427 ----------------------
428 0
429 (1 row)
431 -- invalid block number values
432 SELECT brin_summarize_range('brin_summarize_multi_idx', -1);
433 ERROR: block number out of range: -1
434 SELECT brin_summarize_range('brin_summarize_multi_idx', 4294967296);
435 ERROR: block number out of range: 4294967296
436 -- test brin cost estimates behave sanely based on correlation of values
437 CREATE TABLE brin_test_multi (a INT, b INT);
438 INSERT INTO brin_test_multi SELECT x/100,x%100 FROM generate_series(1,10000) x(x);
439 CREATE INDEX brin_test_multi_a_idx ON brin_test_multi USING brin (a) WITH (pages_per_range = 2);
440 CREATE INDEX brin_test_multi_b_idx ON brin_test_multi USING brin (b) WITH (pages_per_range = 2);
441 VACUUM ANALYZE brin_test_multi;
442 -- Ensure brin index is used when columns are perfectly correlated
443 EXPLAIN (COSTS OFF) SELECT * FROM brin_test_multi WHERE a = 1;
444 QUERY PLAN
445 --------------------------------------------------
446 Bitmap Heap Scan on brin_test_multi
447 Recheck Cond: (a = 1)
448 -> Bitmap Index Scan on brin_test_multi_a_idx
449 Index Cond: (a = 1)
450 (4 rows)
452 -- Ensure brin index is not used when values are not correlated
453 EXPLAIN (COSTS OFF) SELECT * FROM brin_test_multi WHERE b = 1;
454 QUERY PLAN
455 -----------------------------
456 Seq Scan on brin_test_multi
457 Filter: (b = 1)
458 (2 rows)