作者
digoal
日期
2021-03-26
标签
PostgreSQL , brin , 索引 , 块级索引 , iot , 物联网 , 时序 , feed , log
背景
BRIN索引特别适合边界清晰的堆存储数据, 例如BLOCK 1到8 存储的id范围是100-10000, 9到16 存储的id范围是100001到200000, 检索id=1000时, 只需要扫描1到8号数据块.
然而如果堆存储里面的边界不清晰(或者说存储顺序和value相关性不高), 那么被索引字段的值分布很零散,或者范围跨度很大时, 检索一个ID值时可能要扫描很多很多数据块.
为了解决这个问题, PG 14 支持multi range brin, 1到8号块存储的ID范围可能是1-199, 10000-10019, 20000-20000, 占用5个value(1,199,10000,10019,20000), 一个blocks区间存储多少个value取决于values_per_range参数(8到256).
当不断插入数据时, 这些范围还可以被合并
PostgreSQL 开发者确实很精致, 赞!!!
28 * The number of intervals tracked per page range is somewhat flexible.
29 * What is restricted is the number of values per page range, and the limit
30 * is currently 32 (see values_per_range reloption). Collapsed intervals
31 * (with equal minimum and maximum value) are stored as a single value,
32 * while regular intervals require two values.
33 *
34 * When the number of values gets too high (by adding new values to the
35 * summary), we merge some of the intervals to free space for more values.
36 * This is done in a greedy way - we simply pick the two closest intervals,
37 * merge them, and repeat this until the number of values to store gets
38 * sufficiently low (below 50% of maximum values), but that is mostly
39 * arbitrary threshold and may be changed easily).
1 /*
2 * brin_minmax_multi.c
3 * Implementation of Multi Min/Max opclass for BRIN
4 *
5 * Portions Copyright (c) 1996-2017, PostgreSQL Global Development Group
6 * Portions Copyright (c) 1994, Regents of the University of California
7 *
8 *
9 * Implements a variant of minmax opclass, where the summary is composed of
10 * multiple smaller intervals. This allows us to handle outliers, which
11 * usually make the simple minmax opclass inefficient.
12 *
13 * Consider for example page range with simple minmax interval [1000,2000],
14 * and assume a new row gets inserted into the range with value 1000000.
15 * Due to that the interval gets [1000,1000000]. I.e. the minmax interval
16 * got 1000x wider and won't be useful to eliminate scan keys between 2001
17 * and 1000000.
18 *
19 * With minmax-multi opclass, we may have [1000,2000] interval initially,
20 * but after adding the new row we start tracking it as two interval:
21 *
22 * [1000,2000] and [1000000,1000000]
23 *
24 * This allow us to still eliminate the page range when the scan keys hit
25 * the gap between 2000 and 1000000, making it useful in cases when the
26 * simple minmax opclass gets inefficient.
27 *
28 * The number of intervals tracked per page range is somewhat flexible.
29 * What is restricted is the number of values per page range, and the limit
30 * is currently 32 (see values_per_range reloption). Collapsed intervals
31 * (with equal minimum and maximum value) are stored as a single value,
32 * while regular intervals require two values.
33 *
34 * When the number of values gets too high (by adding new values to the
35 * summary), we merge some of the intervals to free space for more values.
36 * This is done in a greedy way - we simply pick the two closest intervals,
37 * merge them, and repeat this until the number of values to store gets
38 * sufficiently low (below 50% of maximum values), but that is mostly
39 * arbitrary threshold and may be changed easily).
40 *
41 * To pick the closest intervals we use the "distance" support procedure,
42 * which measures space between two ranges (i.e. length of an interval).
43 * The computed value may be an approximation - in the worst case we will
44 * merge two ranges that are slightly less optimal at that step, but the
45 * index should still produce correct results.
46 *
47 * The compactions (reducing the number of values) is fairly expensive, as
48 * it requires calling the distance functions, sorting etc. So when building
49 * the summary, we use a significantly larger buffer, and only enforce the
50 * exact limit at the very end. This improves performance, and it also helps
51 * with building better ranges (due to the greedy approach).
52 *
53 *
54 * IDENTIFICATION
55 * src/backend/access/brin/brin_minmax_multi.c
56 */
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ab596105b55f1d7fbd5a66b66f65227d210b047d
```
BRIN minmax-multi indexes master github/master
author Tomas Vondra tomas.vondra@postgresql.org
Fri, 26 Mar 2021 12:54:29 +0000 (13:54 +0100)
committer Tomas Vondra tomas.vondra@postgresql.org
Fri, 26 Mar 2021 12:54:30 +0000 (13:54 +0100)
commit ab596105b55f1d7fbd5a66b66f65227d210b047d
tree ba03f65e68913c0a684e9483a0c43e0df0229ee3 tree
parent 77b88cd1bb9041a735f24072150cacfa06c699a3 commit | diff
BRIN minmax-multi indexes
Adds BRIN opclasses similar to the existing minmax, except that instead
of summarizing the page range into a single [min,max] range, the summary
consists of multiple ranges and/or points, allowing gaps. This allows
more efficient handling of data with poor correlation to physical
location within the table and/or outlier values, for which the regular
minmax opclassed tend to work poorly.
It's possible to specify the number of values kept for each page range,
either as a single point or an interval boundary.
CREATE TABLE t (a int);
CREATE INDEX ON t
USING brin (a int4_minmax_multi_ops(values_per_range=16));
When building the summary, the values are combined into intervals with
the goal to minimize the "covering" (sum of interval lengths), using a
support procedure computing distance between two values.
Bump catversion, due to various catalog changes.
Author: Tomas Vondra tomas.vondra@postgresql.org
Reviewed-by: Alvaro Herrera alvherre@alvh.no-ip.org
Reviewed-by: Alexander Korotkov aekorotkov@gmail.com
Reviewed-by: Sokolov Yura y.sokolov@postgrespro.ru
Reviewed-by: John Naylor john.naylor@enterprisedb.com
Discussion: https://postgr.es/m/c1138ead-7668-f0e1-0638-c3be3237e812@2ndquadrant.com
Discussion: https://postgr.es/m/5d78b774-7e9c-c94e-12cf-fef51cc89b1a%402ndquadrant.com
```
用法
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 inetcol inet,
11 cidrcol cidr,
12 datecol date,
13 timecol time without time zone,
14 timestampcol timestamp without time zone,
15 timestamptzcol timestamp with time zone,
16 intervalcol interval,
17 timetzcol time with time zone,
18 numericcol numeric,
19 uuidcol uuid,
20 lsncol pg_lsn
21 ) WITH (fillfactor=10);
22 INSERT INTO brintest_multi SELECT
23 142857 * tenthous,
24 thousand,
25 twothousand,
26 unique1::oid,
27 format('(%s,%s)', tenthous, twenty)::tid,
28 (four + 1.0)/(hundred+1),
29 odd::float8 / (tenthous + 1),
30 format('%s:00:%s:00:%s:00', to_hex(odd), to_hex(even), to_hex(hundred))::macaddr,
31 inet '10.2.3.4/24' + tenthous,
32 cidr '10.2.3/24' + tenthous,
33 date '1995-08-15' + tenthous,
34 time '01:20:30' + thousand * interval '18.5 second',
35 timestamp '1942-07-23 03:05:09' + tenthous * interval '36.38 hours',
36 timestamptz '1972-10-10 03:00' + thousand * interval '1 hour',
37 justify_days(justify_hours(tenthous * interval '12 minutes')),
38 timetz '01:30:20+02' + hundred * interval '15 seconds',
39 tenthous::numeric(36,30) * fivethous * even / (hundred + 1),
40 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,
41 format('%s/%s%s', odd, even, tenthous)::pg_lsn
42 FROM tenk1 ORDER BY unique2 LIMIT 100;
43 -- throw in some NULL's and different values
44 INSERT INTO brintest_multi (inetcol, cidrcol) SELECT
45 inet 'fe80::6e40:8ff:fea9:8c46' + tenthous,
46 cidr 'fe80::6e40:8ff:fea9:8c46' + tenthous
47 FROM tenk1 ORDER BY thousand, tenthous LIMIT 25;
48 -- test minmax-multi specific index options
49 -- number of values must be >= 16
50 CREATE INDEX brinidx_multi ON brintest_multi USING brin (
51 int8col int8_minmax_multi_ops(values_per_range = 7)
52 );
53 ERROR: value 7 out of bounds for option "values_per_range"
54 DETAIL: Valid values are between "8" and "256".
55 -- number of values must be <= 256
56 CREATE INDEX brinidx_multi ON brintest_multi USING brin (
57 int8col int8_minmax_multi_ops(values_per_range = 257)
58 );
59 ERROR: value 257 out of bounds for option "values_per_range"
60 DETAIL: Valid values are between "8" and "256".
61 -- first create an index with a single page range, to force compaction
62 -- due to exceeding the number of values per summary
63 CREATE INDEX brinidx_multi ON brintest_multi USING brin (
64 int8col int8_minmax_multi_ops,
65 int2col int2_minmax_multi_ops,
66 int4col int4_minmax_multi_ops,
67 oidcol oid_minmax_multi_ops,
68 tidcol tid_minmax_multi_ops,
69 float4col float4_minmax_multi_ops,
70 float8col float8_minmax_multi_ops,
71 macaddrcol macaddr_minmax_multi_ops,
72 inetcol inet_minmax_multi_ops,
73 cidrcol inet_minmax_multi_ops,
74 datecol date_minmax_multi_ops,
75 timecol time_minmax_multi_ops,
76 timestampcol timestamp_minmax_multi_ops,
77 timestamptzcol timestamptz_minmax_multi_ops,
78 intervalcol interval_minmax_multi_ops,
79 timetzcol timetz_minmax_multi_ops,
80 numericcol numeric_minmax_multi_ops,
81 uuidcol uuid_minmax_multi_ops,
82 lsncol pg_lsn_minmax_multi_ops
83 );
84 DROP INDEX brinidx_multi;
85 CREATE INDEX brinidx_multi ON brintest_multi USING brin (
86 int8col int8_minmax_multi_ops,
87 int2col int2_minmax_multi_ops,
88 int4col int4_minmax_multi_ops,
89 oidcol oid_minmax_multi_ops,
90 tidcol tid_minmax_multi_ops,
91 float4col float4_minmax_multi_ops,
92 float8col float8_minmax_multi_ops,
93 macaddrcol macaddr_minmax_multi_ops,
94 inetcol inet_minmax_multi_ops,
95 cidrcol inet_minmax_multi_ops,
96 datecol date_minmax_multi_ops,
97 timecol time_minmax_multi_ops,
98 timestampcol timestamp_minmax_multi_ops,
99 timestamptzcol timestamptz_minmax_multi_ops,
100 intervalcol interval_minmax_multi_ops,
101 timetzcol timetz_minmax_multi_ops,
102 numericcol numeric_minmax_multi_ops,
103 uuidcol uuid_minmax_multi_ops,
104 lsncol pg_lsn_minmax_multi_ops
105 ) with (pages_per_range = 1);
106 CREATE TABLE brinopers_multi (colname name, typ text,
107 op text[], value text[], matches int[],
108 check (cardinality(op) = cardinality(value)),
109 check (cardinality(op) = cardinality(matches)));
110 INSERT INTO brinopers_multi VALUES
111 ('int2col', 'int2',
112 '{>, >=, =, <=, <}',
113 '{0, 0, 800, 999, 999}',
114 '{100, 100, 1, 100, 100}'),
115 ('int2col', 'int4',
116 '{>, >=, =, <=, <}',
117 '{0, 0, 800, 999, 1999}',
118 '{100, 100, 1, 100, 100}'),
119 ('int2col', 'int8',
120 '{>, >=, =, <=, <}',
121 '{0, 0, 800, 999, 1428427143}',
122 '{100, 100, 1, 100, 100}'),
123 ('int4col', 'int2',
124 '{>, >=, =, <=, <}',
125 '{0, 0, 800, 1999, 1999}',
126 '{100, 100, 1, 100, 100}'),
127 ('int4col', 'int4',
128 '{>, >=, =, <=, <}',
129 '{0, 0, 800, 1999, 1999}',
130 '{100, 100, 1, 100, 100}'),
131 ('int4col', 'int8',
132 '{>, >=, =, <=, <}',
133 '{0, 0, 800, 1999, 1428427143}',
134 '{100, 100, 1, 100, 100}'),
135 ('int8col', 'int2',
136 '{>, >=}',
137 '{0, 0}',
138 '{100, 100}'),
139 ('int8col', 'int4',
140 '{>, >=}',
141 '{0, 0}',
142 '{100, 100}'),
143 ('int8col', 'int8',
144 '{>, >=, =, <=, <}',
145 '{0, 0, 1257141600, 1428427143, 1428427143}',
146 '{100, 100, 1, 100, 100}'),
147 ('oidcol', 'oid',
148 '{>, >=, =, <=, <}',
149 '{0, 0, 8800, 9999, 9999}',
150 '{100, 100, 1, 100, 100}'),
151 ('tidcol', 'tid',
152 '{>, >=, =, <=, <}',
153 '{"(0,0)", "(0,0)", "(8800,0)", "(9999,19)", "(9999,19)"}',
154 '{100, 100, 1, 100, 100}'),
155 ('float4col', 'float4',
156 '{>, >=, =, <=, <}',
157 '{0.0103093, 0.0103093, 1, 1, 1}',
158 '{100, 100, 4, 100, 96}'),
159 ('float4col', 'float8',
160 '{>, >=, =, <=, <}',
161 '{0.0103093, 0.0103093, 1, 1, 1}',
162 '{100, 100, 4, 100, 96}'),
163 ('float8col', 'float4',
164 '{>, >=, =, <=, <}',
165 '{0, 0, 0, 1.98, 1.98}',
166 '{99, 100, 1, 100, 100}'),
167 ('float8col', 'float8',
168 '{>, >=, =, <=, <}',
169 '{0, 0, 0, 1.98, 1.98}',
170 '{99, 100, 1, 100, 100}'),
171 ('macaddrcol', 'macaddr',
172 '{>, >=, =, <=, <}',
173 '{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}',
174 '{99, 100, 2, 100, 100}'),
175 ('inetcol', 'inet',
176 '{=, <, <=, >, >=}',
177 '{10.2.14.231/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
178 '{1, 100, 100, 125, 125}'),
179 ('inetcol', 'cidr',
180 '{<, <=, >, >=}',
181 '{255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
182 '{100, 100, 125, 125}'),
183 ('cidrcol', 'inet',
184 '{=, <, <=, >, >=}',
185 '{10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
186 '{2, 100, 100, 125, 125}'),
187 ('cidrcol', 'cidr',
188 '{=, <, <=, >, >=}',
189 '{10.2.14/24, 255.255.255.255, 255.255.255.255, 0.0.0.0, 0.0.0.0}',
190 '{2, 100, 100, 125, 125}'),
191 ('datecol', 'date',
192 '{>, >=, =, <=, <}',
193 '{1995-08-15, 1995-08-15, 2009-12-01, 2022-12-30, 2022-12-30}',
194 '{100, 100, 1, 100, 100}'),
195 ('timecol', 'time',
196 '{>, >=, =, <=, <}',
197 '{01:20:30, 01:20:30, 02:28:57, 06:28:31.5, 06:28:31.5}',
198 '{100, 100, 1, 100, 100}'),
199 ('timestampcol', 'timestamp',
200 '{>, >=, =, <=, <}',
201 '{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}',
202 '{100, 100, 1, 100, 100}'),
203 ('timestampcol', 'timestamptz',
204 '{>, >=, =, <=, <}',
205 '{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}',
206 '{100, 100, 1, 100, 100}'),
207 ('timestamptzcol', 'timestamptz',
208 '{>, >=, =, <=, <}',
209 '{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}',
210 '{100, 100, 1, 100, 100}'),
211 ('intervalcol', 'interval',
212 '{>, >=, =, <=, <}',
213 '{00:00:00, 00:00:00, 1 mons 13 days 12:24, 2 mons 23 days 07:48:00, 1 year}',
214 '{100, 100, 1, 100, 100}'),
215 ('timetzcol', 'timetz',
216 '{>, >=, =, <=, <}',
217 '{01:30:20+02, 01:30:20+02, 01:35:50+02, 23:55:05+02, 23:55:05+02}',
218 '{99, 100, 2, 100, 100}'),
219 ('numericcol', 'numeric',
220 '{>, >=, =, <=, <}',
221 '{0.00, 0.01, 2268164.347826086956521739130434782609, 99470151.9, 99470151.9}',
222 '{100, 100, 1, 100, 100}'),
223 ('uuidcol', 'uuid',
224 '{>, >=, =, <=, <}',
225 '{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}',
226 '{100, 100, 1, 100, 100}'),
227 ('lsncol', 'pg_lsn',
228 '{>, >=, =, <=, <, IS, IS NOT}',
229 '{0/1200, 0/1200, 44/455222, 198/1999799, 198/1999799, NULL, NULL}',
230 '{100, 100, 1, 100, 100, 25, 100}');
231 DO $x$
232 DECLARE
233 r record;
234 r2 record;
235 cond text;
236 idx_ctids tid[];
237 ss_ctids tid[];
238 count int;
239 plan_ok bool;
240 plan_line text;
241 BEGIN
242 FOR r IN SELECT colname, oper, typ, value[ordinality], matches[ordinality] FROM brinopers_multi, unnest(op) WITH ORDINALITY AS oper LOOP
243
244 -- prepare the condition
245 IF r.value IS NULL THEN
246 cond := format('%I %s %L', r.colname, r.oper, r.value);
247 ELSE
248 cond := format('%I %s %L::%s', r.colname, r.oper, r.value, r.typ);
249 END IF;
250
251 -- run the query using the brin index
252 SET enable_seqscan = 0;
253 SET enable_bitmapscan = 1;
254
255 plan_ok := false;
256 FOR plan_line IN EXECUTE format($y$EXPLAIN SELECT array_agg(ctid) FROM brintest_multi WHERE %s $y$, cond) LOOP
257 IF plan_line LIKE '%Bitmap Heap Scan on brintest_multi%' THEN
258 plan_ok := true;
259 END IF;
260 END LOOP;
261 IF NOT plan_ok THEN
262 RAISE WARNING 'did not get bitmap indexscan plan for %', r;
263 END IF;
264
265 EXECUTE format($y$SELECT array_agg(ctid) FROM brintest_multi WHERE %s $y$, cond)
266 INTO idx_ctids;
267
268 -- run the query using a seqscan
269 SET enable_seqscan = 1;
270 SET enable_bitmapscan = 0;
271
272 plan_ok := false;
273 FOR plan_line IN EXECUTE format($y$EXPLAIN SELECT array_agg(ctid) FROM brintest_multi WHERE %s $y$, cond) LOOP
274 IF plan_line LIKE '%Seq Scan on brintest_multi%' THEN
275 plan_ok := true;
276 END IF;
277 END LOOP;
278 IF NOT plan_ok THEN
279 RAISE WARNING 'did not get seqscan plan for %', r;
280 END IF;
281
282 EXECUTE format($y$SELECT array_agg(ctid) FROM brintest_multi WHERE %s $y$, cond)
283 INTO ss_ctids;
284
285 -- make sure both return the same results
286 count := array_length(idx_ctids, 1);
287
288 IF NOT (count = array_length(ss_ctids, 1) AND
289 idx_ctids @> ss_ctids AND
290 idx_ctids <@ ss_ctids) THEN
291 -- report the results of each scan to make the differences obvious
292 RAISE WARNING 'something not right in %: count %', r, count;
293 SET enable_seqscan = 1;
294 SET enable_bitmapscan = 0;
295 FOR r2 IN EXECUTE 'SELECT ' || r.colname || ' FROM brintest_multi WHERE ' || cond LOOP
296 RAISE NOTICE 'seqscan: %', r2;
297 END LOOP;
298
299 SET enable_seqscan = 0;
300 SET enable_bitmapscan = 1;
301 FOR r2 IN EXECUTE 'SELECT ' || r.colname || ' FROM brintest_multi WHERE ' || cond LOOP
302 RAISE NOTICE 'bitmapscan: %', r2;
303 END LOOP;
304 END IF;
305
306 -- make sure we found expected number of matches
307 IF count != r.matches THEN RAISE WARNING 'unexpected number of results % for %', count, r; END IF;
308 END LOOP;
309 END;
310 $x$;
311 RESET enable_seqscan;
312 RESET enable_bitmapscan;
313 INSERT INTO brintest_multi SELECT
314 142857 * tenthous,
315 thousand,
316 twothousand,
317 unique1::oid,
318 format('(%s,%s)', tenthous, twenty)::tid,
319 (four + 1.0)/(hundred+1),
320 odd::float8 / (tenthous + 1),
321 format('%s:00:%s:00:%s:00', to_hex(odd), to_hex(even), to_hex(hundred))::macaddr,
322 inet '10.2.3.4' + tenthous,
323 cidr '10.2.3/24' + tenthous,
324 date '1995-08-15' + tenthous,
325 time '01:20:30' + thousand * interval '18.5 second',
326 timestamp '1942-07-23 03:05:09' + tenthous * interval '36.38 hours',
327 timestamptz '1972-10-10 03:00' + thousand * interval '1 hour',
328 justify_days(justify_hours(tenthous * interval '12 minutes')),
329 timetz '01:30:20' + hundred * interval '15 seconds',
330 tenthous::numeric(36,30) * fivethous * even / (hundred + 1),
331 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,
332 format('%s/%s%s', odd, even, tenthous)::pg_lsn
333 FROM tenk1 ORDER BY unique2 LIMIT 5 OFFSET 5;
334 SELECT brin_desummarize_range('brinidx_multi', 0);
335 brin_desummarize_range
336 ------------------------
337
338 (1 row)
339
340 VACUUM brintest_multi; -- force a summarization cycle in brinidx
341 UPDATE brintest_multi SET int8col = int8col * int4col;
342 -- Tests for brin_summarize_new_values
343 SELECT brin_summarize_new_values('brintest_multi'); -- error, not an index
344 ERROR: "brintest_multi" is not an index
345 SELECT brin_summarize_new_values('tenk1_unique1'); -- error, not a BRIN index
346 ERROR: "tenk1_unique1" is not a BRIN index
347 SELECT brin_summarize_new_values('brinidx_multi'); -- ok, no change expected
348 brin_summarize_new_values
349 ---------------------------
350 0
351 (1 row)
352
353 -- Tests for brin_desummarize_range
354 SELECT brin_desummarize_range('brinidx_multi', -1); -- error, invalid range
355 ERROR: block number out of range: -1
356 SELECT brin_desummarize_range('brinidx_multi', 0);
357 brin_desummarize_range
358 ------------------------
359
360 (1 row)
361
362 SELECT brin_desummarize_range('brinidx_multi', 0);
363 brin_desummarize_range
364 ------------------------
365
366 (1 row)
367
368 SELECT brin_desummarize_range('brinidx_multi', 100000000);
369 brin_desummarize_range
370 ------------------------
371
372 (1 row)
373
374 -- test building an index with many values, to force compaction of the buffer
375 CREATE TABLE brin_large_range (a int4);
376 INSERT INTO brin_large_range SELECT i FROM generate_series(1,10000) s(i);
377 CREATE INDEX brin_large_range_idx ON brin_large_range USING brin (a int4_minmax_multi_ops);
378 DROP TABLE brin_large_range;
379 -- Test brin_summarize_range
380 CREATE TABLE brin_summarize_multi (
381 value int
382 ) WITH (fillfactor=10, autovacuum_enabled=false);
383 CREATE INDEX brin_summarize_multi_idx ON brin_summarize_multi USING brin (value) WITH (pages_per_range=2);
384 -- Fill a few pages
385 DO $$
386 DECLARE curtid tid;
387 BEGIN
388 LOOP
389 INSERT INTO brin_summarize_multi VALUES (1) RETURNING ctid INTO curtid;
390 EXIT WHEN curtid > tid '(2, 0)';
391 END LOOP;
392 END;
393 $$;
394 -- summarize one range
395 SELECT brin_summarize_range('brin_summarize_multi_idx', 0);
396 brin_summarize_range
397 ----------------------
398 0
399 (1 row)
400
401 -- nothing: already summarized
402 SELECT brin_summarize_range('brin_summarize_multi_idx', 1);
403 brin_summarize_range
404 ----------------------
405 0
406 (1 row)
407
408 -- summarize one range
409 SELECT brin_summarize_range('brin_summarize_multi_idx', 2);
410 brin_summarize_range
411 ----------------------
412 1
413 (1 row)
414
415 -- nothing: page doesn't exist in table
416 SELECT brin_summarize_range('brin_summarize_multi_idx', 4294967295);
417 brin_summarize_range
418 ----------------------
419 0
420 (1 row)
421
422 -- invalid block number values
423 SELECT brin_summarize_range('brin_summarize_multi_idx', -1);
424 ERROR: block number out of range: -1
425 SELECT brin_summarize_range('brin_summarize_multi_idx', 4294967296);
426 ERROR: block number out of range: 4294967296
427 -- test brin cost estimates behave sanely based on correlation of values
428 CREATE TABLE brin_test_multi (a INT, b INT);
429 INSERT INTO brin_test_multi SELECT x/100,x%100 FROM generate_series(1,10000) x(x);
430 CREATE INDEX brin_test_multi_a_idx ON brin_test_multi USING brin (a) WITH (pages_per_range = 2);
431 CREATE INDEX brin_test_multi_b_idx ON brin_test_multi USING brin (b) WITH (pages_per_range = 2);
432 VACUUM ANALYZE brin_test_multi;
433 -- Ensure brin index is used when columns are perfectly correlated
434 EXPLAIN (COSTS OFF) SELECT * FROM brin_test_multi WHERE a = 1;
435 QUERY PLAN
436 --------------------------------------------------
437 Bitmap Heap Scan on brin_test_multi
438 Recheck Cond: (a = 1)
439 -> Bitmap Index Scan on brin_test_multi_a_idx
440 Index Cond: (a = 1)
441 (4 rows)
442
443 -- Ensure brin index is not used when values are not correlated
444 EXPLAIN (COSTS OFF) SELECT * FROM brin_test_multi WHERE b = 1;
445 QUERY PLAN
446 -----------------------------
447 Seq Scan on brin_test_multi
448 Filter: (b = 1)
449 (2 rows)
450
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
