public inbox for [email protected]help / color / mirror / Atom feed
[PATCH 4/8] BRIN bloom indexes 5+ messages / 4 participants [nested] [flat]
* [PATCH 4/8] BRIN bloom indexes @ 2020-12-16 20:24 Tomas Vondra <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Tomas Vondra @ 2020-12-16 20:24 UTC (permalink / raw) Adds a BRIN opclass using a Bloom filter to summarize the range. BRIN indexes using the new opclasses only allow equality queries, but that works for data like UUID, MAC addresses etc. for which range queries are not very common (and the data look random, making BRIN minmax indexes inefficient anyway). BRIN bloom opclasses allow specifying the usual Bloom parameters, like expected number of distinct values (in the BRIN range) and desired false positive rate. The opclasses store 32-bit hashes of the values, not the raw indexed values. This assumes the hash functions for data types has low number of collisions, good performance etc. Collisions are not a huge issue though, because the number of values in a BRIN ranges is fairly small. The summary does not start as a Bloom filter right away - it initially stores a plain array of hashes. Only when the size of the array grows too large it switches to proper Bloom filter. This means that ranges with low number of distinct values the summary will use less space. Author: Tomas Vondra <[email protected]> Reviewed-by: Alvaro Herrera <[email protected]> Reviewed-by: Alexander Korotkov <[email protected]> Reviewed-by: Sokolov Yura <[email protected]> Discussion: https://postgr.es/m/[email protected] Discussion: https://postgr.es/m/5d78b774-7e9c-c94e-12cf-fef51cc89b1a%402ndquadrant.com --- doc/src/sgml/brin.sgml | 178 ++++ doc/src/sgml/ref/create_index.sgml | 31 + src/backend/access/brin/Makefile | 1 + src/backend/access/brin/brin_bloom.c | 1101 +++++++++++++++++++++ src/include/access/brin.h | 2 + src/include/access/brin_internal.h | 4 + src/include/catalog/pg_amop.dat | 170 ++++ src/include/catalog/pg_amproc.dat | 447 +++++++++ src/include/catalog/pg_opclass.dat | 72 ++ src/include/catalog/pg_opfamily.dat | 38 + src/include/catalog/pg_proc.dat | 34 + src/include/catalog/pg_type.dat | 7 + src/test/regress/expected/brin_bloom.out | 456 +++++++++ src/test/regress/expected/opr_sanity.out | 3 +- src/test/regress/expected/psql.out | 3 +- src/test/regress/expected/type_sanity.out | 7 +- src/test/regress/parallel_schedule | 5 + src/test/regress/serial_schedule | 1 + src/test/regress/sql/brin_bloom.sql | 404 ++++++++ 19 files changed, 2959 insertions(+), 5 deletions(-) create mode 100644 src/backend/access/brin/brin_bloom.c create mode 100644 src/test/regress/expected/brin_bloom.out create mode 100644 src/test/regress/sql/brin_bloom.sql diff --git a/doc/src/sgml/brin.sgml b/doc/src/sgml/brin.sgml index 4420794e5b..577dd18c49 100644 --- a/doc/src/sgml/brin.sgml +++ b/doc/src/sgml/brin.sgml @@ -128,6 +128,10 @@ LOG: request for BRIN range summarization for index "brin_wi_idx" page 128 was </row> </thead> <tbody> + <row> + <entry valign="middle"><literal>int8_bloom_ops</literal></entry> + <entry><literal>= (bit,bit)</literal></entry> + </row> <row> <entry valign="middle" morerows="4"><literal>bit_minmax_ops</literal></entry> <entry><literal>= (bit,bit)</literal></entry> @@ -154,6 +158,11 @@ LOG: request for BRIN range summarization for index "brin_wi_idx" page 128 was <row><entry><literal>|&> (box,box)</literal></entry></row> <row><entry><literal>|>> (box,box)</literal></entry></row> + <row> + <entry valign="middle"><literal>bpchar_bloom_ops</literal></entry> + <entry><literal>= (character,character)</literal></entry> + </row> + <row> <entry valign="middle" morerows="4"><literal>bpchar_minmax_ops</literal></entry> <entry><literal>= (character,character)</literal></entry> @@ -163,6 +172,11 @@ LOG: request for BRIN range summarization for index "brin_wi_idx" page 128 was <row><entry><literal>> (character,character)</literal></entry></row> <row><entry><literal>>= (character,character)</literal></entry></row> + <row> + <entry valign="middle"><literal>bytea_bloom_ops</literal></entry> + <entry><literal>= (bytea,bytea)</literal></entry> + </row> + <row> <entry valign="middle" morerows="4"><literal>bytea_minmax_ops</literal></entry> <entry><literal>= (bytea,bytea)</literal></entry> @@ -172,6 +186,11 @@ LOG: request for BRIN range summarization for index "brin_wi_idx" page 128 was <row><entry><literal>> (bytea,bytea)</literal></entry></row> <row><entry><literal>>= (bytea,bytea)</literal></entry></row> + <row> + <entry valign="middle"><literal>char_bloom_ops</literal></entry> + <entry><literal>= ("char","char")</literal></entry> + </row> + <row> <entry valign="middle" morerows="4"><literal>char_minmax_ops</literal></entry> <entry><literal>= ("char","char")</literal></entry> @@ -181,6 +200,11 @@ LOG: request for BRIN range summarization for index "brin_wi_idx" page 128 was <row><entry><literal>> ("char","char")</literal></entry></row> <row><entry><literal>>= ("char","char")</literal></entry></row> + <row> + <entry valign="middle"><literal>date_bloom_ops</literal></entry> + <entry><literal>= (date,date)</literal></entry> + </row> + <row> <entry valign="middle" morerows="4"><literal>date_minmax_ops</literal></entry> <entry><literal>= (date,date)</literal></entry> @@ -190,6 +214,11 @@ LOG: request for BRIN range summarization for index "brin_wi_idx" page 128 was <row><entry><literal>> (date,date)</literal></entry></row> <row><entry><literal>>= (date,date)</literal></entry></row> + <row> + <entry valign="middle"><literal>float4_bloom_ops</literal></entry> + <entry><literal>= (float4,float4)</literal></entry> + </row> + <row> <entry valign="middle" morerows="4"><literal>float4_minmax_ops</literal></entry> <entry><literal>= (float4,float4)</literal></entry> @@ -199,6 +228,11 @@ LOG: request for BRIN range summarization for index "brin_wi_idx" page 128 was <row><entry><literal><= (float4,float4)</literal></entry></row> <row><entry><literal>>= (float4,float4)</literal></entry></row> + <row> + <entry valign="middle"><literal>float8_bloom_ops</literal></entry> + <entry><literal>= (float8,float8)</literal></entry> + </row> + <row> <entry valign="middle" morerows="4"><literal>float8_minmax_ops</literal></entry> <entry><literal>= (float8,float8)</literal></entry> @@ -218,6 +252,11 @@ LOG: request for BRIN range summarization for index "brin_wi_idx" page 128 was <row><entry><literal>= (inet,inet)</literal></entry></row> <row><entry><literal>&& (inet,inet)</literal></entry></row> + <row> + <entry valign="middle"><literal>inet_bloom_ops</literal></entry> + <entry><literal>= (inet,inet)</literal></entry> + </row> + <row> <entry valign="middle" morerows="4"><literal>inet_minmax_ops</literal></entry> <entry><literal>= (inet,inet)</literal></entry> @@ -227,6 +266,11 @@ LOG: request for BRIN range summarization for index "brin_wi_idx" page 128 was <row><entry><literal>> (inet,inet)</literal></entry></row> <row><entry><literal>>= (inet,inet)</literal></entry></row> + <row> + <entry valign="middle"><literal>int2_bloom_ops</literal></entry> + <entry><literal>= (int2,int2)</literal></entry> + </row> + <row> <entry valign="middle" morerows="4"><literal>int2_minmax_ops</literal></entry> <entry><literal>= (int2,int2)</literal></entry> @@ -236,6 +280,11 @@ LOG: request for BRIN range summarization for index "brin_wi_idx" page 128 was <row><entry><literal><= (int2,int2)</literal></entry></row> <row><entry><literal>>= (int2,int2)</literal></entry></row> + <row> + <entry valign="middle"><literal>int4_bloom_ops</literal></entry> + <entry><literal>= (int4,int4)</literal></entry> + </row> + <row> <entry valign="middle" morerows="4"><literal>int4_minmax_ops</literal></entry> <entry><literal>= (int4,int4)</literal></entry> @@ -245,6 +294,11 @@ LOG: request for BRIN range summarization for index "brin_wi_idx" page 128 was <row><entry><literal><= (int4,int4)</literal></entry></row> <row><entry><literal>>= (int4,int4)</literal></entry></row> + <row> + <entry valign="middle"><literal>int8_bloom_ops</literal></entry> + <entry><literal>= (bigint,bigint)</literal></entry> + </row> + <row> <entry valign="middle" morerows="4"><literal>int8_minmax_ops</literal></entry> <entry><literal>= (bigint,bigint)</literal></entry> @@ -254,6 +308,11 @@ LOG: request for BRIN range summarization for index "brin_wi_idx" page 128 was <row><entry><literal><= (bigint,bigint)</literal></entry></row> <row><entry><literal>>= (bigint,bigint)</literal></entry></row> + <row> + <entry valign="middle"><literal>interval_bloom_ops</literal></entry> + <entry><literal>= (interval,interval)</literal></entry> + </row> + <row> <entry valign="middle" morerows="4"><literal>interval_minmax_ops</literal></entry> <entry><literal>= (interval,interval)</literal></entry> @@ -263,6 +322,11 @@ LOG: request for BRIN range summarization for index "brin_wi_idx" page 128 was <row><entry><literal>> (interval,interval)</literal></entry></row> <row><entry><literal>>= (interval,interval)</literal></entry></row> + <row> + <entry valign="middle"><literal>macaddr_bloom_ops</literal></entry> + <entry><literal>= (macaddr,macaddr)</literal></entry> + </row> + <row> <entry valign="middle" morerows="4"><literal>macaddr_minmax_ops</literal></entry> <entry><literal>= (macaddr,macaddr)</literal></entry> @@ -272,6 +336,11 @@ LOG: request for BRIN range summarization for index "brin_wi_idx" page 128 was <row><entry><literal>> (macaddr,macaddr)</literal></entry></row> <row><entry><literal>>= (macaddr,macaddr)</literal></entry></row> + <row> + <entry valign="middle"><literal>macaddr8_bloom_ops</literal></entry> + <entry><literal>= (macaddr8,macaddr8)</literal></entry> + </row> + <row> <entry valign="middle" morerows="4"><literal>macaddr8_minmax_ops</literal></entry> <entry><literal>= (macaddr8,macaddr8)</literal></entry> @@ -281,6 +350,11 @@ LOG: request for BRIN range summarization for index "brin_wi_idx" page 128 was <row><entry><literal>> (macaddr8,macaddr8)</literal></entry></row> <row><entry><literal>>= (macaddr8,macaddr8)</literal></entry></row> + <row> + <entry valign="middle"><literal>name_bloom_ops</literal></entry> + <entry><literal>= (name,name)</literal></entry> + </row> + <row> <entry valign="middle" morerows="4"><literal>name_minmax_ops</literal></entry> <entry><literal>= (name,name)</literal></entry> @@ -290,6 +364,11 @@ LOG: request for BRIN range summarization for index "brin_wi_idx" page 128 was <row><entry><literal>> (name,name)</literal></entry></row> <row><entry><literal>>= (name,name)</literal></entry></row> + <row> + <entry valign="middle"><literal>numeric_bloom_ops</literal></entry> + <entry><literal>= (numeric,numeric)</literal></entry> + </row> + <row> <entry valign="middle" morerows="4"><literal>numeric_minmax_ops</literal></entry> <entry><literal>= (numeric,numeric)</literal></entry> @@ -299,6 +378,11 @@ LOG: request for BRIN range summarization for index "brin_wi_idx" page 128 was <row><entry><literal>> (numeric,numeric)</literal></entry></row> <row><entry><literal>>= (numeric,numeric)</literal></entry></row> + <row> + <entry valign="middle"><literal>oid_bloom_ops</literal></entry> + <entry><literal>= (oid,oid)</literal></entry> + </row> + <row> <entry valign="middle" morerows="4"><literal>oid_minmax_ops</literal></entry> <entry><literal>= (oid,oid)</literal></entry> @@ -308,6 +392,11 @@ LOG: request for BRIN range summarization for index "brin_wi_idx" page 128 was <row><entry><literal><= (oid,oid)</literal></entry></row> <row><entry><literal>>= (oid,oid)</literal></entry></row> + <row> + <entry valign="middle"><literal>pg_lsn_bloom_ops</literal></entry> + <entry><literal>= (pg_lsn,pg_lsn)</literal></entry> + </row> + <row> <entry valign="middle" morerows="4"><literal>pg_lsn_minmax_ops</literal></entry> <entry><literal>= (pg_lsn,pg_lsn)</literal></entry> @@ -335,6 +424,11 @@ LOG: request for BRIN range summarization for index "brin_wi_idx" page 128 was <row><entry><literal>&> (anyrange,anyrange)</literal></entry></row> <row><entry><literal>-|- (anyrange,anyrange)</literal></entry></row> + <row> + <entry valign="middle"><literal>text_bloom_ops</literal></entry> + <entry><literal>= (text,text)</literal></entry> + </row> + <row> <entry valign="middle" morerows="4"><literal>text_minmax_ops</literal></entry> <entry><literal>= (text,text)</literal></entry> @@ -344,6 +438,11 @@ LOG: request for BRIN range summarization for index "brin_wi_idx" page 128 was <row><entry><literal>> (text,text)</literal></entry></row> <row><entry><literal>>= (text,text)</literal></entry></row> + <row> + <entry valign="middle" morerows="4"><literal>tid_bloom_ops</literal></entry> + <entry><literal>= (tid,tid)</literal></entry> + </row> + <row> <entry valign="middle" morerows="4"><literal>tid_minmax_ops</literal></entry> <entry><literal>= (tid,tid)</literal></entry> @@ -353,6 +452,11 @@ LOG: request for BRIN range summarization for index "brin_wi_idx" page 128 was <row><entry><literal><= (tid,tid)</literal></entry></row> <row><entry><literal>>= (tid,tid)</literal></entry></row> + <row> + <entry valign="middle"><literal>timestamp_bloom_ops</literal></entry> + <entry><literal>= (timestamp,timestamp)</literal></entry> + </row> + <row> <entry valign="middle" morerows="4"><literal>timestamp_minmax_ops</literal></entry> <entry><literal>= (timestamp,timestamp)</literal></entry> @@ -362,6 +466,11 @@ LOG: request for BRIN range summarization for index "brin_wi_idx" page 128 was <row><entry><literal>> (timestamp,timestamp)</literal></entry></row> <row><entry><literal>>= (timestamp,timestamp)</literal></entry></row> + <row> + <entry valign="middle"><literal>timestamptz_bloom_ops</literal></entry> + <entry><literal>= (timestamptz,timestamptz)</literal></entry> + </row> + <row> <entry valign="middle" morerows="4"><literal>timestamptz_minmax_ops</literal></entry> <entry><literal>= (timestamptz,timestamptz)</literal></entry> @@ -371,6 +480,11 @@ LOG: request for BRIN range summarization for index "brin_wi_idx" page 128 was <row><entry><literal>> (timestamptz,timestamptz)</literal></entry></row> <row><entry><literal>>= (timestamptz,timestamptz)</literal></entry></row> + <row> + <entry valign="middle"><literal>time_bloom_ops</literal></entry> + <entry><literal>= (time,time)</literal></entry> + </row> + <row> <entry valign="middle" morerows="4"><literal>time_minmax_ops</literal></entry> <entry><literal>= (time,time)</literal></entry> @@ -380,6 +494,11 @@ LOG: request for BRIN range summarization for index "brin_wi_idx" page 128 was <row><entry><literal>> (time,time)</literal></entry></row> <row><entry><literal>>= (time,time)</literal></entry></row> + <row> + <entry valign="middle"><literal>timetz_bloom_ops</literal></entry> + <entry><literal>= (timetz,timetz)</literal></entry> + </row> + <row> <entry valign="middle" morerows="4"><literal>timetz_minmax_ops</literal></entry> <entry><literal>= (timetz,timetz)</literal></entry> @@ -389,6 +508,11 @@ LOG: request for BRIN range summarization for index "brin_wi_idx" page 128 was <row><entry><literal>> (timetz,timetz)</literal></entry></row> <row><entry><literal>>= (timetz,timetz)</literal></entry></row> + <row> + <entry valign="middle"><literal>uuid_bloom_ops</literal></entry> + <entry><literal>= (uuid,uuid)</literal></entry> + </row> + <row> <entry valign="middle" morerows="4"><literal>uuid_minmax_ops</literal></entry> <entry><literal>= (uuid,uuid)</literal></entry> @@ -779,6 +903,60 @@ typedef struct BrinOpcInfo function can improve index performance. </para> + <para> + To write an operator class for a data type that implements only an equality + operator and supports hashing, it is possible to use the bloom support procedures + alongside the corresponding operators, as shown in + <xref linkend="brin-extensibility-bloom-table"/>. + All operator class members (procedures and operators) are mandatory. + </para> + + <table id="brin-extensibility-bloom-table"> + <title>Procedure and Support Numbers for Bloom Operator Classes</title> + <tgroup cols="2"> + <thead> + <row> + <entry>Operator class member</entry> + <entry>Object</entry> + </row> + </thead> + <tbody> + <row> + <entry>Support Procedure 1</entry> + <entry>internal function <function>brin_bloom_opcinfo()</function></entry> + </row> + <row> + <entry>Support Procedure 2</entry> + <entry>internal function <function>brin_bloom_add_value()</function></entry> + </row> + <row> + <entry>Support Procedure 3</entry> + <entry>internal function <function>brin_bloom_consistent()</function></entry> + </row> + <row> + <entry>Support Procedure 4</entry> + <entry>internal function <function>brin_bloom_union()</function></entry> + </row> + <row> + <entry>Support Procedure 11</entry> + <entry>function to compute hash of an element</entry> + </row> + <row> + <entry>Operator Strategy 1</entry> + <entry>operator equal-to</entry> + </row> + </tbody> + </tgroup> + </table> + + <para> + Support procedure numbers 1-10 are reserved for the BRIN internal + functions, so the SQL level functions start with number 11. Support + function number 11 is the main function required to build the index. + It should accept one argument with the same data type as the operator class, + and return a hash of the value. + </para> + <para> Both minmax and inclusion operator classes support cross-data-type operators, though with these the dependencies become more complicated. diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml index 2054d5d943..8db10b7b1e 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -559,6 +559,37 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class= </para> </listitem> </varlistentry> + + <varlistentry> + <term><literal>n_distinct_per_range</literal></term> + <listitem> + <para> + Defines the estimated number of distinct non-null values in the block + range, used by <acronym>BRIN</acronym> bloom indexes for sizing of the + Bloom filter. It behaves similarly to <literal>n_distinct</literal> option + for <xref linkend="sql-altertable"/>. When set to a positive value, + each block range is assumed to contain this number of distinct non-null + values. When set to a negative value, which must be greater than or + equal to -1, the number of distinct non-null is assumed linear with + the maximum possible number of tuples in the block range (about 290 + rows per block). The default values is <literal>-0.1</literal>, and + the minimum number of distinct non-null values is <literal>128</literal>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>false_positive_rate</literal></term> + <listitem> + <para> + Defines the desired false positive rate used by <acronym>BRIN</acronym> + bloom indexes for sizing of the Bloom filter. The values must be be + greater than 0.0 and smaller than 1.0. The default values is 0.01, + which is 1% false positive rate. + </para> + </listitem> + </varlistentry> + </variablelist> </refsect2> diff --git a/src/backend/access/brin/Makefile b/src/backend/access/brin/Makefile index 468e1e289a..6b56131215 100644 --- a/src/backend/access/brin/Makefile +++ b/src/backend/access/brin/Makefile @@ -14,6 +14,7 @@ include $(top_builddir)/src/Makefile.global OBJS = \ brin.o \ + brin_bloom.o \ brin_inclusion.o \ brin_minmax.o \ brin_pageops.o \ diff --git a/src/backend/access/brin/brin_bloom.c b/src/backend/access/brin/brin_bloom.c new file mode 100644 index 0000000000..b7aa6d9f11 --- /dev/null +++ b/src/backend/access/brin/brin_bloom.c @@ -0,0 +1,1101 @@ +/* + * brin_bloom.c + * Implementation of Bloom opclass for BRIN + * + * Portions Copyright (c) 1996-2017, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * + * A BRIN opclass summarizing page range into a bloom filter. + * + * Bloom filters allow efficient test whether a given page range contains + * a particular value. Therefore, if we summarize each page range into a + * bloom filter, we can easily and cheaply test wheter it containst values + * we get later. + * + * The index only supports equality operator, similarly to hash indexes. + * BRIN bloom indexes are however much smaller, and support only bitmap + * scans. + * + * Note: Don't confuse this with bloom indexes, implemented in a contrib + * module. That extension implements an entirely new AM, building a bloom + * filter on multiple columns in a single row. This opclass works with an + * existing AM (BRIN) and builds bloom filter on a column. + * + * + * values vs. hashes + * ----------------- + * + * The original column values are not used directly, but are first hashed + * using the regular type-specific hash function, producing a uint32 hash. + * And this hash value is then added to the summary - either stored as is + * (in the sorted mode), or hashed again and added to the bloom filter. + * + * This allows the code to treat all data types (byval/byref/...) the same + * way, with only minimal space requirements. For example we don't need to + * store varlena types differently in the sorted mode, etc. Everything is + * uint32 making it much simpler. + * + * Of course, this assumes the built-in hash function is reasonably good, + * without too many collisions etc. But that does seem to be the case, at + * least based on past experience. After all, the same hash functions are + * used for hash indexes, hash partitioning and so on. + * + * + * sizing the bloom filter + * ----------------------- + * + * Size of a bloom filter depends on the number of distinct values we will + * store in it, and the desired false positive rate. The higher the number + * of distinct values and/or the lower the false positive rate, the larger + * the bloom filter. On the other hand, we want to keep the index as small + * as possible - that's one of the basic advantages of BRIN indexes. + * + * The number of distinct elements (in a page range) depends on the data, + * we can consider it fixed. This simplifies the trade-off to just false + * positive rate vs. size. + * + * At the page range level, false positive rate is a probability the bloom + * filter matches a random value. For the whole index (with sufficiently + * many page ranges) it represents the fraction of the index ranges (and + * thus fraction of the table to be scanned) matching the random value. + * + * Furthermore, the size of the bloom filter is subject to implementation + * limits - it has to fit onto a single index page (8kB by default). As + * the bitmap is inherently random, compression can't reliably help here. + * To reduce the size of a filter (to fit to a page), we have to either + * accept higher false positive rate (undesirable), or reduce the number + * of distinct items to be stored in the filter. We can't quite the input + * data, of course, but we may make the BRIN page ranges smaller - instead + * of the default 128 pages (1MB) we may build index with 16-page ranges, + * or something like that. This does help even for random data sets, as + * the number of rows per heap page is limited (to ~290 with very narrow + * tables, likely ~20 in practice). + * + * Of course, good sizing decisions depend on having the necessary data, + * i.e. number of distinct values in a page range (of a given size) and + * table size (to estimate cost change due to change in false positive + * rate due to having larger index vs. scanning larger indexes). We may + * not have that data - for example when building an index on empty table + * it's not really possible. And for some data we only have estimates for + * the whole table and we can only estimate per-range values (ndistinct). + * + * Another challenge is that while the bloom filter is per-column, it's + * the whole index tuple that has to fit into a page. And for multi-column + * indexes that may include pieces we have no control over (not necessarily + * bloom filters, the other columns may use other BRIN opclasses). So it's + * not entirely clear how to distrubute the space between those columns. + * + * The current logic, implemented in brin_bloom_get_ndistinct, attempts to + * make some basic sizing decisions, based on the table ndistinct estimate. + * + * + * sort vs. hash + * ------------- + * + * As explained in the preceding section, sizing bloom filters correctly is + * difficult in practice. It's also true that bloom filters quickly degrade + * after exceeding the expected number of distinct items. It's therefore + * expected that people will overshoot the parameters a bit (particularly + * the ndistinct per page range), perhaps by a factor of 2 or more. + * + * It's also possible the data set is not uniform - it may have ranges with + * very many distinct items per range, but also ranges with only very few + * distinct items. The bloom filter has to be sized for the more variable + * ranges, making it rather wasteful in the less variable part. + * + * For example, if some page ranges have 1000 distinct values, that means + * about ~1.2kB bloom filter with 1% false positive rate. For page ranges + * that only contain 10 distinct values, that's wasteful, because we might + * store the values (the uint32 hashes) in just 40B. + * + * To address these issues, the opclass stores the raw values directly, and + * only switches to the actual bloom filter after reaching the same space + * requirements. + * + * + * IDENTIFICATION + * src/backend/access/brin/brin_bloom.c + */ +#include "postgres.h" + +#include "access/genam.h" +#include "access/brin.h" +#include "access/brin_internal.h" +#include "access/brin_tuple.h" +#include "access/hash.h" +#include "access/htup_details.h" +#include "access/reloptions.h" +#include "access/stratnum.h" +#include "catalog/pg_type.h" +#include "catalog/pg_amop.h" +#include "utils/builtins.h" +#include "utils/datum.h" +#include "utils/lsyscache.h" +#include "utils/rel.h" +#include "utils/syscache.h" + +#include <math.h> + +#define BloomEqualStrategyNumber 1 + +/* + * Additional SQL level support functions + * + * Procedure numbers must not use values reserved for BRIN itself; see + * brin_internal.h. + */ +#define BLOOM_MAX_PROCNUMS 1 /* maximum support procs we need */ +#define PROCNUM_HASH 11 /* required */ + +/* + * Subtract this from procnum to obtain index in BloomOpaque arrays + * (Must be equal to minimum of private procnums). + */ +#define PROCNUM_BASE 11 + +/* + * Flags. We only use a single bit for now, to decide whether we're in the + * sorted or hash phase. So we have 15 bits for future use, if needed. The + * filters are expected to be hundreds of bytes, so this is negligible. + */ +#define BLOOM_FLAG_PHASE_HASH 0x0001 + +#define BLOOM_IS_HASHED(f) ((f)->flags & BLOOM_FLAG_PHASE_HASH) +#define BLOOM_IS_SORTED(f) (!BLOOM_IS_HASHED(f)) + +/* + * Number of hashes to accumulate before deduplicating and sorting in the + * sort phase? We want this fairly small to reduce the amount of space and + * also speed-up bsearch lookups. + */ +#define BLOOM_MAX_UNSORTED 32 + +/* + * Storage type for BRIN's reloptions. + */ +typedef struct BloomOptions +{ + int32 vl_len_; /* varlena header (do not touch directly!) */ + double nDistinctPerRange; /* number of distinct values per range */ + double falsePositiveRate; /* false positive for bloom filter */ +} BloomOptions; + +/* + * The current min value (16) is somewhat arbitrary, but it's based + * on the fact that the filter header is ~20B alone, which is about + * the same as the filter bitmap for 16 distinct items with 1% false + * positive rate. So by allowing lower values we'd not gain much. In + * any case, the min should not be larger than MaxHeapTuplesPerPage + * (~290), which is the theoretical maximum for single-page ranges. + */ +#define BLOOM_MIN_NDISTINCT_PER_RANGE 16 + +/* + * Used to determine number of distinct items, based on the number of rows + * in a page range. The 10% is somewhat similar to what estimate_num_groups + * does, so we use the same factor here. + */ +#define BLOOM_DEFAULT_NDISTINCT_PER_RANGE -0.1 /* 10% of values */ + +/* + * Allowed range and default value for the false positive range. The exact + * values are somewhat arbitrary. + */ +#define BLOOM_MIN_FALSE_POSITIVE_RATE 0.001 /* 0.1% fp rate */ +#define BLOOM_MAX_FALSE_POSITIVE_RATE 0.1 /* 10% fp rate */ +#define BLOOM_DEFAULT_FALSE_POSITIVE_RATE 0.01 /* 1% fp rate */ + +#define BloomGetNDistinctPerRange(opts) \ + ((opts) && (((BloomOptions *) (opts))->nDistinctPerRange != 0) ? \ + (((BloomOptions *) (opts))->nDistinctPerRange) : \ + BLOOM_DEFAULT_NDISTINCT_PER_RANGE) + +#define BloomGetFalsePositiveRate(opts) \ + ((opts) && (((BloomOptions *) (opts))->falsePositiveRate != 0.0) ? \ + (((BloomOptions *) (opts))->falsePositiveRate) : \ + BLOOM_DEFAULT_FALSE_POSITIVE_RATE) + +/* + * Bloom Filter + * + * Represents a bloom filter, built on hashes of the indexed values. That is, + * we compute a uint32 hash of the value, and then store this hash into the + * bloom filter (and compute additional hashes on it). + * + * We use an optimisation that initially we store the uint32 values directly, + * without the extra hashing step. And only later filling the bitmap space, + * we switch to the regular bloom filter mode. + * + * PHASE_SORTED + * + * Initially we copy the uint32 hash into the bitmap, regularly sorting the + * hash values for fast lookup (we keep at most BLOOM_MAX_UNSORTED unsorted + * values). + * + * The idea is that if we only see very few distinct values, we can store + * them in less space compared to the (sparse) bloom filter bitmap. It also + * stores them exactly, although that's not a big advantage as almost-empty + * bloom filter has false positive rate close to zero anyway. + * + * PHASE_HASH + * + * Once we fill the bitmap space in the sorted phase, we switch to the hash + * phase, where we actually use the bloom filter. We treat the uint32 hashes + * as input values, and hash them again with different seeds (to get the k + * hash functions needed for bloom filter). + * + * + * XXX Perhaps we could save a few bytes by using different data types, but + * considering the size of the bitmap, the difference is negligible. + * + * XXX We could also implement "sparse" bloom filters, keeping only the + * bytes that are not entirely 0. That might make the "sorted" phase + * mostly unnecessary. + * + * XXX We can also watch the number of bits set in the bloom filter, and + * then stop using it (and not store the bitmap, to save space) when the + * false positive rate gets too high. + */ +typedef struct BloomFilter +{ + /* varlena header (do not touch directly!) */ + int32 vl_len_; + + /* space for various flags (phase, etc.) */ + uint16 flags; + + /* fields used only in the SORTED phase */ + uint16 nvalues; /* number of hashes stored (total) */ + uint16 nsorted; /* number of hashes in the sorted part */ + + /* fields for the HASHED phase */ + uint8 nhashes; /* number of hash functions */ + uint32 nbits; /* number of bits in the bitmap (size) */ + uint32 nbits_set; /* number of bits set to 1 */ + + /* data of the bloom filter (used both for sorted and hashed phase) */ + char data[FLEXIBLE_ARRAY_MEMBER]; + +} BloomFilter; + +static BloomFilter *bloom_switch_to_hashing(BloomFilter *filter); + + +/* + * bloom_init + * Initialize the Bloom Filter, allocate all the memory. + * + * The filter is initialized with optimal size for ndistinct expected + * values, and requested false positive rate. The filter is stored as + * varlena. + */ +static BloomFilter * +bloom_init(int ndistinct, double false_positive_rate) +{ + Size len; + BloomFilter *filter; + + int m; /* number of bits */ + double k; /* number of hash functions */ + + Assert(ndistinct > 0); + Assert((false_positive_rate > 0) && (false_positive_rate < 1.0)); + + m = ceil((ndistinct * log(false_positive_rate)) / log(1.0 / (pow(2.0, log(2.0))))); + + /* round m to whole bytes */ + m = ((m + 7) / 8) * 8; + + /* + * round(log(2.0) * m / ndistinct), but assume round() may not be + * available on Windows + */ + k = log(2.0) * m / ndistinct; + k = (k - floor(k) >= 0.5) ? ceil(k) : floor(k); + + /* + * Allocate the bloom filter with a minimum size 64B (about 40B in the + * bitmap part). We require space at least for the header. + * + * XXX Maybe the 64B min size is not really needed? + */ + len = Max(offsetof(BloomFilter, data), 64); + + /* Reject filters that are obviously too large to store on a page. */ + if (len > BLCKSZ) + elog(ERROR, "the bloom filter is too large (%zu > %d)", len, BLCKSZ); + + filter = (BloomFilter *) palloc0(len); + + filter->flags = 0; /* implies SORTED phase */ + filter->nhashes = (int) k; + filter->nbits = m; + + SET_VARSIZE(filter, len); + + return filter; +} + +/* simple uint32 comparator, for pg_qsort and bsearch */ +static int +cmp_uint32(const void *a, const void *b) +{ + uint32 *ia = (uint32 *) a; + uint32 *ib = (uint32 *) b; + + if (*ia == *ib) + return 0; + else if (*ia < *ib) + return -1; + else + return 1; +} + +/* + * bloom_compact + * Compact the filter during the 'sorted' phase. + * + * We sort the uint32 hashes and remove duplicates, for two main reasons. + * Firstly, to keep most of the data sorted for bsearch lookups. Secondly, + * we try to save space by removing the duplicates, allowing us to stay + * in the sorted phase a bit longer. + * + * We currently don't repalloc the bitmap, i.e. we don't free the memory + * here - in the worst case we waste space for up to 32 unsorted hashes + * (if all of them are already in the sorted part), so about 128B. We can + * either reduce the number of unsorted items (e.g. to 8 hashes, which + * would mean 32B), or start doing the repalloc. + * + * We do however set the varlena length, to minimize the storage needs. + */ +static void +bloom_compact(BloomFilter *filter) +{ + int i, j, k; + Size len; + + uint32 *values; + uint32 *result; + uint32 *sorted; + uint32 *unsorted; + + int nvalues; + int nsorted; + int nunsorted; + + /* never call compact on filters in HASH phase */ + Assert(BLOOM_IS_SORTED(filter)); + + /* when already fully sorted, no chance to compact anything */ + if (filter->nvalues == filter->nsorted) + return; + + values = (uint32 *) filter->data; + + /* result of merging */ + k = 0; + result = (uint32 *) palloc(sizeof(uint32) * filter->nvalues); + + /* first we have sorted data, then unsorted */ + sorted = values; + nsorted = filter->nsorted; + + unsorted = &values[filter->nsorted]; + nunsorted = filter->nvalues - filter->nsorted; + + /* sort the unsorted part, then merge the two parts */ + pg_qsort(unsorted, nunsorted, sizeof(uint32), cmp_uint32); + + i = 0; /* sorted index */ + j = 0; /* unsorted index */ + + while ((i < nsorted) && (j < nunsorted)) + { + if (sorted[i] <= unsorted[j]) + result[k++] = sorted[i++]; + else + result[k++] = unsorted[j++]; + } + + while (i < nsorted) + result[k++] = sorted[i++]; + + while (j < nunsorted) + result[k++] = unsorted[j++]; + + /* compact - remove duplicate values */ + nvalues = 1; + for (i = 1; i < filter->nvalues; i++) + { + /* if different from the last value, keep it */ + if (result[i] != result[nvalues - 1]) + result[nvalues++] = result[i]; + } + + memcpy(filter->data, result, sizeof(uint32) * nvalues); + + filter->nvalues = nvalues; + filter->nsorted = nvalues; + + len = offsetof(BloomFilter, data) + + (filter->nvalues) * sizeof(uint32); + + SET_VARSIZE(filter, len); +} + +/* + * bloom_add_value + * Add value to the bloom filter. + */ +static BloomFilter * +bloom_add_value(BloomFilter *filter, uint32 value, bool *updated) +{ + int i; + uint32 h1, h2; + + /* assume 'not updated' by default */ + Assert(filter); + + /* if we're in the sorted phase, we store the hashes directly */ + if (BLOOM_IS_SORTED(filter)) + { + /* how many uint32 hashes can we fit into the bitmap */ + int maxvalues = filter->nbits / (8 * sizeof(uint32)); + + /* do not overflow the bitmap space or number of unsorted items */ + Assert(filter->nvalues <= maxvalues); + Assert(filter->nvalues - filter->nsorted <= BLOOM_MAX_UNSORTED); + + /* + * In this branch we always update the filter - we either add the + * hash to the unsorted part, or switch the filter to hashing. + */ + if (updated) + *updated = true; + + /* + * If the array is full, or if we reached the limit on unsorted + * items, try to compact the filter first, before attempting to + * add the new value. + */ + if ((filter->nvalues == maxvalues) || + (filter->nvalues - filter->nsorted == BLOOM_MAX_UNSORTED)) + bloom_compact(filter); + + /* + * Can we squeeze one more uint32 hash into the bitmap? Also make + * sure there's enough space in the bytea value first. + */ + if (filter->nvalues < maxvalues) + { + Size len = VARSIZE_ANY(filter); + Size need = offsetof(BloomFilter, data) + + (filter->nvalues + 1) * sizeof(uint32); + + /* + * We don't double the size here, as in the first place we care about + * reducing storage requirements, and the doubling happens automatically + * in memory contexts (so the repalloc should be cheap in most cases). + */ + if (len < need) + { + filter = (BloomFilter *) repalloc(filter, need); + SET_VARSIZE(filter, need); + } + + /* copy the new value into the filter */ + memcpy(&filter->data[filter->nvalues * sizeof(uint32)], + &value, sizeof(uint32)); + + filter->nvalues++; + + /* we're done */ + return filter; + } + + /* can't add any more exact hashes, so switch to hashing */ + filter = bloom_switch_to_hashing(filter); + } + + /* we better be in the hashing phase */ + Assert(BLOOM_IS_HASHED(filter)); + + /* compute the hashes, used for the bloom filter */ + h1 = hash_uint32_extended(value, 0x71d924af) % filter->nbits; + h2 = hash_uint32_extended(value, 0xba48b314) % filter->nbits; + + /* compute the requested number of hashes */ + for (i = 0; i < filter->nhashes; i++) + { + /* h1 + h2 + f(i) */ + uint32 h = (h1 + i * h2) % filter->nbits; + uint32 byte = (h / 8); + uint32 bit = (h % 8); + + /* if the bit is not set, set it and remember we did that */ + if (! (filter->data[byte] & (0x01 << bit))) + { + filter->data[byte] |= (0x01 << bit); + filter->nbits_set++; + if (updated) + *updated = true; + } + } + + return filter; +} + +/* + * bloom_switch_to_hashing + * Switch the bloom filter from sorted to hashing mode. + */ +static BloomFilter * +bloom_switch_to_hashing(BloomFilter *filter) +{ + int i; + uint32 *values; + Size len; + BloomFilter *newfilter; + + Assert(filter->nbits % 8 == 0); + + /* + * The new filter is allocated with all the memory, directly into + * the HASH phase. + */ + len = offsetof(BloomFilter, data) + (filter->nbits / 8); + + newfilter = (BloomFilter *) palloc0(len); + + newfilter->nhashes = filter->nhashes; + newfilter->nbits = filter->nbits; + newfilter->flags |= BLOOM_FLAG_PHASE_HASH; + + SET_VARSIZE(newfilter, len); + + values = (uint32 *) filter->data; + + for (i = 0; i < filter->nvalues; i++) + /* ignore the return value here, re don't repalloc in hashing mode */ + bloom_add_value(newfilter, values[i], NULL); + + /* free the original filter, return the newly allocated one */ + pfree(filter); + + return newfilter; +} + +/* + * bloom_contains_value + * Check if the bloom filter contains a particular value. + */ +static bool +bloom_contains_value(BloomFilter *filter, uint32 value) +{ + int i; + uint32 h1, h2; + + Assert(filter); + + /* in sorted mode we simply search the two arrays (sorted, unsorted) */ + if (BLOOM_IS_SORTED(filter)) + { + int i; + uint32 *values = (uint32 *) filter->data; + + /* first search through the sorted part */ + if ((filter->nsorted > 0) && + (bsearch(&value, values, filter->nsorted, sizeof(uint32), cmp_uint32) != NULL)) + return true; + + /* now search through the unsorted part - linear search */ + for (i = filter->nsorted; i < filter->nvalues; i++) + { + if (value == values[i]) + return true; + } + + /* nothing found */ + return false; + } + + /* now the regular hashing mode */ + Assert(BLOOM_IS_HASHED(filter)); + + /* calculate the two hashes */ + h1 = hash_uint32_extended(value, 0x71d924af) % filter->nbits; + h2 = hash_uint32_extended(value, 0xba48b314) % filter->nbits; + + /* compute the requested number of hashes */ + for (i = 0; i < filter->nhashes; i++) + { + /* h1 + h2 + f(i) */ + uint32 h = (h1 + i * h2) % filter->nbits; + uint32 byte = (h / 8); + uint32 bit = (h % 8); + + /* if the bit is not set, the value is not there */ + if (! (filter->data[byte] & (0x01 << bit))) + return false; + } + + /* all hashes found in bloom filter */ + return true; +} + +typedef struct BloomOpaque +{ + /* + * XXX At this point we only need a single proc (to compute the hash), + * but let's keep the array just like inclusion and minman opclasses, + * for consistency. We may need additional procs in the future. + */ + FmgrInfo extra_procinfos[BLOOM_MAX_PROCNUMS]; + bool extra_proc_missing[BLOOM_MAX_PROCNUMS]; +} BloomOpaque; + +static FmgrInfo *bloom_get_procinfo(BrinDesc *bdesc, uint16 attno, + uint16 procnum); + + +Datum +brin_bloom_opcinfo(PG_FUNCTION_ARGS) +{ + BrinOpcInfo *result; + + /* + * opaque->strategy_procinfos is initialized lazily; here it is set to + * all-uninitialized by palloc0 which sets fn_oid to InvalidOid. + * + * bloom indexes only store the filter as a single BYTEA column + */ + + result = palloc0(MAXALIGN(SizeofBrinOpcInfo(1)) + + sizeof(BloomOpaque)); + result->oi_nstored = 1; + result->oi_regular_nulls = true; + result->oi_opaque = (BloomOpaque *) + MAXALIGN((char *) result + SizeofBrinOpcInfo(1)); + result->oi_typcache[0] = lookup_type_cache(PG_BRIN_BLOOM_SUMMARYOID, 0); + + PG_RETURN_POINTER(result); +} + +/* + * brin_bloom_get_ndistinct + * Determine the ndistinct value used to size bloom filter. + * + * Tweak the ndistinct value based on the pagesPerRange value. First, + * if it's negative, it's assumed to be relative to maximum number of + * tuples in the range (assuming each page gets MaxHeapTuplesPerPage + * tuples, which is likely a significant over-estimate). We also clamp + * the value, not to over-size the bloom filter unnecessarily. + * + * XXX We can only do this when the pagesPerRange value was supplied. + * If it wasn't, it has to be a read-only access to the index, in which + * case we don't really care. But perhaps we should fall-back to the + * default pagesPerRange value? + * + * XXX We might also fetch info about ndistinct estimate for the column, + * and compute the expected number of distinct values in a range. But + * that may be tricky due to data being sorted in various ways, so it + * seems better to rely on the upper estimate. + */ +static int +brin_bloom_get_ndistinct(BrinDesc *bdesc, BloomOptions *opts) +{ + double ndistinct; + double maxtuples; + BlockNumber pagesPerRange; + + pagesPerRange = BrinGetPagesPerRange(bdesc->bd_index); + ndistinct = BloomGetNDistinctPerRange(opts); + + Assert(BlockNumberIsValid(pagesPerRange)); + + maxtuples = MaxHeapTuplesPerPage * pagesPerRange; + + /* + * Similarly to n_distinct, negative values are relative - in this + * case to maximum number of tuples in the page range (maxtuples). + */ + if (ndistinct < 0) + ndistinct = (-ndistinct) * maxtuples; + + /* + * Positive values are to be used directly, but we still apply a + * couple of safeties no to use unreasonably small bloom filters. + */ + ndistinct = Max(ndistinct, BLOOM_MIN_NDISTINCT_PER_RANGE); + + /* + * And don't use more than the maximum possible number of tuples, + * in the range, which would be entirely wasteful. + */ + ndistinct = Min(ndistinct, maxtuples); + + return (int) ndistinct; +} + +static double +brin_bloom_get_fp_rate(BrinDesc *bdesc, BloomOptions *opts) +{ + return BloomGetFalsePositiveRate(opts); +} + +/* + * Examine the given index tuple (which contains partial status of a certain + * page range) by comparing it to the given value that comes from another heap + * tuple. If the new value is outside the bloom filter specified by the + * existing tuple values, update the index tuple and return true. Otherwise, + * return false and do not modify in this case. + */ +Datum +brin_bloom_add_value(PG_FUNCTION_ARGS) +{ + BrinDesc *bdesc = (BrinDesc *) PG_GETARG_POINTER(0); + BrinValues *column = (BrinValues *) PG_GETARG_POINTER(1); + Datum newval = PG_GETARG_DATUM(2); + bool isnull PG_USED_FOR_ASSERTS_ONLY = PG_GETARG_DATUM(3); + BloomOptions *opts = (BloomOptions *) PG_GET_OPCLASS_OPTIONS(); + Oid colloid = PG_GET_COLLATION(); + FmgrInfo *hashFn; + uint32 hashValue; + bool updated = false; + AttrNumber attno; + BloomFilter *filter; + + Assert(!isnull); + + attno = column->bv_attno; + + /* + * If this is the first non-null value, we need to initialize the bloom + * filter. Otherwise just extract the existing bloom filter from BrinValues. + */ + if (column->bv_allnulls) + { + filter = bloom_init(brin_bloom_get_ndistinct(bdesc, opts), + brin_bloom_get_fp_rate(bdesc, opts)); + column->bv_values[0] = PointerGetDatum(filter); + column->bv_allnulls = false; + updated = true; + } + else + filter = (BloomFilter *) PG_DETOAST_DATUM(column->bv_values[0]); + + /* + * Compute the hash of the new value, using the supplied hash function, + * and then add the hash value to the bloom filter. + */ + hashFn = bloom_get_procinfo(bdesc, attno, PROCNUM_HASH); + + hashValue = DatumGetUInt32(FunctionCall1Coll(hashFn, colloid, newval)); + + filter = bloom_add_value(filter, hashValue, &updated); + + column->bv_values[0] = PointerGetDatum(filter); + + PG_RETURN_BOOL(updated); +} + +/* + * Given an index tuple corresponding to a certain page range and a scan key, + * return whether the scan key is consistent with the index tuple's bloom + * filter. Return true if so, false otherwise. + */ +Datum +brin_bloom_consistent(PG_FUNCTION_ARGS) +{ + BrinDesc *bdesc = (BrinDesc *) PG_GETARG_POINTER(0); + BrinValues *column = (BrinValues *) PG_GETARG_POINTER(1); + ScanKey *keys = (ScanKey *) PG_GETARG_POINTER(2); + int nkeys = PG_GETARG_INT32(3); + Oid colloid = PG_GET_COLLATION(); + AttrNumber attno; + Datum value; + Datum matches; + FmgrInfo *finfo; + uint32 hashValue; + BloomFilter *filter; + int keyno; + + filter = (BloomFilter *) PG_DETOAST_DATUM(column->bv_values[0]); + + Assert(filter); + + matches = true; + + for (keyno = 0; keyno < nkeys; keyno++) + { + ScanKey key = keys[keyno]; + + /* NULL keys are handled and filtered-out in bringetbitmap */ + Assert(!(key->sk_flags & SK_ISNULL)); + + attno = key->sk_attno; + value = key->sk_argument; + + switch (key->sk_strategy) + { + case BloomEqualStrategyNumber: + + /* + * In the equality case (WHERE col = someval), we want to return + * the current page range if the minimum value in the range <= + * scan key, and the maximum value >= scan key. + */ + finfo = bloom_get_procinfo(bdesc, attno, PROCNUM_HASH); + + hashValue = DatumGetUInt32(FunctionCall1Coll(finfo, colloid, value)); + matches &= bloom_contains_value(filter, hashValue); + + break; + default: + /* shouldn't happen */ + elog(ERROR, "invalid strategy number %d", key->sk_strategy); + matches = 0; + break; + } + + if (!matches) + break; + } + + PG_RETURN_DATUM(matches); +} + +/* + * Given two BrinValues, update the first of them as a union of the summary + * values contained in both. The second one is untouched. + * + * XXX We assume the bloom filters have the same parameters fow now. In the + * future we should have 'can union' function, to decide if we can combine + * two particular bloom filters. + */ +Datum +brin_bloom_union(PG_FUNCTION_ARGS) +{ + BrinValues *col_a = (BrinValues *) PG_GETARG_POINTER(1); + BrinValues *col_b = (BrinValues *) PG_GETARG_POINTER(2); + BloomFilter *filter_a; + BloomFilter *filter_b; + + Assert(col_a->bv_attno == col_b->bv_attno); + Assert(!col_a->bv_allnulls && !col_b->bv_allnulls); + + filter_a = (BloomFilter *) PG_DETOAST_DATUM(col_a->bv_values[0]); + filter_b = (BloomFilter *) PG_DETOAST_DATUM(col_b->bv_values[0]); + + /* make sure neither of the bloom filters is NULL */ + Assert(filter_a && filter_b); + Assert(filter_a->nbits == filter_b->nbits); + + /* + * Merging of the filters depends on the phase of both filters. + */ + if (BLOOM_IS_SORTED(filter_b)) + { + /* + * Simply read all items from 'b' and add them to 'a' (the phase of + * 'a' does not really matter). + */ + int i; + uint32 *values = (uint32 *) filter_b->data; + + for (i = 0; i < filter_b->nvalues; i++) + filter_a = bloom_add_value(filter_a, values[i], NULL); + + col_a->bv_values[0] = PointerGetDatum(filter_a); + } + else if (BLOOM_IS_SORTED(filter_a)) + { + /* + * 'b' hashed, 'a' sorted - copy 'b' into 'a' and then add all values + * from 'a' into the new copy. + */ + int i; + BloomFilter *filter_c; + uint32 *values = (uint32 *) filter_a->data; + + filter_c = (BloomFilter *) PG_DETOAST_DATUM(datumCopy(PointerGetDatum(filter_b), false, -1)); + + for (i = 0; i < filter_a->nvalues; i++) + filter_c = bloom_add_value(filter_c, values[i], NULL); + + col_a->bv_values[0] = PointerGetDatum(filter_c); + } + else if (BLOOM_IS_HASHED(filter_a)) + { + /* + * 'b' hashed, 'a' hashed - merge the bitmaps by OR + */ + int i; + int nbytes = (filter_a->nbits + 7) / 8; + + /* we better have "compatible" bloom filters */ + Assert(filter_a->nbits == filter_b->nbits); + Assert(filter_a->nhashes == filter_b->nhashes); + + for (i = 0; i < nbytes; i++) + filter_a->data[i] |= filter_b->data[i]; + } + + PG_RETURN_VOID(); +} + +/* + * Cache and return inclusion opclass support procedure + * + * Return the procedure corresponding to the given function support number + * or null if it is not exists. + */ +static FmgrInfo * +bloom_get_procinfo(BrinDesc *bdesc, uint16 attno, uint16 procnum) +{ + BloomOpaque *opaque; + uint16 basenum = procnum - PROCNUM_BASE; + + /* + * We cache these in the opaque struct, to avoid repetitive syscache + * lookups. + */ + opaque = (BloomOpaque *) bdesc->bd_info[attno - 1]->oi_opaque; + + /* + * If we already searched for this proc and didn't find it, don't bother + * searching again. + */ + if (opaque->extra_proc_missing[basenum]) + return NULL; + + if (opaque->extra_procinfos[basenum].fn_oid == InvalidOid) + { + if (RegProcedureIsValid(index_getprocid(bdesc->bd_index, attno, + procnum))) + { + fmgr_info_copy(&opaque->extra_procinfos[basenum], + index_getprocinfo(bdesc->bd_index, attno, procnum), + bdesc->bd_context); + } + else + { + opaque->extra_proc_missing[basenum] = true; + return NULL; + } + } + + return &opaque->extra_procinfos[basenum]; +} + +Datum +brin_bloom_options(PG_FUNCTION_ARGS) +{ + local_relopts *relopts = (local_relopts *) PG_GETARG_POINTER(0); + + init_local_reloptions(relopts, sizeof(BloomOptions)); + + add_local_real_reloption(relopts, "n_distinct_per_range", + "number of distinct items expected in a BRIN page range", + BLOOM_DEFAULT_NDISTINCT_PER_RANGE, + -1.0, INT_MAX, offsetof(BloomOptions, nDistinctPerRange)); + + add_local_real_reloption(relopts, "false_positive_rate", + "desired false-positive rate for the bloom filters", + BLOOM_DEFAULT_FALSE_POSITIVE_RATE, + BLOOM_MIN_FALSE_POSITIVE_RATE, + BLOOM_MAX_FALSE_POSITIVE_RATE, + offsetof(BloomOptions, falsePositiveRate)); + + PG_RETURN_VOID(); +} + +/* + * brin_bloom_summary_in + * - input routine for type brin_bloom_summary. + * + * brin_bloom_summary is only used internally to represent summaries + * in BRIN bloom indexes, so it has no operations of its own, and we + * disallow input too. + */ +Datum +brin_bloom_summary_in(PG_FUNCTION_ARGS) +{ + /* + * brin_bloom_summary stores the data in binary form and parsing + * text input is not needed, so disallow this. + */ + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot accept a value of type %s", "pg_brin_bloom_summary"))); + + PG_RETURN_VOID(); /* keep compiler quiet */ +} + + +/* + * brin_bloom_summary_out + * - output routine for type brin_bloom_summary. + * + * BRIN bloom summaries are serialized into a bytea value, but we want + * to output something nicer humans can understand. + */ +Datum +brin_bloom_summary_out(PG_FUNCTION_ARGS) +{ + BloomFilter *filter; + StringInfoData str; + + initStringInfo(&str); + appendStringInfoChar(&str, '{'); + + /* + * XXX not sure the detoasting is necessary (probably not, this + * can only be in an index). + */ + filter = (BloomFilter *) PG_DETOAST_DATUM(PG_GETARG_BYTEA_PP(0)); + + if (BLOOM_IS_HASHED(filter)) + { + appendStringInfo(&str, "mode: hashed nhashes: %u nbits: %u nbits_set: %u", + filter->nhashes, filter->nbits, filter->nbits_set); + } + else + { + appendStringInfo(&str, "mode: sorted nvalues: %u nsorted: %u", + filter->nvalues, filter->nsorted); + /* TODO include the sorted/unsorted values */ + } + + appendStringInfoChar(&str, '}'); + + PG_RETURN_CSTRING(str.data); +} + +/* + * brin_bloom_summary_recv + * - binary input routine for type brin_bloom_summary. + */ +Datum +brin_bloom_summary_recv(PG_FUNCTION_ARGS) +{ + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot accept a value of type %s", "pg_brin_bloom_summary"))); + + PG_RETURN_VOID(); /* keep compiler quiet */ +} + +/* + * brin_bloom_summary_send + * - binary output routine for type brin_bloom_summary. + * + * BRIN bloom summaries are serialized in a bytea value (although the + * type is named differently), so let's just send that. + */ +Datum +brin_bloom_summary_send(PG_FUNCTION_ARGS) +{ + return byteasend(fcinfo); +} diff --git a/src/include/access/brin.h b/src/include/access/brin.h index 4e2be13cd6..0e52d75457 100644 --- a/src/include/access/brin.h +++ b/src/include/access/brin.h @@ -22,6 +22,8 @@ typedef struct BrinOptions int32 vl_len_; /* varlena header (do not touch directly!) */ BlockNumber pagesPerRange; bool autosummarize; + double nDistinctPerRange; /* number of distinct values per range */ + double falsePositiveRate; /* false positive for bloom filter */ } BrinOptions; diff --git a/src/include/access/brin_internal.h b/src/include/access/brin_internal.h index 7b79a52536..d114ac58cd 100644 --- a/src/include/access/brin_internal.h +++ b/src/include/access/brin_internal.h @@ -58,6 +58,10 @@ typedef struct BrinDesc /* total number of Datum entries that are stored on-disk for all columns */ int bd_totalstored; + /* parameters for sizing bloom filter (BRIN bloom opclasses) */ + double bd_nDistinctPerRange; + double bd_falsePositiveRange; + /* per-column info; bd_tupdesc->natts entries long */ BrinOpcInfo *bd_info[FLEXIBLE_ARRAY_MEMBER]; } BrinDesc; diff --git a/src/include/catalog/pg_amop.dat b/src/include/catalog/pg_amop.dat index 9339971e77..c48d07f8fc 100644 --- a/src/include/catalog/pg_amop.dat +++ b/src/include/catalog/pg_amop.dat @@ -1814,6 +1814,11 @@ amoprighttype => 'bytea', amopstrategy => '5', amopopr => '>(bytea,bytea)', amopmethod => 'brin' }, +# bloom bytea +{ amopfamily => 'brin/bytea_bloom_ops', amoplefttype => 'bytea', + amoprighttype => 'bytea', amopstrategy => '1', amopopr => '=(bytea,bytea)', + amopmethod => 'brin' }, + # minmax "char" { amopfamily => 'brin/char_minmax_ops', amoplefttype => 'char', amoprighttype => 'char', amopstrategy => '1', amopopr => '<(char,char)', @@ -1831,6 +1836,11 @@ amoprighttype => 'char', amopstrategy => '5', amopopr => '>(char,char)', amopmethod => 'brin' }, +# bloom "char" +{ amopfamily => 'brin/char_bloom_ops', amoplefttype => 'char', + amoprighttype => 'char', amopstrategy => '1', amopopr => '=(char,char)', + amopmethod => 'brin' }, + # minmax name { amopfamily => 'brin/name_minmax_ops', amoplefttype => 'name', amoprighttype => 'name', amopstrategy => '1', amopopr => '<(name,name)', @@ -1848,6 +1858,11 @@ amoprighttype => 'name', amopstrategy => '5', amopopr => '>(name,name)', amopmethod => 'brin' }, +# bloom name +{ amopfamily => 'brin/name_bloom_ops', amoplefttype => 'name', + amoprighttype => 'name', amopstrategy => '1', amopopr => '=(name,name)', + amopmethod => 'brin' }, + # minmax integer { amopfamily => 'brin/integer_minmax_ops', amoplefttype => 'int8', @@ -1994,6 +2009,44 @@ amoprighttype => 'int8', amopstrategy => '5', amopopr => '>(int4,int8)', amopmethod => 'brin' }, +# bloom integer + +{ amopfamily => 'brin/integer_bloom_ops', amoplefttype => 'int8', + amoprighttype => 'int8', amopstrategy => '1', amopopr => '=(int8,int8)', + amopmethod => 'brin' }, + +{ amopfamily => 'brin/integer_bloom_ops', amoplefttype => 'int8', + amoprighttype => 'int2', amopstrategy => '1', amopopr => '=(int8,int2)', + amopmethod => 'brin' }, + +{ amopfamily => 'brin/integer_bloom_ops', amoplefttype => 'int8', + amoprighttype => 'int4', amopstrategy => '1', amopopr => '=(int8,int4)', + amopmethod => 'brin' }, + +{ amopfamily => 'brin/integer_bloom_ops', amoplefttype => 'int2', + amoprighttype => 'int2', amopstrategy => '1', amopopr => '=(int2,int2)', + amopmethod => 'brin' }, + +{ amopfamily => 'brin/integer_bloom_ops', amoplefttype => 'int2', + amoprighttype => 'int8', amopstrategy => '1', amopopr => '=(int2,int8)', + amopmethod => 'brin' }, + +{ amopfamily => 'brin/integer_bloom_ops', amoplefttype => 'int2', + amoprighttype => 'int4', amopstrategy => '1', amopopr => '=(int2,int4)', + amopmethod => 'brin' }, + +{ amopfamily => 'brin/integer_bloom_ops', amoplefttype => 'int4', + amoprighttype => 'int4', amopstrategy => '1', amopopr => '=(int4,int4)', + amopmethod => 'brin' }, + +{ amopfamily => 'brin/integer_bloom_ops', amoplefttype => 'int4', + amoprighttype => 'int2', amopstrategy => '1', amopopr => '=(int4,int2)', + amopmethod => 'brin' }, + +{ amopfamily => 'brin/integer_bloom_ops', amoplefttype => 'int4', + amoprighttype => 'int8', amopstrategy => '1', amopopr => '=(int4,int8)', + amopmethod => 'brin' }, + # minmax text { amopfamily => 'brin/text_minmax_ops', amoplefttype => 'text', amoprighttype => 'text', amopstrategy => '1', amopopr => '<(text,text)', @@ -2011,6 +2064,11 @@ amoprighttype => 'text', amopstrategy => '5', amopopr => '>(text,text)', amopmethod => 'brin' }, +# bloom text +{ amopfamily => 'brin/text_bloom_ops', amoplefttype => 'text', + amoprighttype => 'text', amopstrategy => '1', amopopr => '=(text,text)', + amopmethod => 'brin' }, + # minmax oid { amopfamily => 'brin/oid_minmax_ops', amoplefttype => 'oid', amoprighttype => 'oid', amopstrategy => '1', amopopr => '<(oid,oid)', @@ -2028,6 +2086,11 @@ amoprighttype => 'oid', amopstrategy => '5', amopopr => '>(oid,oid)', amopmethod => 'brin' }, +# bloom oid +{ amopfamily => 'brin/oid_bloom_ops', amoplefttype => 'oid', + amoprighttype => 'oid', amopstrategy => '1', amopopr => '=(oid,oid)', + amopmethod => 'brin' }, + # minmax tid { amopfamily => 'brin/tid_minmax_ops', amoplefttype => 'tid', amoprighttype => 'tid', amopstrategy => '1', amopopr => '<(tid,tid)', @@ -2045,6 +2108,11 @@ amoprighttype => 'tid', amopstrategy => '5', amopopr => '>(tid,tid)', amopmethod => 'brin' }, +# tid oid +{ amopfamily => 'brin/tid_bloom_ops', amoplefttype => 'tid', + amoprighttype => 'tid', amopstrategy => '1', amopopr => '=(tid,tid)', + amopmethod => 'brin' }, + # minmax float (float4, float8) { amopfamily => 'brin/float_minmax_ops', amoplefttype => 'float4', @@ -2111,6 +2179,20 @@ amoprighttype => 'float8', amopstrategy => '5', amopopr => '>(float8,float8)', amopmethod => 'brin' }, +# bloom float +{ amopfamily => 'brin/float_bloom_ops', amoplefttype => 'float4', + amoprighttype => 'float4', amopstrategy => '1', amopopr => '=(float4,float4)', + amopmethod => 'brin' }, +{ amopfamily => 'brin/float_bloom_ops', amoplefttype => 'float4', + amoprighttype => 'float8', amopstrategy => '1', amopopr => '=(float4,float8)', + amopmethod => 'brin' }, +{ amopfamily => 'brin/float_bloom_ops', amoplefttype => 'float8', + amoprighttype => 'float4', amopstrategy => '1', amopopr => '=(float8,float4)', + amopmethod => 'brin' }, +{ amopfamily => 'brin/float_bloom_ops', amoplefttype => 'float8', + amoprighttype => 'float8', amopstrategy => '1', amopopr => '=(float8,float8)', + amopmethod => 'brin' }, + # minmax macaddr { amopfamily => 'brin/macaddr_minmax_ops', amoplefttype => 'macaddr', amoprighttype => 'macaddr', amopstrategy => '1', @@ -2128,6 +2210,11 @@ amoprighttype => 'macaddr', amopstrategy => '5', amopopr => '>(macaddr,macaddr)', amopmethod => 'brin' }, +# bloom macaddr +{ amopfamily => 'brin/macaddr_bloom_ops', amoplefttype => 'macaddr', + amoprighttype => 'macaddr', amopstrategy => '1', + amopopr => '=(macaddr,macaddr)', amopmethod => 'brin' }, + # minmax macaddr8 { amopfamily => 'brin/macaddr8_minmax_ops', amoplefttype => 'macaddr8', amoprighttype => 'macaddr8', amopstrategy => '1', @@ -2145,6 +2232,11 @@ amoprighttype => 'macaddr8', amopstrategy => '5', amopopr => '>(macaddr8,macaddr8)', amopmethod => 'brin' }, +# bloom macaddr8 +{ amopfamily => 'brin/macaddr8_bloom_ops', amoplefttype => 'macaddr8', + amoprighttype => 'macaddr8', amopstrategy => '1', + amopopr => '=(macaddr8,macaddr8)', amopmethod => 'brin' }, + # minmax inet { amopfamily => 'brin/network_minmax_ops', amoplefttype => 'inet', amoprighttype => 'inet', amopstrategy => '1', amopopr => '<(inet,inet)', @@ -2162,6 +2254,11 @@ amoprighttype => 'inet', amopstrategy => '5', amopopr => '>(inet,inet)', amopmethod => 'brin' }, +# bloom inet +{ amopfamily => 'brin/network_bloom_ops', amoplefttype => 'inet', + amoprighttype => 'inet', amopstrategy => '1', amopopr => '=(inet,inet)', + amopmethod => 'brin' }, + # inclusion inet { amopfamily => 'brin/network_inclusion_ops', amoplefttype => 'inet', amoprighttype => 'inet', amopstrategy => '3', amopopr => '&&(inet,inet)', @@ -2199,6 +2296,11 @@ amoprighttype => 'bpchar', amopstrategy => '5', amopopr => '>(bpchar,bpchar)', amopmethod => 'brin' }, +# bloom character +{ amopfamily => 'brin/bpchar_bloom_ops', amoplefttype => 'bpchar', + amoprighttype => 'bpchar', amopstrategy => '1', amopopr => '=(bpchar,bpchar)', + amopmethod => 'brin' }, + # minmax time without time zone { amopfamily => 'brin/time_minmax_ops', amoplefttype => 'time', amoprighttype => 'time', amopstrategy => '1', amopopr => '<(time,time)', @@ -2216,6 +2318,11 @@ amoprighttype => 'time', amopstrategy => '5', amopopr => '>(time,time)', amopmethod => 'brin' }, +# bloom time without time zone +{ amopfamily => 'brin/time_bloom_ops', amoplefttype => 'time', + amoprighttype => 'time', amopstrategy => '1', amopopr => '=(time,time)', + amopmethod => 'brin' }, + # minmax datetime (date, timestamp, timestamptz) { amopfamily => 'brin/datetime_minmax_ops', amoplefttype => 'timestamp', @@ -2362,6 +2469,44 @@ amoprighttype => 'timestamptz', amopstrategy => '5', amopopr => '>(timestamptz,timestamptz)', amopmethod => 'brin' }, +# bloom datetime (date, timestamp, timestamptz) + +{ amopfamily => 'brin/datetime_bloom_ops', amoplefttype => 'timestamp', + amoprighttype => 'timestamp', amopstrategy => '1', + amopopr => '=(timestamp,timestamp)', amopmethod => 'brin' }, + +{ amopfamily => 'brin/datetime_bloom_ops', amoplefttype => 'timestamp', + amoprighttype => 'date', amopstrategy => '1', amopopr => '=(timestamp,date)', + amopmethod => 'brin' }, + +{ amopfamily => 'brin/datetime_bloom_ops', amoplefttype => 'timestamp', + amoprighttype => 'timestamptz', amopstrategy => '1', + amopopr => '=(timestamp,timestamptz)', amopmethod => 'brin' }, + +{ amopfamily => 'brin/datetime_bloom_ops', amoplefttype => 'date', + amoprighttype => 'date', amopstrategy => '1', amopopr => '=(date,date)', + amopmethod => 'brin' }, + +{ amopfamily => 'brin/datetime_bloom_ops', amoplefttype => 'date', + amoprighttype => 'timestamp', amopstrategy => '1', + amopopr => '=(date,timestamp)', amopmethod => 'brin' }, + +{ amopfamily => 'brin/datetime_bloom_ops', amoplefttype => 'date', + amoprighttype => 'timestamptz', amopstrategy => '1', + amopopr => '=(date,timestamptz)', amopmethod => 'brin' }, + +{ amopfamily => 'brin/datetime_bloom_ops', amoplefttype => 'timestamptz', + amoprighttype => 'date', amopstrategy => '1', + amopopr => '=(timestamptz,date)', amopmethod => 'brin' }, + +{ amopfamily => 'brin/datetime_bloom_ops', amoplefttype => 'timestamptz', + amoprighttype => 'timestamp', amopstrategy => '1', + amopopr => '=(timestamptz,timestamp)', amopmethod => 'brin' }, + +{ amopfamily => 'brin/datetime_bloom_ops', amoplefttype => 'timestamptz', + amoprighttype => 'timestamptz', amopstrategy => '1', + amopopr => '=(timestamptz,timestamptz)', amopmethod => 'brin' }, + # minmax interval { amopfamily => 'brin/interval_minmax_ops', amoplefttype => 'interval', amoprighttype => 'interval', amopstrategy => '1', @@ -2379,6 +2524,11 @@ amoprighttype => 'interval', amopstrategy => '5', amopopr => '>(interval,interval)', amopmethod => 'brin' }, +# bloom interval +{ amopfamily => 'brin/interval_bloom_ops', amoplefttype => 'interval', + amoprighttype => 'interval', amopstrategy => '1', + amopopr => '=(interval,interval)', amopmethod => 'brin' }, + # minmax time with time zone { amopfamily => 'brin/timetz_minmax_ops', amoplefttype => 'timetz', amoprighttype => 'timetz', amopstrategy => '1', amopopr => '<(timetz,timetz)', @@ -2396,6 +2546,11 @@ amoprighttype => 'timetz', amopstrategy => '5', amopopr => '>(timetz,timetz)', amopmethod => 'brin' }, +# bloom time with time zone +{ amopfamily => 'brin/timetz_bloom_ops', amoplefttype => 'timetz', + amoprighttype => 'timetz', amopstrategy => '1', amopopr => '=(timetz,timetz)', + amopmethod => 'brin' }, + # minmax bit { amopfamily => 'brin/bit_minmax_ops', amoplefttype => 'bit', amoprighttype => 'bit', amopstrategy => '1', amopopr => '<(bit,bit)', @@ -2447,6 +2602,11 @@ amoprighttype => 'numeric', amopstrategy => '5', amopopr => '>(numeric,numeric)', amopmethod => 'brin' }, +# bloom numeric +{ amopfamily => 'brin/numeric_bloom_ops', amoplefttype => 'numeric', + amoprighttype => 'numeric', amopstrategy => '1', + amopopr => '=(numeric,numeric)', amopmethod => 'brin' }, + # minmax uuid { amopfamily => 'brin/uuid_minmax_ops', amoplefttype => 'uuid', amoprighttype => 'uuid', amopstrategy => '1', amopopr => '<(uuid,uuid)', @@ -2464,6 +2624,11 @@ amoprighttype => 'uuid', amopstrategy => '5', amopopr => '>(uuid,uuid)', amopmethod => 'brin' }, +# bloom uuid +{ amopfamily => 'brin/uuid_bloom_ops', amoplefttype => 'uuid', + amoprighttype => 'uuid', amopstrategy => '1', amopopr => '=(uuid,uuid)', + amopmethod => 'brin' }, + # inclusion range types { amopfamily => 'brin/range_inclusion_ops', amoplefttype => 'anyrange', amoprighttype => 'anyrange', amopstrategy => '1', @@ -2525,6 +2690,11 @@ amoprighttype => 'pg_lsn', amopstrategy => '5', amopopr => '>(pg_lsn,pg_lsn)', amopmethod => 'brin' }, +# bloom pg_lsn +{ amopfamily => 'brin/pg_lsn_bloom_ops', amoplefttype => 'pg_lsn', + amoprighttype => 'pg_lsn', amopstrategy => '1', amopopr => '=(pg_lsn,pg_lsn)', + amopmethod => 'brin' }, + # inclusion box { amopfamily => 'brin/box_inclusion_ops', amoplefttype => 'box', amoprighttype => 'box', amopstrategy => '1', amopopr => '<<(box,box)', diff --git a/src/include/catalog/pg_amproc.dat b/src/include/catalog/pg_amproc.dat index 9b90f4d806..d9308c1337 100644 --- a/src/include/catalog/pg_amproc.dat +++ b/src/include/catalog/pg_amproc.dat @@ -801,6 +801,24 @@ { amprocfamily => 'brin/bytea_minmax_ops', amproclefttype => 'bytea', amprocrighttype => 'bytea', amprocnum => '4', amproc => 'brin_minmax_union' }, +# bloom bytea +{ amprocfamily => 'brin/bytea_bloom_ops', amproclefttype => 'bytea', + amprocrighttype => 'bytea', amprocnum => '1', + amproc => 'brin_bloom_opcinfo' }, +{ amprocfamily => 'brin/bytea_bloom_ops', amproclefttype => 'bytea', + amprocrighttype => 'bytea', amprocnum => '2', + amproc => 'brin_bloom_add_value' }, +{ amprocfamily => 'brin/bytea_bloom_ops', amproclefttype => 'bytea', + amprocrighttype => 'bytea', amprocnum => '3', + amproc => 'brin_bloom_consistent' }, +{ amprocfamily => 'brin/bytea_bloom_ops', amproclefttype => 'bytea', + amprocrighttype => 'bytea', amprocnum => '4', amproc => 'brin_bloom_union' }, +{ amprocfamily => 'brin/bytea_bloom_ops', amproclefttype => 'bytea', + amprocrighttype => 'bytea', amprocnum => '5', + amproc => 'brin_bloom_options' }, +{ amprocfamily => 'brin/bytea_bloom_ops', amproclefttype => 'bytea', + amprocrighttype => 'bytea', amprocnum => '11', amproc => 'hashvarlena' }, + # minmax "char" { amprocfamily => 'brin/char_minmax_ops', amproclefttype => 'char', amprocrighttype => 'char', amprocnum => '1', @@ -814,6 +832,24 @@ { amprocfamily => 'brin/char_minmax_ops', amproclefttype => 'char', amprocrighttype => 'char', amprocnum => '4', amproc => 'brin_minmax_union' }, +# bloom "char" +{ amprocfamily => 'brin/char_bloom_ops', amproclefttype => 'char', + amprocrighttype => 'char', amprocnum => '1', + amproc => 'brin_bloom_opcinfo' }, +{ amprocfamily => 'brin/char_bloom_ops', amproclefttype => 'char', + amprocrighttype => 'char', amprocnum => '2', + amproc => 'brin_bloom_add_value' }, +{ amprocfamily => 'brin/char_bloom_ops', amproclefttype => 'char', + amprocrighttype => 'char', amprocnum => '3', + amproc => 'brin_bloom_consistent' }, +{ amprocfamily => 'brin/char_bloom_ops', amproclefttype => 'char', + amprocrighttype => 'char', amprocnum => '4', amproc => 'brin_bloom_union' }, +{ amprocfamily => 'brin/char_bloom_ops', amproclefttype => 'char', + amprocrighttype => 'char', amprocnum => '5', + amproc => 'brin_bloom_options' }, +{ amprocfamily => 'brin/char_bloom_ops', amproclefttype => 'char', + amprocrighttype => 'char', amprocnum => '11', amproc => 'hashchar' }, + # minmax name { amprocfamily => 'brin/name_minmax_ops', amproclefttype => 'name', amprocrighttype => 'name', amprocnum => '1', @@ -827,6 +863,24 @@ { amprocfamily => 'brin/name_minmax_ops', amproclefttype => 'name', amprocrighttype => 'name', amprocnum => '4', amproc => 'brin_minmax_union' }, +# bloom name +{ amprocfamily => 'brin/name_bloom_ops', amproclefttype => 'name', + amprocrighttype => 'name', amprocnum => '1', + amproc => 'brin_bloom_opcinfo' }, +{ amprocfamily => 'brin/name_bloom_ops', amproclefttype => 'name', + amprocrighttype => 'name', amprocnum => '2', + amproc => 'brin_bloom_add_value' }, +{ amprocfamily => 'brin/name_bloom_ops', amproclefttype => 'name', + amprocrighttype => 'name', amprocnum => '3', + amproc => 'brin_bloom_consistent' }, +{ amprocfamily => 'brin/name_bloom_ops', amproclefttype => 'name', + amprocrighttype => 'name', amprocnum => '4', amproc => 'brin_bloom_union' }, +{ amprocfamily => 'brin/name_bloom_ops', amproclefttype => 'name', + amprocrighttype => 'name', amprocnum => '5', + amproc => 'brin_bloom_options' }, +{ amprocfamily => 'brin/name_bloom_ops', amproclefttype => 'name', + amprocrighttype => 'name', amprocnum => '11', amproc => 'hashname' }, + # minmax integer: int2, int4, int8 { amprocfamily => 'brin/integer_minmax_ops', amproclefttype => 'int8', amprocrighttype => 'int8', amprocnum => '1', @@ -928,6 +982,58 @@ { amprocfamily => 'brin/integer_minmax_ops', amproclefttype => 'int4', amprocrighttype => 'int2', amprocnum => '4', amproc => 'brin_minmax_union' }, +# bloom integer: int2, int4, int8 +{ amprocfamily => 'brin/integer_bloom_ops', amproclefttype => 'int8', + amprocrighttype => 'int8', amprocnum => '1', + amproc => 'brin_bloom_opcinfo' }, +{ amprocfamily => 'brin/integer_bloom_ops', amproclefttype => 'int8', + amprocrighttype => 'int8', amprocnum => '2', + amproc => 'brin_bloom_add_value' }, +{ amprocfamily => 'brin/integer_bloom_ops', amproclefttype => 'int8', + amprocrighttype => 'int8', amprocnum => '3', + amproc => 'brin_bloom_consistent' }, +{ amprocfamily => 'brin/integer_bloom_ops', amproclefttype => 'int8', + amprocrighttype => 'int8', amprocnum => '4', amproc => 'brin_bloom_union' }, +{ amprocfamily => 'brin/integer_bloom_ops', amproclefttype => 'int8', + amprocrighttype => 'int8', amprocnum => '5', + amproc => 'brin_bloom_options' }, +{ amprocfamily => 'brin/integer_bloom_ops', amproclefttype => 'int8', + amprocrighttype => 'int8', amprocnum => '11', amproc => 'hashint8' }, + +{ amprocfamily => 'brin/integer_bloom_ops', amproclefttype => 'int2', + amprocrighttype => 'int2', amprocnum => '1', + amproc => 'brin_bloom_opcinfo' }, +{ amprocfamily => 'brin/integer_bloom_ops', amproclefttype => 'int2', + amprocrighttype => 'int2', amprocnum => '2', + amproc => 'brin_bloom_add_value' }, +{ amprocfamily => 'brin/integer_bloom_ops', amproclefttype => 'int2', + amprocrighttype => 'int2', amprocnum => '3', + amproc => 'brin_bloom_consistent' }, +{ amprocfamily => 'brin/integer_bloom_ops', amproclefttype => 'int2', + amprocrighttype => 'int2', amprocnum => '4', amproc => 'brin_bloom_union' }, +{ amprocfamily => 'brin/integer_bloom_ops', amproclefttype => 'int2', + amprocrighttype => 'int2', amprocnum => '5', + amproc => 'brin_bloom_options' }, +{ amprocfamily => 'brin/integer_bloom_ops', amproclefttype => 'int2', + amprocrighttype => 'int2', amprocnum => '11', amproc => 'hashint2' }, + +{ amprocfamily => 'brin/integer_bloom_ops', amproclefttype => 'int4', + amprocrighttype => 'int4', amprocnum => '1', + amproc => 'brin_bloom_opcinfo' }, +{ amprocfamily => 'brin/integer_bloom_ops', amproclefttype => 'int4', + amprocrighttype => 'int4', amprocnum => '2', + amproc => 'brin_bloom_add_value' }, +{ amprocfamily => 'brin/integer_bloom_ops', amproclefttype => 'int4', + amprocrighttype => 'int4', amprocnum => '3', + amproc => 'brin_bloom_consistent' }, +{ amprocfamily => 'brin/integer_bloom_ops', amproclefttype => 'int4', + amprocrighttype => 'int4', amprocnum => '4', amproc => 'brin_bloom_union' }, +{ amprocfamily => 'brin/integer_bloom_ops', amproclefttype => 'int4', + amprocrighttype => 'int4', amprocnum => '5', + amproc => 'brin_bloom_options' }, +{ amprocfamily => 'brin/integer_bloom_ops', amproclefttype => 'int4', + amprocrighttype => 'int4', amprocnum => '11', amproc => 'hashint4' }, + # minmax text { amprocfamily => 'brin/text_minmax_ops', amproclefttype => 'text', amprocrighttype => 'text', amprocnum => '1', @@ -941,6 +1047,24 @@ { amprocfamily => 'brin/text_minmax_ops', amproclefttype => 'text', amprocrighttype => 'text', amprocnum => '4', amproc => 'brin_minmax_union' }, +# bloom text +{ amprocfamily => 'brin/text_bloom_ops', amproclefttype => 'text', + amprocrighttype => 'text', amprocnum => '1', + amproc => 'brin_bloom_opcinfo' }, +{ amprocfamily => 'brin/text_bloom_ops', amproclefttype => 'text', + amprocrighttype => 'text', amprocnum => '2', + amproc => 'brin_bloom_add_value' }, +{ amprocfamily => 'brin/text_bloom_ops', amproclefttype => 'text', + amprocrighttype => 'text', amprocnum => '3', + amproc => 'brin_bloom_consistent' }, +{ amprocfamily => 'brin/text_bloom_ops', amproclefttype => 'text', + amprocrighttype => 'text', amprocnum => '4', amproc => 'brin_bloom_union' }, +{ amprocfamily => 'brin/text_bloom_ops', amproclefttype => 'text', + amprocrighttype => 'text', amprocnum => '5', + amproc => 'brin_bloom_options' }, +{ amprocfamily => 'brin/text_bloom_ops', amproclefttype => 'text', + amprocrighttype => 'text', amprocnum => '11', amproc => 'hashtext' }, + # minmax oid { amprocfamily => 'brin/oid_minmax_ops', amproclefttype => 'oid', amprocrighttype => 'oid', amprocnum => '1', amproc => 'brin_minmax_opcinfo' }, @@ -953,6 +1077,23 @@ { amprocfamily => 'brin/oid_minmax_ops', amproclefttype => 'oid', amprocrighttype => 'oid', amprocnum => '4', amproc => 'brin_minmax_union' }, +# bloom oid +{ amprocfamily => 'brin/oid_bloom_ops', amproclefttype => 'oid', + amprocrighttype => 'oid', amprocnum => '1', amproc => 'brin_bloom_opcinfo' }, +{ amprocfamily => 'brin/oid_bloom_ops', amproclefttype => 'oid', + amprocrighttype => 'oid', amprocnum => '2', + amproc => 'brin_bloom_add_value' }, +{ amprocfamily => 'brin/oid_bloom_ops', amproclefttype => 'oid', + amprocrighttype => 'oid', amprocnum => '3', + amproc => 'brin_bloom_consistent' }, +{ amprocfamily => 'brin/oid_bloom_ops', amproclefttype => 'oid', + amprocrighttype => 'oid', amprocnum => '4', amproc => 'brin_bloom_union' }, +{ amprocfamily => 'brin/oid_bloom_ops', amproclefttype => 'oid', + amprocrighttype => 'oid', amprocnum => '5', + amproc => 'brin_bloom_options' }, +{ amprocfamily => 'brin/oid_bloom_ops', amproclefttype => 'oid', + amprocrighttype => 'oid', amprocnum => '11', amproc => 'hashoid' }, + # minmax tid { amprocfamily => 'brin/tid_minmax_ops', amproclefttype => 'tid', amprocrighttype => 'tid', amprocnum => '1', amproc => 'brin_minmax_opcinfo' }, @@ -965,6 +1106,23 @@ { amprocfamily => 'brin/tid_minmax_ops', amproclefttype => 'tid', amprocrighttype => 'tid', amprocnum => '4', amproc => 'brin_minmax_union' }, +# bloom tid +{ amprocfamily => 'brin/tid_bloom_ops', amproclefttype => 'tid', + amprocrighttype => 'tid', amprocnum => '1', amproc => 'brin_bloom_opcinfo' }, +{ amprocfamily => 'brin/tid_bloom_ops', amproclefttype => 'tid', + amprocrighttype => 'tid', amprocnum => '2', + amproc => 'brin_bloom_add_value' }, +{ amprocfamily => 'brin/tid_bloom_ops', amproclefttype => 'tid', + amprocrighttype => 'tid', amprocnum => '3', + amproc => 'brin_bloom_consistent' }, +{ amprocfamily => 'brin/tid_bloom_ops', amproclefttype => 'tid', + amprocrighttype => 'tid', amprocnum => '4', amproc => 'brin_bloom_union' }, +{ amprocfamily => 'brin/tid_bloom_ops', amproclefttype => 'tid', + amprocrighttype => 'tid', amprocnum => '5', + amproc => 'brin_bloom_options' }, +{ amprocfamily => 'brin/tid_bloom_ops', amproclefttype => 'tid', + amprocrighttype => 'tid', amprocnum => '11', amproc => 'hashtid' }, + # minmax float { amprocfamily => 'brin/float_minmax_ops', amproclefttype => 'float4', amprocrighttype => 'float4', amprocnum => '1', @@ -1015,6 +1173,45 @@ amprocrighttype => 'float4', amprocnum => '4', amproc => 'brin_minmax_union' }, +# bloom float +{ amprocfamily => 'brin/float_bloom_ops', amproclefttype => 'float4', + amprocrighttype => 'float4', amprocnum => '1', + amproc => 'brin_bloom_opcinfo' }, +{ amprocfamily => 'brin/float_bloom_ops', amproclefttype => 'float4', + amprocrighttype => 'float4', amprocnum => '2', + amproc => 'brin_bloom_add_value' }, +{ amprocfamily => 'brin/float_bloom_ops', amproclefttype => 'float4', + amprocrighttype => 'float4', amprocnum => '3', + amproc => 'brin_bloom_consistent' }, +{ amprocfamily => 'brin/float_bloom_ops', amproclefttype => 'float4', + amprocrighttype => 'float4', amprocnum => '4', + amproc => 'brin_bloom_union' }, +{ amprocfamily => 'brin/float_bloom_ops', amproclefttype => 'float4', + amprocrighttype => 'float4', amprocnum => '5', + amproc => 'brin_bloom_options' }, +{ amprocfamily => 'brin/float_bloom_ops', amproclefttype => 'float4', + amprocrighttype => 'float4', amprocnum => '11', + amproc => 'hashfloat4' }, + +{ amprocfamily => 'brin/float_bloom_ops', amproclefttype => 'float8', + amprocrighttype => 'float8', amprocnum => '1', + amproc => 'brin_bloom_opcinfo' }, +{ amprocfamily => 'brin/float_bloom_ops', amproclefttype => 'float8', + amprocrighttype => 'float8', amprocnum => '2', + amproc => 'brin_bloom_add_value' }, +{ amprocfamily => 'brin/float_bloom_ops', amproclefttype => 'float8', + amprocrighttype => 'float8', amprocnum => '3', + amproc => 'brin_bloom_consistent' }, +{ amprocfamily => 'brin/float_bloom_ops', amproclefttype => 'float8', + amprocrighttype => 'float8', amprocnum => '4', + amproc => 'brin_bloom_union' }, +{ amprocfamily => 'brin/float_bloom_ops', amproclefttype => 'float8', + amprocrighttype => 'float8', amprocnum => '5', + amproc => 'brin_bloom_options' }, +{ amprocfamily => 'brin/float_bloom_ops', amproclefttype => 'float8', + amprocrighttype => 'float8', amprocnum => '11', + amproc => 'hashfloat8' }, + # minmax macaddr { amprocfamily => 'brin/macaddr_minmax_ops', amproclefttype => 'macaddr', amprocrighttype => 'macaddr', amprocnum => '1', @@ -1029,6 +1226,26 @@ amprocrighttype => 'macaddr', amprocnum => '4', amproc => 'brin_minmax_union' }, +# bloom macaddr +{ amprocfamily => 'brin/macaddr_bloom_ops', amproclefttype => 'macaddr', + amprocrighttype => 'macaddr', amprocnum => '1', + amproc => 'brin_bloom_opcinfo' }, +{ amprocfamily => 'brin/macaddr_bloom_ops', amproclefttype => 'macaddr', + amprocrighttype => 'macaddr', amprocnum => '2', + amproc => 'brin_bloom_add_value' }, +{ amprocfamily => 'brin/macaddr_bloom_ops', amproclefttype => 'macaddr', + amprocrighttype => 'macaddr', amprocnum => '3', + amproc => 'brin_bloom_consistent' }, +{ amprocfamily => 'brin/macaddr_bloom_ops', amproclefttype => 'macaddr', + amprocrighttype => 'macaddr', amprocnum => '4', + amproc => 'brin_bloom_union' }, +{ amprocfamily => 'brin/macaddr_bloom_ops', amproclefttype => 'macaddr', + amprocrighttype => 'macaddr', amprocnum => '5', + amproc => 'brin_bloom_options' }, +{ amprocfamily => 'brin/macaddr_bloom_ops', amproclefttype => 'macaddr', + amprocrighttype => 'macaddr', amprocnum => '11', + amproc => 'hashmacaddr' }, + # minmax macaddr8 { amprocfamily => 'brin/macaddr8_minmax_ops', amproclefttype => 'macaddr8', amprocrighttype => 'macaddr8', amprocnum => '1', @@ -1043,6 +1260,26 @@ amprocrighttype => 'macaddr8', amprocnum => '4', amproc => 'brin_minmax_union' }, +# bloom macaddr8 +{ amprocfamily => 'brin/macaddr8_bloom_ops', amproclefttype => 'macaddr8', + amprocrighttype => 'macaddr8', amprocnum => '1', + amproc => 'brin_bloom_opcinfo' }, +{ amprocfamily => 'brin/macaddr8_bloom_ops', amproclefttype => 'macaddr8', + amprocrighttype => 'macaddr8', amprocnum => '2', + amproc => 'brin_bloom_add_value' }, +{ amprocfamily => 'brin/macaddr8_bloom_ops', amproclefttype => 'macaddr8', + amprocrighttype => 'macaddr8', amprocnum => '3', + amproc => 'brin_bloom_consistent' }, +{ amprocfamily => 'brin/macaddr8_bloom_ops', amproclefttype => 'macaddr8', + amprocrighttype => 'macaddr8', amprocnum => '4', + amproc => 'brin_bloom_union' }, +{ amprocfamily => 'brin/macaddr8_bloom_ops', amproclefttype => 'macaddr8', + amprocrighttype => 'macaddr8', amprocnum => '5', + amproc => 'brin_bloom_options' }, +{ amprocfamily => 'brin/macaddr8_bloom_ops', amproclefttype => 'macaddr8', + amprocrighttype => 'macaddr8', amprocnum => '11', + amproc => 'hashmacaddr8' }, + # minmax inet { amprocfamily => 'brin/network_minmax_ops', amproclefttype => 'inet', amprocrighttype => 'inet', amprocnum => '1', @@ -1056,6 +1293,24 @@ { amprocfamily => 'brin/network_minmax_ops', amproclefttype => 'inet', amprocrighttype => 'inet', amprocnum => '4', amproc => 'brin_minmax_union' }, +# bloom inet +{ amprocfamily => 'brin/network_bloom_ops', amproclefttype => 'inet', + amprocrighttype => 'inet', amprocnum => '1', + amproc => 'brin_bloom_opcinfo' }, +{ amprocfamily => 'brin/network_bloom_ops', amproclefttype => 'inet', + amprocrighttype => 'inet', amprocnum => '2', + amproc => 'brin_bloom_add_value' }, +{ amprocfamily => 'brin/network_bloom_ops', amproclefttype => 'inet', + amprocrighttype => 'inet', amprocnum => '3', + amproc => 'brin_bloom_consistent' }, +{ amprocfamily => 'brin/network_bloom_ops', amproclefttype => 'inet', + amprocrighttype => 'inet', amprocnum => '4', amproc => 'brin_bloom_union' }, +{ amprocfamily => 'brin/network_bloom_ops', amproclefttype => 'inet', + amprocrighttype => 'inet', amprocnum => '5', + amproc => 'brin_bloom_options' }, +{ amprocfamily => 'brin/network_bloom_ops', amproclefttype => 'inet', + amprocrighttype => 'inet', amprocnum => '11', amproc => 'hashinet' }, + # inclusion inet { amprocfamily => 'brin/network_inclusion_ops', amproclefttype => 'inet', amprocrighttype => 'inet', amprocnum => '1', @@ -1090,6 +1345,26 @@ amprocrighttype => 'bpchar', amprocnum => '4', amproc => 'brin_minmax_union' }, +# bloom character +{ amprocfamily => 'brin/bpchar_bloom_ops', amproclefttype => 'bpchar', + amprocrighttype => 'bpchar', amprocnum => '1', + amproc => 'brin_bloom_opcinfo' }, +{ amprocfamily => 'brin/bpchar_bloom_ops', amproclefttype => 'bpchar', + amprocrighttype => 'bpchar', amprocnum => '2', + amproc => 'brin_bloom_add_value' }, +{ amprocfamily => 'brin/bpchar_bloom_ops', amproclefttype => 'bpchar', + amprocrighttype => 'bpchar', amprocnum => '3', + amproc => 'brin_bloom_consistent' }, +{ amprocfamily => 'brin/bpchar_bloom_ops', amproclefttype => 'bpchar', + amprocrighttype => 'bpchar', amprocnum => '4', + amproc => 'brin_bloom_union' }, +{ amprocfamily => 'brin/bpchar_bloom_ops', amproclefttype => 'bpchar', + amprocrighttype => 'bpchar', amprocnum => '5', + amproc => 'brin_bloom_options' }, +{ amprocfamily => 'brin/bpchar_bloom_ops', amproclefttype => 'bpchar', + amprocrighttype => 'bpchar', amprocnum => '11', + amproc => 'hashchar' }, + # minmax time without time zone { amprocfamily => 'brin/time_minmax_ops', amproclefttype => 'time', amprocrighttype => 'time', amprocnum => '1', @@ -1103,6 +1378,24 @@ { amprocfamily => 'brin/time_minmax_ops', amproclefttype => 'time', amprocrighttype => 'time', amprocnum => '4', amproc => 'brin_minmax_union' }, +# bloom time without time zone +{ amprocfamily => 'brin/time_bloom_ops', amproclefttype => 'time', + amprocrighttype => 'time', amprocnum => '1', + amproc => 'brin_bloom_opcinfo' }, +{ amprocfamily => 'brin/time_bloom_ops', amproclefttype => 'time', + amprocrighttype => 'time', amprocnum => '2', + amproc => 'brin_bloom_add_value' }, +{ amprocfamily => 'brin/time_bloom_ops', amproclefttype => 'time', + amprocrighttype => 'time', amprocnum => '3', + amproc => 'brin_bloom_consistent' }, +{ amprocfamily => 'brin/time_bloom_ops', amproclefttype => 'time', + amprocrighttype => 'time', amprocnum => '4', amproc => 'brin_bloom_union' }, +{ amprocfamily => 'brin/time_bloom_ops', amproclefttype => 'time', + amprocrighttype => 'time', amprocnum => '5', + amproc => 'brin_bloom_options' }, +{ amprocfamily => 'brin/time_bloom_ops', amproclefttype => 'time', + amprocrighttype => 'time', amprocnum => '11', amproc => 'time_hash' }, + # minmax datetime (date, timestamp, timestamptz) { amprocfamily => 'brin/datetime_minmax_ops', amproclefttype => 'timestamp', amprocrighttype => 'timestamp', amprocnum => '1', @@ -1210,6 +1503,62 @@ amprocrighttype => 'timestamptz', amprocnum => '4', amproc => 'brin_minmax_union' }, +# bloom datetime (date, timestamp, timestamptz) +{ amprocfamily => 'brin/datetime_bloom_ops', amproclefttype => 'timestamp', + amprocrighttype => 'timestamp', amprocnum => '1', + amproc => 'brin_bloom_opcinfo' }, +{ amprocfamily => 'brin/datetime_bloom_ops', amproclefttype => 'timestamp', + amprocrighttype => 'timestamp', amprocnum => '2', + amproc => 'brin_bloom_add_value' }, +{ amprocfamily => 'brin/datetime_bloom_ops', amproclefttype => 'timestamp', + amprocrighttype => 'timestamp', amprocnum => '3', + amproc => 'brin_bloom_consistent' }, +{ amprocfamily => 'brin/datetime_bloom_ops', amproclefttype => 'timestamp', + amprocrighttype => 'timestamp', amprocnum => '4', + amproc => 'brin_bloom_union' }, +{ amprocfamily => 'brin/datetime_bloom_ops', amproclefttype => 'timestamp', + amprocrighttype => 'timestamp', amprocnum => '5', + amproc => 'brin_bloom_options' }, +{ amprocfamily => 'brin/datetime_bloom_ops', amproclefttype => 'timestamp', + amprocrighttype => 'timestamp', amprocnum => '11', + amproc => 'timestamp_hash' }, + +{ amprocfamily => 'brin/datetime_bloom_ops', amproclefttype => 'timestamptz', + amprocrighttype => 'timestamptz', amprocnum => '1', + amproc => 'brin_bloom_opcinfo' }, +{ amprocfamily => 'brin/datetime_bloom_ops', amproclefttype => 'timestamptz', + amprocrighttype => 'timestamptz', amprocnum => '2', + amproc => 'brin_bloom_add_value' }, +{ amprocfamily => 'brin/datetime_bloom_ops', amproclefttype => 'timestamptz', + amprocrighttype => 'timestamptz', amprocnum => '3', + amproc => 'brin_bloom_consistent' }, +{ amprocfamily => 'brin/datetime_bloom_ops', amproclefttype => 'timestamptz', + amprocrighttype => 'timestamptz', amprocnum => '4', + amproc => 'brin_bloom_union' }, +{ amprocfamily => 'brin/datetime_bloom_ops', amproclefttype => 'timestamptz', + amprocrighttype => 'timestamptz', amprocnum => '5', + amproc => 'brin_bloom_options' }, +{ amprocfamily => 'brin/datetime_bloom_ops', amproclefttype => 'timestamptz', + amprocrighttype => 'timestamptz', amprocnum => '11', + amproc => 'timestamp_hash' }, + +{ amprocfamily => 'brin/datetime_bloom_ops', amproclefttype => 'date', + amprocrighttype => 'date', amprocnum => '1', + amproc => 'brin_bloom_opcinfo' }, +{ amprocfamily => 'brin/datetime_bloom_ops', amproclefttype => 'date', + amprocrighttype => 'date', amprocnum => '2', + amproc => 'brin_bloom_add_value' }, +{ amprocfamily => 'brin/datetime_bloom_ops', amproclefttype => 'date', + amprocrighttype => 'date', amprocnum => '3', + amproc => 'brin_bloom_consistent' }, +{ amprocfamily => 'brin/datetime_bloom_ops', amproclefttype => 'date', + amprocrighttype => 'date', amprocnum => '4', amproc => 'brin_bloom_union' }, +{ amprocfamily => 'brin/datetime_bloom_ops', amproclefttype => 'date', + amprocrighttype => 'date', amprocnum => '5', + amproc => 'brin_bloom_options' }, +{ amprocfamily => 'brin/datetime_bloom_ops', amproclefttype => 'date', + amprocrighttype => 'date', amprocnum => '11', amproc => 'hashint4' }, + # minmax interval { amprocfamily => 'brin/interval_minmax_ops', amproclefttype => 'interval', amprocrighttype => 'interval', amprocnum => '1', @@ -1224,6 +1573,26 @@ amprocrighttype => 'interval', amprocnum => '4', amproc => 'brin_minmax_union' }, +# bloom interval +{ amprocfamily => 'brin/interval_bloom_ops', amproclefttype => 'interval', + amprocrighttype => 'interval', amprocnum => '1', + amproc => 'brin_bloom_opcinfo' }, +{ amprocfamily => 'brin/interval_bloom_ops', amproclefttype => 'interval', + amprocrighttype => 'interval', amprocnum => '2', + amproc => 'brin_bloom_add_value' }, +{ amprocfamily => 'brin/interval_bloom_ops', amproclefttype => 'interval', + amprocrighttype => 'interval', amprocnum => '3', + amproc => 'brin_bloom_consistent' }, +{ amprocfamily => 'brin/interval_bloom_ops', amproclefttype => 'interval', + amprocrighttype => 'interval', amprocnum => '4', + amproc => 'brin_bloom_union' }, +{ amprocfamily => 'brin/interval_bloom_ops', amproclefttype => 'interval', + amprocrighttype => 'interval', amprocnum => '5', + amproc => 'brin_bloom_options' }, +{ amprocfamily => 'brin/interval_bloom_ops', amproclefttype => 'interval', + amprocrighttype => 'interval', amprocnum => '11', + amproc => 'interval_hash' }, + # minmax time with time zone { amprocfamily => 'brin/timetz_minmax_ops', amproclefttype => 'timetz', amprocrighttype => 'timetz', amprocnum => '1', @@ -1238,6 +1607,26 @@ amprocrighttype => 'timetz', amprocnum => '4', amproc => 'brin_minmax_union' }, +# bloom time with time zone +{ amprocfamily => 'brin/timetz_bloom_ops', amproclefttype => 'timetz', + amprocrighttype => 'timetz', amprocnum => '1', + amproc => 'brin_bloom_opcinfo' }, +{ amprocfamily => 'brin/timetz_bloom_ops', amproclefttype => 'timetz', + amprocrighttype => 'timetz', amprocnum => '2', + amproc => 'brin_bloom_add_value' }, +{ amprocfamily => 'brin/timetz_bloom_ops', amproclefttype => 'timetz', + amprocrighttype => 'timetz', amprocnum => '3', + amproc => 'brin_bloom_consistent' }, +{ amprocfamily => 'brin/timetz_bloom_ops', amproclefttype => 'timetz', + amprocrighttype => 'timetz', amprocnum => '4', + amproc => 'brin_bloom_union' }, +{ amprocfamily => 'brin/timetz_bloom_ops', amproclefttype => 'timetz', + amprocrighttype => 'timetz', amprocnum => '5', + amproc => 'brin_bloom_options' }, +{ amprocfamily => 'brin/timetz_bloom_ops', amproclefttype => 'timetz', + amprocrighttype => 'timetz', amprocnum => '11', + amproc => 'timetz_hash' }, + # minmax bit { amprocfamily => 'brin/bit_minmax_ops', amproclefttype => 'bit', amprocrighttype => 'bit', amprocnum => '1', amproc => 'brin_minmax_opcinfo' }, @@ -1278,6 +1667,26 @@ amprocrighttype => 'numeric', amprocnum => '4', amproc => 'brin_minmax_union' }, +# bloom numeric +{ amprocfamily => 'brin/numeric_bloom_ops', amproclefttype => 'numeric', + amprocrighttype => 'numeric', amprocnum => '1', + amproc => 'brin_bloom_opcinfo' }, +{ amprocfamily => 'brin/numeric_bloom_ops', amproclefttype => 'numeric', + amprocrighttype => 'numeric', amprocnum => '2', + amproc => 'brin_bloom_add_value' }, +{ amprocfamily => 'brin/numeric_bloom_ops', amproclefttype => 'numeric', + amprocrighttype => 'numeric', amprocnum => '3', + amproc => 'brin_bloom_consistent' }, +{ amprocfamily => 'brin/numeric_bloom_ops', amproclefttype => 'numeric', + amprocrighttype => 'numeric', amprocnum => '4', + amproc => 'brin_bloom_union' }, +{ amprocfamily => 'brin/numeric_bloom_ops', amproclefttype => 'numeric', + amprocrighttype => 'numeric', amprocnum => '5', + amproc => 'brin_bloom_options' }, +{ amprocfamily => 'brin/numeric_bloom_ops', amproclefttype => 'numeric', + amprocrighttype => 'numeric', amprocnum => '11', + amproc => 'hash_numeric' }, + # minmax uuid { amprocfamily => 'brin/uuid_minmax_ops', amproclefttype => 'uuid', amprocrighttype => 'uuid', amprocnum => '1', @@ -1291,6 +1700,24 @@ { amprocfamily => 'brin/uuid_minmax_ops', amproclefttype => 'uuid', amprocrighttype => 'uuid', amprocnum => '4', amproc => 'brin_minmax_union' }, +# bloom uuid +{ amprocfamily => 'brin/uuid_bloom_ops', amproclefttype => 'uuid', + amprocrighttype => 'uuid', amprocnum => '1', + amproc => 'brin_bloom_opcinfo' }, +{ amprocfamily => 'brin/uuid_bloom_ops', amproclefttype => 'uuid', + amprocrighttype => 'uuid', amprocnum => '2', + amproc => 'brin_bloom_add_value' }, +{ amprocfamily => 'brin/uuid_bloom_ops', amproclefttype => 'uuid', + amprocrighttype => 'uuid', amprocnum => '3', + amproc => 'brin_bloom_consistent' }, +{ amprocfamily => 'brin/uuid_bloom_ops', amproclefttype => 'uuid', + amprocrighttype => 'uuid', amprocnum => '4', amproc => 'brin_bloom_union' }, +{ amprocfamily => 'brin/uuid_bloom_ops', amproclefttype => 'uuid', + amprocrighttype => 'uuid', amprocnum => '5', + amproc => 'brin_bloom_options' }, +{ amprocfamily => 'brin/uuid_bloom_ops', amproclefttype => 'uuid', + amprocrighttype => 'uuid', amprocnum => '11', amproc => 'uuid_hash' }, + # inclusion range types { amprocfamily => 'brin/range_inclusion_ops', amproclefttype => 'anyrange', amprocrighttype => 'anyrange', amprocnum => '1', @@ -1327,6 +1754,26 @@ amprocrighttype => 'pg_lsn', amprocnum => '4', amproc => 'brin_minmax_union' }, +# bloom pg_lsn +{ amprocfamily => 'brin/pg_lsn_bloom_ops', amproclefttype => 'pg_lsn', + amprocrighttype => 'pg_lsn', amprocnum => '1', + amproc => 'brin_bloom_opcinfo' }, +{ amprocfamily => 'brin/pg_lsn_bloom_ops', amproclefttype => 'pg_lsn', + amprocrighttype => 'pg_lsn', amprocnum => '2', + amproc => 'brin_bloom_add_value' }, +{ amprocfamily => 'brin/pg_lsn_bloom_ops', amproclefttype => 'pg_lsn', + amprocrighttype => 'pg_lsn', amprocnum => '3', + amproc => 'brin_bloom_consistent' }, +{ amprocfamily => 'brin/pg_lsn_bloom_ops', amproclefttype => 'pg_lsn', + amprocrighttype => 'pg_lsn', amprocnum => '4', + amproc => 'brin_bloom_union' }, +{ amprocfamily => 'brin/pg_lsn_bloom_ops', amproclefttype => 'pg_lsn', + amprocrighttype => 'pg_lsn', amprocnum => '5', + amproc => 'brin_bloom_options' }, +{ amprocfamily => 'brin/pg_lsn_bloom_ops', amproclefttype => 'pg_lsn', + amprocrighttype => 'pg_lsn', amprocnum => '11', + amproc => 'pg_lsn_hash' }, + # inclusion box { amprocfamily => 'brin/box_inclusion_ops', amproclefttype => 'box', amprocrighttype => 'box', amprocnum => '1', diff --git a/src/include/catalog/pg_opclass.dat b/src/include/catalog/pg_opclass.dat index 5128d6eded..b9e96bf27e 100644 --- a/src/include/catalog/pg_opclass.dat +++ b/src/include/catalog/pg_opclass.dat @@ -265,67 +265,130 @@ { opcmethod => 'brin', opcname => 'bytea_minmax_ops', opcfamily => 'brin/bytea_minmax_ops', opcintype => 'bytea', opckeytype => 'bytea' }, +{ opcmethod => 'brin', opcname => 'bytea_bloom_ops', + opcfamily => 'brin/bytea_bloom_ops', opcintype => 'bytea', + opckeytype => 'bytea', opcdefault => 'f' }, { opcmethod => 'brin', opcname => 'char_minmax_ops', opcfamily => 'brin/char_minmax_ops', opcintype => 'char', opckeytype => 'char' }, +{ opcmethod => 'brin', opcname => 'char_bloom_ops', + opcfamily => 'brin/char_bloom_ops', opcintype => 'char', + opckeytype => 'char', opcdefault => 'f' }, { opcmethod => 'brin', opcname => 'name_minmax_ops', opcfamily => 'brin/name_minmax_ops', opcintype => 'name', opckeytype => 'name' }, +{ opcmethod => 'brin', opcname => 'name_bloom_ops', + opcfamily => 'brin/name_bloom_ops', opcintype => 'name', + opckeytype => 'name', opcdefault => 'f' }, { opcmethod => 'brin', opcname => 'int8_minmax_ops', opcfamily => 'brin/integer_minmax_ops', opcintype => 'int8', opckeytype => 'int8' }, +{ opcmethod => 'brin', opcname => 'int8_bloom_ops', + opcfamily => 'brin/integer_bloom_ops', opcintype => 'int8', + opckeytype => 'int8', opcdefault => 'f' }, { opcmethod => 'brin', opcname => 'int2_minmax_ops', opcfamily => 'brin/integer_minmax_ops', opcintype => 'int2', opckeytype => 'int2' }, +{ opcmethod => 'brin', opcname => 'int2_bloom_ops', + opcfamily => 'brin/integer_bloom_ops', opcintype => 'int2', + opckeytype => 'int2', opcdefault => 'f' }, { opcmethod => 'brin', opcname => 'int4_minmax_ops', opcfamily => 'brin/integer_minmax_ops', opcintype => 'int4', opckeytype => 'int4' }, +{ opcmethod => 'brin', opcname => 'int4_bloom_ops', + opcfamily => 'brin/integer_bloom_ops', opcintype => 'int4', + opckeytype => 'int4', opcdefault => 'f' }, { opcmethod => 'brin', opcname => 'text_minmax_ops', opcfamily => 'brin/text_minmax_ops', opcintype => 'text', opckeytype => 'text' }, +{ opcmethod => 'brin', opcname => 'text_bloom_ops', + opcfamily => 'brin/text_bloom_ops', opcintype => 'text', + opckeytype => 'text', opcdefault => 'f' }, { opcmethod => 'brin', opcname => 'oid_minmax_ops', opcfamily => 'brin/oid_minmax_ops', opcintype => 'oid', opckeytype => 'oid' }, +{ opcmethod => 'brin', opcname => 'oid_bloom_ops', + opcfamily => 'brin/oid_bloom_ops', opcintype => 'oid', + opckeytype => 'oid', opcdefault => 'f' }, { opcmethod => 'brin', opcname => 'tid_minmax_ops', opcfamily => 'brin/tid_minmax_ops', opcintype => 'tid', opckeytype => 'tid' }, +{ opcmethod => 'brin', opcname => 'tid_bloom_ops', + opcfamily => 'brin/tid_bloom_ops', opcintype => 'tid', opckeytype => 'tid', + opcdefault => 'f'}, { opcmethod => 'brin', opcname => 'float4_minmax_ops', opcfamily => 'brin/float_minmax_ops', opcintype => 'float4', opckeytype => 'float4' }, +{ opcmethod => 'brin', opcname => 'float4_bloom_ops', + opcfamily => 'brin/float_bloom_ops', opcintype => 'float4', + opckeytype => 'float4', opcdefault => 'f' }, { opcmethod => 'brin', opcname => 'float8_minmax_ops', opcfamily => 'brin/float_minmax_ops', opcintype => 'float8', opckeytype => 'float8' }, +{ opcmethod => 'brin', opcname => 'float8_bloom_ops', + opcfamily => 'brin/float_bloom_ops', opcintype => 'float8', + opckeytype => 'float8', opcdefault => 'f' }, { opcmethod => 'brin', opcname => 'macaddr_minmax_ops', opcfamily => 'brin/macaddr_minmax_ops', opcintype => 'macaddr', opckeytype => 'macaddr' }, +{ opcmethod => 'brin', opcname => 'macaddr_bloom_ops', + opcfamily => 'brin/macaddr_bloom_ops', opcintype => 'macaddr', + opckeytype => 'macaddr', opcdefault => 'f' }, { opcmethod => 'brin', opcname => 'macaddr8_minmax_ops', opcfamily => 'brin/macaddr8_minmax_ops', opcintype => 'macaddr8', opckeytype => 'macaddr8' }, +{ opcmethod => 'brin', opcname => 'macaddr8_bloom_ops', + opcfamily => 'brin/macaddr8_bloom_ops', opcintype => 'macaddr8', + opckeytype => 'macaddr8', opcdefault => 'f' }, { opcmethod => 'brin', opcname => 'inet_minmax_ops', opcfamily => 'brin/network_minmax_ops', opcintype => 'inet', opcdefault => 'f', opckeytype => 'inet' }, +{ opcmethod => 'brin', opcname => 'inet_bloom_ops', + opcfamily => 'brin/network_bloom_ops', opcintype => 'inet', + opcdefault => 'f', opckeytype => 'inet', opcdefault => 'f' }, { opcmethod => 'brin', opcname => 'inet_inclusion_ops', opcfamily => 'brin/network_inclusion_ops', opcintype => 'inet', opckeytype => 'inet' }, { opcmethod => 'brin', opcname => 'bpchar_minmax_ops', opcfamily => 'brin/bpchar_minmax_ops', opcintype => 'bpchar', opckeytype => 'bpchar' }, +{ opcmethod => 'brin', opcname => 'bpchar_bloom_ops', + opcfamily => 'brin/bpchar_bloom_ops', opcintype => 'bpchar', + opckeytype => 'bpchar', opcdefault => 'f' }, { opcmethod => 'brin', opcname => 'time_minmax_ops', opcfamily => 'brin/time_minmax_ops', opcintype => 'time', opckeytype => 'time' }, +{ opcmethod => 'brin', opcname => 'time_bloom_ops', + opcfamily => 'brin/time_bloom_ops', opcintype => 'time', + opckeytype => 'time', opcdefault => 'f' }, { opcmethod => 'brin', opcname => 'date_minmax_ops', opcfamily => 'brin/datetime_minmax_ops', opcintype => 'date', opckeytype => 'date' }, +{ opcmethod => 'brin', opcname => 'date_bloom_ops', + opcfamily => 'brin/datetime_bloom_ops', opcintype => 'date', + opckeytype => 'date', opcdefault => 'f' }, { opcmethod => 'brin', opcname => 'timestamp_minmax_ops', opcfamily => 'brin/datetime_minmax_ops', opcintype => 'timestamp', opckeytype => 'timestamp' }, +{ opcmethod => 'brin', opcname => 'timestamp_bloom_ops', + opcfamily => 'brin/datetime_bloom_ops', opcintype => 'timestamp', + opckeytype => 'timestamp', opcdefault => 'f' }, { opcmethod => 'brin', opcname => 'timestamptz_minmax_ops', opcfamily => 'brin/datetime_minmax_ops', opcintype => 'timestamptz', opckeytype => 'timestamptz' }, +{ opcmethod => 'brin', opcname => 'timestamptz_bloom_ops', + opcfamily => 'brin/datetime_bloom_ops', opcintype => 'timestamptz', + opckeytype => 'timestamptz', opcdefault => 'f' }, { opcmethod => 'brin', opcname => 'interval_minmax_ops', opcfamily => 'brin/interval_minmax_ops', opcintype => 'interval', opckeytype => 'interval' }, +{ opcmethod => 'brin', opcname => 'interval_bloom_ops', + opcfamily => 'brin/interval_bloom_ops', opcintype => 'interval', + opckeytype => 'interval', opcdefault => 'f' }, { opcmethod => 'brin', opcname => 'timetz_minmax_ops', opcfamily => 'brin/timetz_minmax_ops', opcintype => 'timetz', opckeytype => 'timetz' }, +{ opcmethod => 'brin', opcname => 'timetz_bloom_ops', + opcfamily => 'brin/timetz_bloom_ops', opcintype => 'timetz', + opckeytype => 'timetz', opcdefault => 'f' }, { opcmethod => 'brin', opcname => 'bit_minmax_ops', opcfamily => 'brin/bit_minmax_ops', opcintype => 'bit', opckeytype => 'bit' }, { opcmethod => 'brin', opcname => 'varbit_minmax_ops', @@ -334,18 +397,27 @@ { opcmethod => 'brin', opcname => 'numeric_minmax_ops', opcfamily => 'brin/numeric_minmax_ops', opcintype => 'numeric', opckeytype => 'numeric' }, +{ opcmethod => 'brin', opcname => 'numeric_bloom_ops', + opcfamily => 'brin/numeric_bloom_ops', opcintype => 'numeric', + opckeytype => 'numeric', opcdefault => 'f' }, # no brin opclass for record, anyarray { opcmethod => 'brin', opcname => 'uuid_minmax_ops', opcfamily => 'brin/uuid_minmax_ops', opcintype => 'uuid', opckeytype => 'uuid' }, +{ opcmethod => 'brin', opcname => 'uuid_bloom_ops', + opcfamily => 'brin/uuid_bloom_ops', opcintype => 'uuid', + opckeytype => 'uuid', opcdefault => 'f' }, { opcmethod => 'brin', opcname => 'range_inclusion_ops', opcfamily => 'brin/range_inclusion_ops', opcintype => 'anyrange', opckeytype => 'anyrange' }, { opcmethod => 'brin', opcname => 'pg_lsn_minmax_ops', opcfamily => 'brin/pg_lsn_minmax_ops', opcintype => 'pg_lsn', opckeytype => 'pg_lsn' }, +{ opcmethod => 'brin', opcname => 'pg_lsn_bloom_ops', + opcfamily => 'brin/pg_lsn_bloom_ops', opcintype => 'pg_lsn', + opckeytype => 'pg_lsn', opcdefault => 'f' }, # no brin opclass for enum, tsvector, tsquery, jsonb diff --git a/src/include/catalog/pg_opfamily.dat b/src/include/catalog/pg_opfamily.dat index 57e5aa0d8b..5c294bac89 100644 --- a/src/include/catalog/pg_opfamily.dat +++ b/src/include/catalog/pg_opfamily.dat @@ -182,50 +182,88 @@ opfmethod => 'gin', opfname => 'jsonb_path_ops' }, { oid => '4054', opfmethod => 'brin', opfname => 'integer_minmax_ops' }, +{ oid => '8100', + opfmethod => 'brin', opfname => 'integer_bloom_ops' }, { oid => '4055', opfmethod => 'brin', opfname => 'numeric_minmax_ops' }, { oid => '4056', opfmethod => 'brin', opfname => 'text_minmax_ops' }, +{ oid => '8101', + opfmethod => 'brin', opfname => 'text_bloom_ops' }, +{ oid => '8102', + opfmethod => 'brin', opfname => 'numeric_bloom_ops' }, { oid => '4058', opfmethod => 'brin', opfname => 'timetz_minmax_ops' }, +{ oid => '8103', + opfmethod => 'brin', opfname => 'timetz_bloom_ops' }, { oid => '4059', opfmethod => 'brin', opfname => 'datetime_minmax_ops' }, +{ oid => '8104', + opfmethod => 'brin', opfname => 'datetime_bloom_ops' }, { oid => '4062', opfmethod => 'brin', opfname => 'char_minmax_ops' }, +{ oid => '8105', + opfmethod => 'brin', opfname => 'char_bloom_ops' }, { oid => '4064', opfmethod => 'brin', opfname => 'bytea_minmax_ops' }, +{ oid => '8106', + opfmethod => 'brin', opfname => 'bytea_bloom_ops' }, { oid => '4065', opfmethod => 'brin', opfname => 'name_minmax_ops' }, +{ oid => '8107', + opfmethod => 'brin', opfname => 'name_bloom_ops' }, { oid => '4068', opfmethod => 'brin', opfname => 'oid_minmax_ops' }, +{ oid => '8108', + opfmethod => 'brin', opfname => 'oid_bloom_ops' }, { oid => '4069', opfmethod => 'brin', opfname => 'tid_minmax_ops' }, +{ oid => '8123', + opfmethod => 'brin', opfname => 'tid_bloom_ops' }, { oid => '4070', opfmethod => 'brin', opfname => 'float_minmax_ops' }, +{ oid => '8109', + opfmethod => 'brin', opfname => 'float_bloom_ops' }, { oid => '4074', opfmethod => 'brin', opfname => 'macaddr_minmax_ops' }, +{ oid => '8110', + opfmethod => 'brin', opfname => 'macaddr_bloom_ops' }, { oid => '4109', opfmethod => 'brin', opfname => 'macaddr8_minmax_ops' }, +{ oid => '8111', + opfmethod => 'brin', opfname => 'macaddr8_bloom_ops' }, { oid => '4075', opfmethod => 'brin', opfname => 'network_minmax_ops' }, { oid => '4102', opfmethod => 'brin', opfname => 'network_inclusion_ops' }, +{ oid => '8112', + opfmethod => 'brin', opfname => 'network_bloom_ops' }, { oid => '4076', opfmethod => 'brin', opfname => 'bpchar_minmax_ops' }, +{ oid => '8113', + opfmethod => 'brin', opfname => 'bpchar_bloom_ops' }, { oid => '4077', opfmethod => 'brin', opfname => 'time_minmax_ops' }, +{ oid => '8114', + opfmethod => 'brin', opfname => 'time_bloom_ops' }, { oid => '4078', opfmethod => 'brin', opfname => 'interval_minmax_ops' }, +{ oid => '8115', + opfmethod => 'brin', opfname => 'interval_bloom_ops' }, { oid => '4079', opfmethod => 'brin', opfname => 'bit_minmax_ops' }, { oid => '4080', opfmethod => 'brin', opfname => 'varbit_minmax_ops' }, { oid => '4081', opfmethod => 'brin', opfname => 'uuid_minmax_ops' }, +{ oid => '8116', + opfmethod => 'brin', opfname => 'uuid_bloom_ops' }, { oid => '4103', opfmethod => 'brin', opfname => 'range_inclusion_ops' }, { oid => '4082', opfmethod => 'brin', opfname => 'pg_lsn_minmax_ops' }, +{ oid => '8117', + opfmethod => 'brin', opfname => 'pg_lsn_bloom_ops' }, { oid => '4104', opfmethod => 'brin', opfname => 'box_inclusion_ops' }, { oid => '5000', diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 871e024e00..c8c4f449d4 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -8166,6 +8166,26 @@ proargtypes => 'internal internal internal', prosrc => 'brin_inclusion_union' }, +# BRIN bloom +{ oid => '8118', descr => 'BRIN bloom support', + proname => 'brin_bloom_opcinfo', prorettype => 'internal', + proargtypes => 'internal', prosrc => 'brin_bloom_opcinfo' }, +{ oid => '8119', descr => 'BRIN bloom support', + proname => 'brin_bloom_add_value', prorettype => 'bool', + proargtypes => 'internal internal internal internal', + prosrc => 'brin_bloom_add_value' }, +{ oid => '8120', descr => 'BRIN bloom support', + proname => 'brin_bloom_consistent', prorettype => 'bool', + proargtypes => 'internal internal internal int4', + prosrc => 'brin_bloom_consistent' }, +{ oid => '8121', descr => 'BRIN bloom support', + proname => 'brin_bloom_union', prorettype => 'bool', + proargtypes => 'internal internal internal', + prosrc => 'brin_bloom_union' }, +{ oid => '8122', descr => 'BRIN bloom support', + proname => 'brin_bloom_options', prorettype => 'void', proisstrict => 'f', + proargtypes => 'internal', prosrc => 'brin_bloom_options' }, + # userlock replacements { oid => '2880', descr => 'obtain exclusive advisory lock', proname => 'pg_advisory_lock', provolatile => 'v', proparallel => 'r', @@ -11315,3 +11335,17 @@ prosrc => 'unicode_is_normalized' }, ] + +{ oid => '9035', descr => 'I/O', + proname => 'brin_bloom_summary_in', prorettype => 'pg_brin_bloom_summary', + proargtypes => 'cstring', prosrc => 'brin_bloom_summary_in' }, +{ oid => '9036', descr => 'I/O', + proname => 'brin_bloom_summary_out', prorettype => 'cstring', + proargtypes => 'pg_brin_bloom_summary', prosrc => 'brin_bloom_summary_out' }, +{ oid => '9037', descr => 'I/O', + proname => 'brin_bloom_summary_recv', provolatile => 's', + prorettype => 'pg_brin_bloom_summary', proargtypes => 'internal', + prosrc => 'brin_bloom_summary_recv' }, +{ oid => '9038', descr => 'I/O', + proname => 'brin_bloom_summary_send', provolatile => 's', prorettype => 'bytea', + proargtypes => 'pg_brin_bloom_summary', prosrc => 'brin_bloom_summary_send' }, diff --git a/src/include/catalog/pg_type.dat b/src/include/catalog/pg_type.dat index 49dc3e18e0..3275719896 100644 --- a/src/include/catalog/pg_type.dat +++ b/src/include/catalog/pg_type.dat @@ -673,3 +673,10 @@ typalign => 'd', typstorage => 'x' }, ] + +{ oid => '9034', + descr => 'BRIN bloom summary', + typname => 'pg_brin_bloom_summary', typlen => '-1', typbyval => 'f', typcategory => 'S', + typinput => 'brin_bloom_summary_in', typoutput => 'brin_bloom_summary_out', + typreceive => 'brin_bloom_summary_recv', typsend => 'brin_bloom_summary_send', + typalign => 'i', typstorage => 'x', typcollation => 'default' }, diff --git a/src/test/regress/expected/brin_bloom.out b/src/test/regress/expected/brin_bloom.out new file mode 100644 index 0000000000..19b866283a --- /dev/null +++ b/src/test/regress/expected/brin_bloom.out @@ -0,0 +1,456 @@ +CREATE TABLE brintest_bloom (byteacol bytea, + charcol "char", + namecol name, + int8col bigint, + int2col smallint, + int4col integer, + textcol text, + oidcol oid, + float4col real, + float8col double precision, + macaddrcol macaddr, + inetcol inet, + cidrcol cidr, + bpcharcol character, + datecol date, + timecol time without time zone, + timestampcol timestamp without time zone, + timestamptzcol timestamp with time zone, + intervalcol interval, + timetzcol time with time zone, + numericcol numeric, + uuidcol uuid, + lsncol pg_lsn +) WITH (fillfactor=10); +INSERT INTO brintest_bloom SELECT + repeat(stringu1, 8)::bytea, + substr(stringu1, 1, 1)::"char", + stringu1::name, 142857 * tenthous, + thousand, + twothousand, + repeat(stringu1, 8), + unique1::oid, + (four + 1.0)/(hundred+1), + odd::float8 / (tenthous + 1), + format('%s:00:%s:00:%s:00', to_hex(odd), to_hex(even), to_hex(hundred))::macaddr, + inet '10.2.3.4/24' + tenthous, + cidr '10.2.3/24' + tenthous, + substr(stringu1, 1, 1)::bpchar, + date '1995-08-15' + tenthous, + time '01:20:30' + thousand * interval '18.5 second', + timestamp '1942-07-23 03:05:09' + tenthous * interval '36.38 hours', + timestamptz '1972-10-10 03:00' + thousand * interval '1 hour', + justify_days(justify_hours(tenthous * interval '12 minutes')), + timetz '01:30:20+02' + hundred * interval '15 seconds', + tenthous::numeric(36,30) * fivethous * even / (hundred + 1), + 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, + format('%s/%s%s', odd, even, tenthous)::pg_lsn +FROM tenk1 ORDER BY unique2 LIMIT 100; +-- throw in some NULL's and different values +INSERT INTO brintest_bloom (inetcol, cidrcol) SELECT + inet 'fe80::6e40:8ff:fea9:8c46' + tenthous, + cidr 'fe80::6e40:8ff:fea9:8c46' + tenthous +FROM tenk1 ORDER BY thousand, tenthous LIMIT 25; +-- test bloom specific index options +-- ndistinct must be >= -1.0 +CREATE INDEX brinidx_bloom ON brintest_bloom USING brin ( + byteacol bytea_bloom_ops(n_distinct_per_range = -1.1) +); +ERROR: value -1.1 out of bounds for option "n_distinct_per_range" +DETAIL: Valid values are between "-1.000000" and "2147483647.000000". +-- false_positive_rate must be between 0.001 and 1.0 +CREATE INDEX brinidx_bloom ON brintest_bloom USING brin ( + byteacol bytea_bloom_ops(false_positive_rate = 0.0009) +); +ERROR: value 0.0009 out of bounds for option "false_positive_rate" +DETAIL: Valid values are between "0.001000" and "0.100000". +CREATE INDEX brinidx_bloom ON brintest_bloom USING brin ( + byteacol bytea_bloom_ops(false_positive_rate = 0.11) +); +ERROR: value 0.11 out of bounds for option "false_positive_rate" +DETAIL: Valid values are between "0.001000" and "0.100000". +CREATE INDEX brinidx_bloom ON brintest_bloom USING brin ( + byteacol bytea_bloom_ops, + charcol char_bloom_ops, + namecol name_bloom_ops, + int8col int8_bloom_ops, + int2col int2_bloom_ops, + int4col int4_bloom_ops, + textcol text_bloom_ops, + oidcol oid_bloom_ops, + float4col float4_bloom_ops, + float8col float8_bloom_ops, + macaddrcol macaddr_bloom_ops, + inetcol inet_bloom_ops, + cidrcol inet_bloom_ops, + bpcharcol bpchar_bloom_ops, + datecol date_bloom_ops, + timecol time_bloom_ops, + timestampcol timestamp_bloom_ops, + timestamptzcol timestamptz_bloom_ops, + intervalcol interval_bloom_ops, + timetzcol timetz_bloom_ops, + numericcol numeric_bloom_ops, + uuidcol uuid_bloom_ops, + lsncol pg_lsn_bloom_ops +) with (pages_per_range = 1); +CREATE TABLE brinopers_bloom (colname name, typ text, + op text[], value text[], matches int[], + check (cardinality(op) = cardinality(value)), + check (cardinality(op) = cardinality(matches))); +INSERT INTO brinopers_bloom VALUES + ('byteacol', 'bytea', + '{=}', + '{BNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAA}', + '{1}'), + ('charcol', '"char"', + '{=}', + '{M}', + '{6}'), + ('namecol', 'name', + '{=}', + '{MAAAAA}', + '{2}'), + ('int2col', 'int2', + '{=}', + '{800}', + '{1}'), + ('int2col', 'int4', + '{=}', + '{800}', + '{1}'), + ('int2col', 'int8', + '{=}', + '{800}', + '{1}'), + ('int4col', 'int2', + '{=}', + '{800}', + '{1}'), + ('int4col', 'int4', + '{=}', + '{800}', + '{1}'), + ('int4col', 'int8', + '{=}', + '{800}', + '{1}'), + ('int8col', 'int8', + '{=}', + '{1257141600}', + '{1}'), + ('textcol', 'text', + '{=}', + '{BNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAA}', + '{1}'), + ('oidcol', 'oid', + '{=}', + '{8800}', + '{1}'), + ('float4col', 'float4', + '{=}', + '{1}', + '{4}'), + ('float4col', 'float8', + '{=}', + '{1}', + '{4}'), + ('float8col', 'float4', + '{=}', + '{0}', + '{1}'), + ('float8col', 'float8', + '{=}', + '{0}', + '{1}'), + ('macaddrcol', 'macaddr', + '{=}', + '{2c:00:2d:00:16:00}', + '{2}'), + ('inetcol', 'inet', + '{=}', + '{10.2.14.231/24}', + '{1}'), + ('inetcol', 'cidr', + '{=}', + '{fe80::6e40:8ff:fea9:8c46}', + '{1}'), + ('cidrcol', 'inet', + '{=}', + '{10.2.14/24}', + '{2}'), + ('cidrcol', 'inet', + '{=}', + '{fe80::6e40:8ff:fea9:8c46}', + '{1}'), + ('cidrcol', 'cidr', + '{=}', + '{10.2.14/24}', + '{2}'), + ('cidrcol', 'cidr', + '{=}', + '{fe80::6e40:8ff:fea9:8c46}', + '{1}'), + ('bpcharcol', 'bpchar', + '{=}', + '{W}', + '{6}'), + ('datecol', 'date', + '{=}', + '{2009-12-01}', + '{1}'), + ('timecol', 'time', + '{=}', + '{02:28:57}', + '{1}'), + ('timestampcol', 'timestamp', + '{=}', + '{1964-03-24 19:26:45}', + '{1}'), + ('timestampcol', 'timestamptz', + '{=}', + '{1964-03-24 19:26:45}', + '{1}'), + ('timestamptzcol', 'timestamptz', + '{=}', + '{1972-10-19 09:00:00-07}', + '{1}'), + ('intervalcol', 'interval', + '{=}', + '{1 mons 13 days 12:24}', + '{1}'), + ('timetzcol', 'timetz', + '{=}', + '{01:35:50+02}', + '{2}'), + ('numericcol', 'numeric', + '{=}', + '{2268164.347826086956521739130434782609}', + '{1}'), + ('uuidcol', 'uuid', + '{=}', + '{52225222-5222-5222-5222-522252225222}', + '{1}'), + ('lsncol', 'pg_lsn', + '{=, IS, IS NOT}', + '{44/455222, NULL, NULL}', + '{1, 25, 100}'); +DO $x$ +DECLARE + r record; + r2 record; + cond text; + idx_ctids tid[]; + ss_ctids tid[]; + count int; + plan_ok bool; + plan_line text; +BEGIN + FOR r IN SELECT colname, oper, typ, value[ordinality], matches[ordinality] FROM brinopers_bloom, unnest(op) WITH ORDINALITY AS oper LOOP + + -- prepare the condition + IF r.value IS NULL THEN + cond := format('%I %s %L', r.colname, r.oper, r.value); + ELSE + cond := format('%I %s %L::%s', r.colname, r.oper, r.value, r.typ); + END IF; + + -- run the query using the brin index + SET enable_seqscan = 0; + SET enable_bitmapscan = 1; + + plan_ok := false; + FOR plan_line IN EXECUTE format($y$EXPLAIN SELECT array_agg(ctid) FROM brintest_bloom WHERE %s $y$, cond) LOOP + IF plan_line LIKE '%Bitmap Heap Scan on brintest_bloom%' THEN + plan_ok := true; + END IF; + END LOOP; + IF NOT plan_ok THEN + RAISE WARNING 'did not get bitmap indexscan plan for %', r; + END IF; + + EXECUTE format($y$SELECT array_agg(ctid) FROM brintest_bloom WHERE %s $y$, cond) + INTO idx_ctids; + + -- run the query using a seqscan + SET enable_seqscan = 1; + SET enable_bitmapscan = 0; + + plan_ok := false; + FOR plan_line IN EXECUTE format($y$EXPLAIN SELECT array_agg(ctid) FROM brintest_bloom WHERE %s $y$, cond) LOOP + IF plan_line LIKE '%Seq Scan on brintest_bloom%' THEN + plan_ok := true; + END IF; + END LOOP; + IF NOT plan_ok THEN + RAISE WARNING 'did not get seqscan plan for %', r; + END IF; + + EXECUTE format($y$SELECT array_agg(ctid) FROM brintest_bloom WHERE %s $y$, cond) + INTO ss_ctids; + + -- make sure both return the same results + count := array_length(idx_ctids, 1); + + IF NOT (count = array_length(ss_ctids, 1) AND + idx_ctids @> ss_ctids AND + idx_ctids <@ ss_ctids) THEN + -- report the results of each scan to make the differences obvious + RAISE WARNING 'something not right in %: count %', r, count; + SET enable_seqscan = 1; + SET enable_bitmapscan = 0; + FOR r2 IN EXECUTE 'SELECT ' || r.colname || ' FROM brintest_bloom WHERE ' || cond LOOP + RAISE NOTICE 'seqscan: %', r2; + END LOOP; + + SET enable_seqscan = 0; + SET enable_bitmapscan = 1; + FOR r2 IN EXECUTE 'SELECT ' || r.colname || ' FROM brintest_bloom WHERE ' || cond LOOP + RAISE NOTICE 'bitmapscan: %', r2; + END LOOP; + END IF; + + -- make sure we found expected number of matches + IF count != r.matches THEN RAISE WARNING 'unexpected number of results % for %', count, r; END IF; + END LOOP; +END; +$x$; +RESET enable_seqscan; +RESET enable_bitmapscan; +INSERT INTO brintest_bloom SELECT + repeat(stringu1, 42)::bytea, + substr(stringu1, 1, 1)::"char", + stringu1::name, 142857 * tenthous, + thousand, + twothousand, + repeat(stringu1, 42), + unique1::oid, + (four + 1.0)/(hundred+1), + odd::float8 / (tenthous + 1), + format('%s:00:%s:00:%s:00', to_hex(odd), to_hex(even), to_hex(hundred))::macaddr, + inet '10.2.3.4' + tenthous, + cidr '10.2.3/24' + tenthous, + substr(stringu1, 1, 1)::bpchar, + date '1995-08-15' + tenthous, + time '01:20:30' + thousand * interval '18.5 second', + timestamp '1942-07-23 03:05:09' + tenthous * interval '36.38 hours', + timestamptz '1972-10-10 03:00' + thousand * interval '1 hour', + justify_days(justify_hours(tenthous * interval '12 minutes')), + timetz '01:30:20' + hundred * interval '15 seconds', + tenthous::numeric(36,30) * fivethous * even / (hundred + 1), + 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, + format('%s/%s%s', odd, even, tenthous)::pg_lsn +FROM tenk1 ORDER BY unique2 LIMIT 5 OFFSET 5; +SELECT brin_desummarize_range('brinidx_bloom', 0); + brin_desummarize_range +------------------------ + +(1 row) + +VACUUM brintest_bloom; -- force a summarization cycle in brinidx +UPDATE brintest_bloom SET int8col = int8col * int4col; +UPDATE brintest_bloom SET textcol = '' WHERE textcol IS NOT NULL; +-- Tests for brin_summarize_new_values +SELECT brin_summarize_new_values('brintest_bloom'); -- error, not an index +ERROR: "brintest_bloom" is not an index +SELECT brin_summarize_new_values('tenk1_unique1'); -- error, not a BRIN index +ERROR: "tenk1_unique1" is not a BRIN index +SELECT brin_summarize_new_values('brinidx_bloom'); -- ok, no change expected + brin_summarize_new_values +--------------------------- + 0 +(1 row) + +-- Tests for brin_desummarize_range +SELECT brin_desummarize_range('brinidx_bloom', -1); -- error, invalid range +ERROR: block number out of range: -1 +SELECT brin_desummarize_range('brinidx_bloom', 0); + brin_desummarize_range +------------------------ + +(1 row) + +SELECT brin_desummarize_range('brinidx_bloom', 0); + brin_desummarize_range +------------------------ + +(1 row) + +SELECT brin_desummarize_range('brinidx_bloom', 100000000); + brin_desummarize_range +------------------------ + +(1 row) + +-- Test brin_summarize_range +CREATE TABLE brin_summarize_bloom ( + value int +) WITH (fillfactor=10, autovacuum_enabled=false); +CREATE INDEX brin_summarize_bloom_idx ON brin_summarize_bloom USING brin (value) WITH (pages_per_range=2); +-- Fill a few pages +DO $$ +DECLARE curtid tid; +BEGIN + LOOP + INSERT INTO brin_summarize_bloom VALUES (1) RETURNING ctid INTO curtid; + EXIT WHEN curtid > tid '(2, 0)'; + END LOOP; +END; +$$; +-- summarize one range +SELECT brin_summarize_range('brin_summarize_bloom_idx', 0); + brin_summarize_range +---------------------- + 0 +(1 row) + +-- nothing: already summarized +SELECT brin_summarize_range('brin_summarize_bloom_idx', 1); + brin_summarize_range +---------------------- + 0 +(1 row) + +-- summarize one range +SELECT brin_summarize_range('brin_summarize_bloom_idx', 2); + brin_summarize_range +---------------------- + 1 +(1 row) + +-- nothing: page doesn't exist in table +SELECT brin_summarize_range('brin_summarize_bloom_idx', 4294967295); + brin_summarize_range +---------------------- + 0 +(1 row) + +-- invalid block number values +SELECT brin_summarize_range('brin_summarize_bloom_idx', -1); +ERROR: block number out of range: -1 +SELECT brin_summarize_range('brin_summarize_bloom_idx', 4294967296); +ERROR: block number out of range: 4294967296 +-- test brin cost estimates behave sanely based on correlation of values +CREATE TABLE brin_test_bloom (a INT, b INT); +INSERT INTO brin_test_bloom SELECT x/100,x%100 FROM generate_series(1,10000) x(x); +CREATE INDEX brin_test_bloom_a_idx ON brin_test_bloom USING brin (a) WITH (pages_per_range = 2); +CREATE INDEX brin_test_bloom_b_idx ON brin_test_bloom USING brin (b) WITH (pages_per_range = 2); +VACUUM ANALYZE brin_test_bloom; +-- Ensure brin index is used when columns are perfectly correlated +EXPLAIN (COSTS OFF) SELECT * FROM brin_test_bloom WHERE a = 1; + QUERY PLAN +-------------------------------------------------- + Bitmap Heap Scan on brin_test_bloom + Recheck Cond: (a = 1) + -> Bitmap Index Scan on brin_test_bloom_a_idx + Index Cond: (a = 1) +(4 rows) + +-- Ensure brin index is not used when values are not correlated +EXPLAIN (COSTS OFF) SELECT * FROM brin_test_bloom WHERE b = 1; + QUERY PLAN +----------------------------- + Seq Scan on brin_test_bloom + Filter: (b = 1) +(2 rows) + diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out index 254ca06d3d..ef4b4444b9 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -2037,6 +2037,7 @@ ORDER BY 1, 2, 3; 2742 | 16 | @@ 3580 | 1 | < 3580 | 1 | << + 3580 | 1 | = 3580 | 2 | &< 3580 | 2 | <= 3580 | 3 | && @@ -2100,7 +2101,7 @@ ORDER BY 1, 2, 3; 4000 | 28 | ^@ 4000 | 29 | <^ 4000 | 30 | >^ -(123 rows) +(124 rows) -- Check that all opclass search operators have selectivity estimators. -- This is not absolutely required, but it seems a reasonable thing diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out index 7204fdb0b4..a7a5b22d4f 100644 --- a/src/test/regress/expected/psql.out +++ b/src/test/regress/expected/psql.out @@ -4993,8 +4993,9 @@ List of access methods List of operator classes AM | Input type | Storage type | Operator class | Default? ------+------------+--------------+----------------+---------- + brin | oid | | oid_bloom_ops | no brin | oid | | oid_minmax_ops | yes -(1 row) +(2 rows) \dAf spgist List of operator families diff --git a/src/test/regress/expected/type_sanity.out b/src/test/regress/expected/type_sanity.out index 0c74dc96a8..a44bde2e6e 100644 --- a/src/test/regress/expected/type_sanity.out +++ b/src/test/regress/expected/type_sanity.out @@ -67,13 +67,14 @@ WHERE p1.typtype not in ('p') AND p1.typname NOT LIKE E'\\_%' WHERE p2.typname = ('_' || p1.typname)::name AND p2.typelem = p1.oid and p1.typarray = p2.oid) ORDER BY p1.oid; - oid | typname -------+----------------- + oid | typname +------+----------------------- 194 | pg_node_tree 3361 | pg_ndistinct 3402 | pg_dependencies 5017 | pg_mcv_list -(4 rows) + 9034 | pg_brin_bloom_summary +(5 rows) -- Make sure typarray points to a "true" array type of our own base SELECT p1.oid, p1.typname as basetype, p2.typname as arraytype, diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index e0e1ef71dd..ef372281ef 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -77,6 +77,11 @@ test: select_into select_distinct select_distinct_on select_implicit select_havi # ---------- test: brin gin gist spgist privileges init_privs security_label collate matview lock replica_identity rowsecurity object_address tablesample groupingsets drop_operator password identity generated join_hash +# ---------- +# Additional BRIN tests +# ---------- +test: brin_bloom + # ---------- # Another group of parallel tests # ---------- diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 081fce32e7..fdddc48f62 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -109,6 +109,7 @@ test: delete test: namespace test: prepared_xacts test: brin +test: brin_bloom test: gin test: gist test: spgist diff --git a/src/test/regress/sql/brin_bloom.sql b/src/test/regress/sql/brin_bloom.sql new file mode 100644 index 0000000000..3c2ef56316 --- /dev/null +++ b/src/test/regress/sql/brin_bloom.sql @@ -0,0 +1,404 @@ +CREATE TABLE brintest_bloom (byteacol bytea, + charcol "char", + namecol name, + int8col bigint, + int2col smallint, + int4col integer, + textcol text, + oidcol oid, + float4col real, + float8col double precision, + macaddrcol macaddr, + inetcol inet, + cidrcol cidr, + bpcharcol character, + datecol date, + timecol time without time zone, + timestampcol timestamp without time zone, + timestamptzcol timestamp with time zone, + intervalcol interval, + timetzcol time with time zone, + numericcol numeric, + uuidcol uuid, + lsncol pg_lsn +) WITH (fillfactor=10); + +INSERT INTO brintest_bloom SELECT + repeat(stringu1, 8)::bytea, + substr(stringu1, 1, 1)::"char", + stringu1::name, 142857 * tenthous, + thousand, + twothousand, + repeat(stringu1, 8), + unique1::oid, + (four + 1.0)/(hundred+1), + odd::float8 / (tenthous + 1), + format('%s:00:%s:00:%s:00', to_hex(odd), to_hex(even), to_hex(hundred))::macaddr, + inet '10.2.3.4/24' + tenthous, + cidr '10.2.3/24' + tenthous, + substr(stringu1, 1, 1)::bpchar, + date '1995-08-15' + tenthous, + time '01:20:30' + thousand * interval '18.5 second', + timestamp '1942-07-23 03:05:09' + tenthous * interval '36.38 hours', + timestamptz '1972-10-10 03:00' + thousand * interval '1 hour', + justify_days(justify_hours(tenthous * interval '12 minutes')), + timetz '01:30:20+02' + hundred * interval '15 seconds', + tenthous::numeric(36,30) * fivethous * even / (hundred + 1), + 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, + format('%s/%s%s', odd, even, tenthous)::pg_lsn +FROM tenk1 ORDER BY unique2 LIMIT 100; + +-- throw in some NULL's and different values +INSERT INTO brintest_bloom (inetcol, cidrcol) SELECT + inet 'fe80::6e40:8ff:fea9:8c46' + tenthous, + cidr 'fe80::6e40:8ff:fea9:8c46' + tenthous +FROM tenk1 ORDER BY thousand, tenthous LIMIT 25; + +-- test bloom specific index options +-- ndistinct must be >= -1.0 +CREATE INDEX brinidx_bloom ON brintest_bloom USING brin ( + byteacol bytea_bloom_ops(n_distinct_per_range = -1.1) +); +-- false_positive_rate must be between 0.001 and 1.0 +CREATE INDEX brinidx_bloom ON brintest_bloom USING brin ( + byteacol bytea_bloom_ops(false_positive_rate = 0.0009) +); +CREATE INDEX brinidx_bloom ON brintest_bloom USING brin ( + byteacol bytea_bloom_ops(false_positive_rate = 0.11) +); + +CREATE INDEX brinidx_bloom ON brintest_bloom USING brin ( + byteacol bytea_bloom_ops, + charcol char_bloom_ops, + namecol name_bloom_ops, + int8col int8_bloom_ops, + int2col int2_bloom_ops, + int4col int4_bloom_ops, + textcol text_bloom_ops, + oidcol oid_bloom_ops, + float4col float4_bloom_ops, + float8col float8_bloom_ops, + macaddrcol macaddr_bloom_ops, + inetcol inet_bloom_ops, + cidrcol inet_bloom_ops, + bpcharcol bpchar_bloom_ops, + datecol date_bloom_ops, + timecol time_bloom_ops, + timestampcol timestamp_bloom_ops, + timestamptzcol timestamptz_bloom_ops, + intervalcol interval_bloom_ops, + timetzcol timetz_bloom_ops, + numericcol numeric_bloom_ops, + uuidcol uuid_bloom_ops, + lsncol pg_lsn_bloom_ops +) with (pages_per_range = 1); + +CREATE TABLE brinopers_bloom (colname name, typ text, + op text[], value text[], matches int[], + check (cardinality(op) = cardinality(value)), + check (cardinality(op) = cardinality(matches))); + +INSERT INTO brinopers_bloom VALUES + ('byteacol', 'bytea', + '{=}', + '{BNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAA}', + '{1}'), + ('charcol', '"char"', + '{=}', + '{M}', + '{6}'), + ('namecol', 'name', + '{=}', + '{MAAAAA}', + '{2}'), + ('int2col', 'int2', + '{=}', + '{800}', + '{1}'), + ('int2col', 'int4', + '{=}', + '{800}', + '{1}'), + ('int2col', 'int8', + '{=}', + '{800}', + '{1}'), + ('int4col', 'int2', + '{=}', + '{800}', + '{1}'), + ('int4col', 'int4', + '{=}', + '{800}', + '{1}'), + ('int4col', 'int8', + '{=}', + '{800}', + '{1}'), + ('int8col', 'int8', + '{=}', + '{1257141600}', + '{1}'), + ('textcol', 'text', + '{=}', + '{BNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAABNAAAA}', + '{1}'), + ('oidcol', 'oid', + '{=}', + '{8800}', + '{1}'), + ('float4col', 'float4', + '{=}', + '{1}', + '{4}'), + ('float4col', 'float8', + '{=}', + '{1}', + '{4}'), + ('float8col', 'float4', + '{=}', + '{0}', + '{1}'), + ('float8col', 'float8', + '{=}', + '{0}', + '{1}'), + ('macaddrcol', 'macaddr', + '{=}', + '{2c:00:2d:00:16:00}', + '{2}'), + ('inetcol', 'inet', + '{=}', + '{10.2.14.231/24}', + '{1}'), + ('inetcol', 'cidr', + '{=}', + '{fe80::6e40:8ff:fea9:8c46}', + '{1}'), + ('cidrcol', 'inet', + '{=}', + '{10.2.14/24}', + '{2}'), + ('cidrcol', 'inet', + '{=}', + '{fe80::6e40:8ff:fea9:8c46}', + '{1}'), + ('cidrcol', 'cidr', + '{=}', + '{10.2.14/24}', + '{2}'), + ('cidrcol', 'cidr', + '{=}', + '{fe80::6e40:8ff:fea9:8c46}', + '{1}'), + ('bpcharcol', 'bpchar', + '{=}', + '{W}', + '{6}'), + ('datecol', 'date', + '{=}', + '{2009-12-01}', + '{1}'), + ('timecol', 'time', + '{=}', + '{02:28:57}', + '{1}'), + ('timestampcol', 'timestamp', + '{=}', + '{1964-03-24 19:26:45}', + '{1}'), + ('timestampcol', 'timestamptz', + '{=}', + '{1964-03-24 19:26:45}', + '{1}'), + ('timestamptzcol', 'timestamptz', + '{=}', + '{1972-10-19 09:00:00-07}', + '{1}'), + ('intervalcol', 'interval', + '{=}', + '{1 mons 13 days 12:24}', + '{1}'), + ('timetzcol', 'timetz', + '{=}', + '{01:35:50+02}', + '{2}'), + ('numericcol', 'numeric', + '{=}', + '{2268164.347826086956521739130434782609}', + '{1}'), + ('uuidcol', 'uuid', + '{=}', + '{52225222-5222-5222-5222-522252225222}', + '{1}'), + ('lsncol', 'pg_lsn', + '{=, IS, IS NOT}', + '{44/455222, NULL, NULL}', + '{1, 25, 100}'); + +DO $x$ +DECLARE + r record; + r2 record; + cond text; + idx_ctids tid[]; + ss_ctids tid[]; + count int; + plan_ok bool; + plan_line text; +BEGIN + FOR r IN SELECT colname, oper, typ, value[ordinality], matches[ordinality] FROM brinopers_bloom, unnest(op) WITH ORDINALITY AS oper LOOP + + -- prepare the condition + IF r.value IS NULL THEN + cond := format('%I %s %L', r.colname, r.oper, r.value); + ELSE + cond := format('%I %s %L::%s', r.colname, r.oper, r.value, r.typ); + END IF; + + -- run the query using the brin index + SET enable_seqscan = 0; + SET enable_bitmapscan = 1; + + plan_ok := false; + FOR plan_line IN EXECUTE format($y$EXPLAIN SELECT array_agg(ctid) FROM brintest_bloom WHERE %s $y$, cond) LOOP + IF plan_line LIKE '%Bitmap Heap Scan on brintest_bloom%' THEN + plan_ok := true; + END IF; + END LOOP; + IF NOT plan_ok THEN + RAISE WARNING 'did not get bitmap indexscan plan for %', r; + END IF; + + EXECUTE format($y$SELECT array_agg(ctid) FROM brintest_bloom WHERE %s $y$, cond) + INTO idx_ctids; + + -- run the query using a seqscan + SET enable_seqscan = 1; + SET enable_bitmapscan = 0; + + plan_ok := false; + FOR plan_line IN EXECUTE format($y$EXPLAIN SELECT array_agg(ctid) FROM brintest_bloom WHERE %s $y$, cond) LOOP + IF plan_line LIKE '%Seq Scan on brintest_bloom%' THEN + plan_ok := true; + END IF; + END LOOP; + IF NOT plan_ok THEN + RAISE WARNING 'did not get seqscan plan for %', r; + END IF; + + EXECUTE format($y$SELECT array_agg(ctid) FROM brintest_bloom WHERE %s $y$, cond) + INTO ss_ctids; + + -- make sure both return the same results + count := array_length(idx_ctids, 1); + + IF NOT (count = array_length(ss_ctids, 1) AND + idx_ctids @> ss_ctids AND + idx_ctids <@ ss_ctids) THEN + -- report the results of each scan to make the differences obvious + RAISE WARNING 'something not right in %: count %', r, count; + SET enable_seqscan = 1; + SET enable_bitmapscan = 0; + FOR r2 IN EXECUTE 'SELECT ' || r.colname || ' FROM brintest_bloom WHERE ' || cond LOOP + RAISE NOTICE 'seqscan: %', r2; + END LOOP; + + SET enable_seqscan = 0; + SET enable_bitmapscan = 1; + FOR r2 IN EXECUTE 'SELECT ' || r.colname || ' FROM brintest_bloom WHERE ' || cond LOOP + RAISE NOTICE 'bitmapscan: %', r2; + END LOOP; + END IF; + + -- make sure we found expected number of matches + IF count != r.matches THEN RAISE WARNING 'unexpected number of results % for %', count, r; END IF; + END LOOP; +END; +$x$; + +RESET enable_seqscan; +RESET enable_bitmapscan; + +INSERT INTO brintest_bloom SELECT + repeat(stringu1, 42)::bytea, + substr(stringu1, 1, 1)::"char", + stringu1::name, 142857 * tenthous, + thousand, + twothousand, + repeat(stringu1, 42), + unique1::oid, + (four + 1.0)/(hundred+1), + odd::float8 / (tenthous + 1), + format('%s:00:%s:00:%s:00', to_hex(odd), to_hex(even), to_hex(hundred))::macaddr, + inet '10.2.3.4' + tenthous, + cidr '10.2.3/24' + tenthous, + substr(stringu1, 1, 1)::bpchar, + date '1995-08-15' + tenthous, + time '01:20:30' + thousand * interval '18.5 second', + timestamp '1942-07-23 03:05:09' + tenthous * interval '36.38 hours', + timestamptz '1972-10-10 03:00' + thousand * interval '1 hour', + justify_days(justify_hours(tenthous * interval '12 minutes')), + timetz '01:30:20' + hundred * interval '15 seconds', + tenthous::numeric(36,30) * fivethous * even / (hundred + 1), + 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, + format('%s/%s%s', odd, even, tenthous)::pg_lsn +FROM tenk1 ORDER BY unique2 LIMIT 5 OFFSET 5; + +SELECT brin_desummarize_range('brinidx_bloom', 0); +VACUUM brintest_bloom; -- force a summarization cycle in brinidx + +UPDATE brintest_bloom SET int8col = int8col * int4col; +UPDATE brintest_bloom SET textcol = '' WHERE textcol IS NOT NULL; + +-- Tests for brin_summarize_new_values +SELECT brin_summarize_new_values('brintest_bloom'); -- error, not an index +SELECT brin_summarize_new_values('tenk1_unique1'); -- error, not a BRIN index +SELECT brin_summarize_new_values('brinidx_bloom'); -- ok, no change expected + +-- Tests for brin_desummarize_range +SELECT brin_desummarize_range('brinidx_bloom', -1); -- error, invalid range +SELECT brin_desummarize_range('brinidx_bloom', 0); +SELECT brin_desummarize_range('brinidx_bloom', 0); +SELECT brin_desummarize_range('brinidx_bloom', 100000000); + +-- Test brin_summarize_range +CREATE TABLE brin_summarize_bloom ( + value int +) WITH (fillfactor=10, autovacuum_enabled=false); +CREATE INDEX brin_summarize_bloom_idx ON brin_summarize_bloom USING brin (value) WITH (pages_per_range=2); +-- Fill a few pages +DO $$ +DECLARE curtid tid; +BEGIN + LOOP + INSERT INTO brin_summarize_bloom VALUES (1) RETURNING ctid INTO curtid; + EXIT WHEN curtid > tid '(2, 0)'; + END LOOP; +END; +$$; + +-- summarize one range +SELECT brin_summarize_range('brin_summarize_bloom_idx', 0); +-- nothing: already summarized +SELECT brin_summarize_range('brin_summarize_bloom_idx', 1); +-- summarize one range +SELECT brin_summarize_range('brin_summarize_bloom_idx', 2); +-- nothing: page doesn't exist in table +SELECT brin_summarize_range('brin_summarize_bloom_idx', 4294967295); +-- invalid block number values +SELECT brin_summarize_range('brin_summarize_bloom_idx', -1); +SELECT brin_summarize_range('brin_summarize_bloom_idx', 4294967296); + + +-- test brin cost estimates behave sanely based on correlation of values +CREATE TABLE brin_test_bloom (a INT, b INT); +INSERT INTO brin_test_bloom SELECT x/100,x%100 FROM generate_series(1,10000) x(x); +CREATE INDEX brin_test_bloom_a_idx ON brin_test_bloom USING brin (a) WITH (pages_per_range = 2); +CREATE INDEX brin_test_bloom_b_idx ON brin_test_bloom USING brin (b) WITH (pages_per_range = 2); +VACUUM ANALYZE brin_test_bloom; + +-- Ensure brin index is used when columns are perfectly correlated +EXPLAIN (COSTS OFF) SELECT * FROM brin_test_bloom WHERE a = 1; +-- Ensure brin index is not used when values are not correlated +EXPLAIN (COSTS OFF) SELECT * FROM brin_test_bloom WHERE b = 1; -- 2.26.2 --------------CF71AF65F7C337C37C24B045 Content-Type: text/x-patch; charset=UTF-8; name="0005-bloom-fixes-and-tweaks-20210112.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0005-bloom-fixes-and-tweaks-20210112.patch" ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Extended Statistics set/restore/clear functions. @ 2026-01-15 08:09 Michael Paquier <[email protected]> 0 siblings, 2 replies; 5+ messages in thread From: Michael Paquier @ 2026-01-15 08:09 UTC (permalink / raw) To: Corey Huinker <[email protected]>; +Cc: Tender Wang <[email protected]>; PostgreSQL Hackers <[email protected]> On Wed, Jan 14, 2026 at 12:57:45PM -0500, Corey Huinker wrote: > Some of the header cleanup work required adding utils/typcache.h to > extended_stats.c. Thanks for sending a rebased set. It is a large patch, but most of the changes are super mechanical, making it sort of "simpler" to think about the whole. Anyway, I have begun a review of the backend changes, and found one independent piece that was useful, leading to 32e27bd32082 that I have just applied after some renames and a couple of fixes. Then I have spent a good portion of today looking at the backend changes, with first a focus to extract the "clear" function as it is useful on its own, also because its relation lookup logic is the same as the restore function. And then, this block of code has been giving me a long pause, because it was fishy, and clearly wrong: + /* no need to fetch reloid, we already have it */ + RangeVarGetRelidExtended(makeRangeVar(nspname, + RelationGetRelationName(rel), -1), + ShareUpdateExclusiveLock, 0, + RangeVarCallbackForStats, &locked_table); A shocking thing here is that we build a RangeVar for the relation of the extended stats object based on the *extstat namespace*, not the *relation namespace*, and that we do so *after* opening the extstat catalog. Anyway, I think that we need to redesign that, and for consistency's sake do a RangeVar lookup *before* even trying to touch the stats data or open its catalogs. That would be beneficial on consistency ground, and one piece where I think that patch is designed wrongly is that we should give in input of the new clear and restore functions the *schema* and *relation* names of the table we expect to find, so as we can enforce first a clean RangeVar lookup, then manipulate the stats data. This relates to 688dc6299a5b, as well, except that we would be stuck with an incorrect design after release if we are not careful because the function schema would be stuck in time. This has to be right from the start. All these changes have given me the attached patch for the clear() function. Another piece is that we did not really check that a role has the MAINTAIN rights of the relation where we want to manipulate the extended stats. A final thing is the location of the new SQL function code, let's put that into a new file, named extended_stats_funcs.c in the patch. There is nothing in the new restore and clear functions that require extended_stats.c. Finally, for the clear() part, the attached version addresses everything I have found during my review. We will need to make the restore() part follow the same design model with the Rangevar lookups of the parent relation, or we'll have trouble waiting ahead. -- Michael Attachments: [text/x-diff] v24-0001-Add-pg_clear_extended_stats.patch (22.1K, ../../[email protected]/2-v24-0001-Add-pg_clear_extended_stats.patch) download | inline diff: From 24a7deed008e578e7c7f055cedcefd13d449c130 Mon Sep 17 00:00:00 2001 From: Michael Paquier <[email protected]> Date: Thu, 15 Jan 2026 16:59:18 +0900 Subject: [PATCH v24] Add pg_clear_extended_stats() This function is able to clear the data associated to an extended statistics object, making things so as the object looks as newly-created. The caller of this function needs the following arguments for the stats cleared: - The name of the relation. - The schema name of the relation. - The name of the extended stats object. - The schema name of the extended stats object. The first two parameters are especially important to ensure a consistent lookup at the relation we expect to find for the stats object, relying on a RangeVar lookup where ACL are checks without locking a relation, critical to prevent denial-of-service attacks when using this function. The third and fourth parameters are important as a stats object may be on a different schema than its relation. This has been extracted from a larger patch by the same author, for a piece which is again useful on its own. I have rewritten large portions of it. XXX: Bump catalog version. Author: Corey Huinker <[email protected]> Co-authored-by: Michael Paquier <[email protected]> Discussion: https://postgr.es/m/CADkLM=dpz3KFnqP-dgJ-zvRvtjsa8UZv8wDAQdqho=qN3kX0Zg@mail.gmail.com --- src/include/catalog/pg_proc.dat | 7 + src/backend/statistics/Makefile | 1 + src/backend/statistics/extended_stats_funcs.c | 232 ++++++++++++++++++ src/backend/statistics/meson.build | 1 + src/test/regress/expected/stats_import.out | 146 +++++++++++ src/test/regress/sql/stats_import.sql | 102 ++++++++ doc/src/sgml/func/func-admin.sgml | 29 +++ 7 files changed, 518 insertions(+) create mode 100644 src/backend/statistics/extended_stats_funcs.c diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 2ac69bf2df55..894b6a1b6d6b 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -12618,6 +12618,13 @@ proname => 'gist_translate_cmptype_common', prorettype => 'int2', proargtypes => 'int4', prosrc => 'gist_translate_cmptype_common' }, +# Extended Statistics functions +{ oid => '9948', descr => 'clear statistics on extended statistics object', + proname => 'pg_clear_extended_stats', proisstrict => 'f', provolatile => 'v', + proparallel => 'u', prorettype => 'void', proargtypes => 'text text text text bool', + proargnames => '{schemaname,relname,statistics_schemaname,statistics_name,inherited}', + prosrc => 'pg_clear_extended_stats' }, + # AIO related functions { oid => '6399', descr => 'information about in-progress asynchronous IOs', proname => 'pg_get_aios', prorows => '100', proretset => 't', diff --git a/src/backend/statistics/Makefile b/src/backend/statistics/Makefile index 4672bd90f225..7ff5938b0273 100644 --- a/src/backend/statistics/Makefile +++ b/src/backend/statistics/Makefile @@ -16,6 +16,7 @@ OBJS = \ attribute_stats.o \ dependencies.o \ extended_stats.o \ + extended_stats_funcs.o \ mcv.o \ mvdistinct.o \ relation_stats.o \ diff --git a/src/backend/statistics/extended_stats_funcs.c b/src/backend/statistics/extended_stats_funcs.c new file mode 100644 index 000000000000..9edc6c1a97eb --- /dev/null +++ b/src/backend/statistics/extended_stats_funcs.c @@ -0,0 +1,232 @@ +/*------------------------------------------------------------------------- + * + * extended_stats_funcs.c + * Functions for manipulating extended statistics. + * + * This file includes the set of facilities required to support the direct + * manipulations of extended statistics objects. + * + * Portions Copyright (c) 1996-2026, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * IDENTIFICATION + * src/backend/statistics/extended_stats_funcs.c + * + *------------------------------------------------------------------------- + */ +#include "postgres.h" + +#include "access/heapam.h" +#include "catalog/indexing.h" +#include "catalog/namespace.h" +#include "catalog/pg_database.h" +#include "catalog/pg_statistic_ext.h" +#include "catalog/pg_statistic_ext_data.h" +#include "miscadmin.h" +#include "nodes/makefuncs.h" +#include "statistics/stat_utils.h" +#include "utils/acl.h" +#include "utils/builtins.h" +#include "utils/fmgroids.h" +#include "utils/lsyscache.h" +#include "utils/syscache.h" + + +/* + * Index of the arguments for the SQL functions. + */ +enum extended_stats_argnum +{ + RELSCHEMA_ARG = 0, + RELNAME_ARG, + STATSCHEMA_ARG, + STATNAME_ARG, + INHERITED_ARG, + NUM_EXTENDED_STATS_ARGS, +}; + +/* + * The argument names and type OIDs of the arguments for the SQL + * functions. + */ +static struct StatsArgInfo extarginfo[] = +{ + [RELSCHEMA_ARG] = {"schemaname", TEXTOID}, + [RELNAME_ARG] = {"relname", TEXTOID}, + [STATSCHEMA_ARG] = {"statistics_schemaname", TEXTOID}, + [STATNAME_ARG] = {"statistics_name", TEXTOID}, + [INHERITED_ARG] = {"inherited", BOOLOID}, + [NUM_EXTENDED_STATS_ARGS] = {0}, +}; + +static HeapTuple get_pg_statistic_ext(Relation pg_stext, Oid nspoid, + const char *stxname); +static bool delete_pg_statistic_ext_data(Oid stxoid, bool inherited); + +/* + * Fetch a pg_statistic_ext row by name and namespace OID. + */ +static HeapTuple +get_pg_statistic_ext(Relation pg_stext, Oid nspoid, const char *stxname) +{ + ScanKeyData key[2]; + SysScanDesc scan; + HeapTuple tup; + Oid stxoid = InvalidOid; + + ScanKeyInit(&key[0], + Anum_pg_statistic_ext_stxname, + BTEqualStrategyNumber, + F_NAMEEQ, + CStringGetDatum(stxname)); + ScanKeyInit(&key[1], + Anum_pg_statistic_ext_stxnamespace, + BTEqualStrategyNumber, + F_OIDEQ, + ObjectIdGetDatum(nspoid)); + + /* + * Try to find matching pg_statistic_ext row. + */ + scan = systable_beginscan(pg_stext, + StatisticExtNameIndexId, + true, + NULL, + 2, + key); + + /* Lookup is based on a unique index, so we get either 0 or 1 tuple. */ + tup = systable_getnext(scan); + + if (HeapTupleIsValid(tup)) + stxoid = ((Form_pg_statistic_ext) GETSTRUCT(tup))->oid; + + systable_endscan(scan); + + if (!OidIsValid(stxoid)) + return NULL; + + return SearchSysCacheCopy1(STATEXTOID, ObjectIdGetDatum(stxoid)); +} + +/* + * Remove an existing pg_statistic_ext_data row for a given pg_statistic_ext + * row and "inherited" pair. + */ +static bool +delete_pg_statistic_ext_data(Oid stxoid, bool inherited) +{ + Relation sed = table_open(StatisticExtDataRelationId, RowExclusiveLock); + HeapTuple oldtup; + bool result = false; + + /* Is there already a pg_statistic tuple for this attribute? */ + oldtup = SearchSysCache2(STATEXTDATASTXOID, + ObjectIdGetDatum(stxoid), + BoolGetDatum(inherited)); + + if (HeapTupleIsValid(oldtup)) + { + CatalogTupleDelete(sed, &oldtup->t_self); + ReleaseSysCache(oldtup); + result = true; + } + + table_close(sed, RowExclusiveLock); + + CommandCounterIncrement(); + + return result; +} + +/* + * Delete statistics for the given statistics object. + */ +Datum +pg_clear_extended_stats(PG_FUNCTION_ARGS) +{ + char *relnspname; + char *relname; + char *nspname; + Oid nspoid; + Oid relid; + char *stxname; + bool inherited; + Relation pg_stext; + HeapTuple tup; + Form_pg_statistic_ext stxform; + Oid locked_table = InvalidOid; + + /* relation arguments */ + stats_check_required_arg(fcinfo, extarginfo, RELSCHEMA_ARG); + relnspname = TextDatumGetCString(PG_GETARG_DATUM(RELSCHEMA_ARG)); + stats_check_required_arg(fcinfo, extarginfo, RELNAME_ARG); + relname = TextDatumGetCString(PG_GETARG_DATUM(RELNAME_ARG)); + + /* extended statistics arguments */ + stats_check_required_arg(fcinfo, extarginfo, STATSCHEMA_ARG); + nspname = TextDatumGetCString(PG_GETARG_DATUM(STATSCHEMA_ARG)); + stats_check_required_arg(fcinfo, extarginfo, STATNAME_ARG); + stxname = TextDatumGetCString(PG_GETARG_DATUM(STATNAME_ARG)); + stats_check_required_arg(fcinfo, extarginfo, INHERITED_ARG); + inherited = PG_GETARG_NAME(INHERITED_ARG); + + if (RecoveryInProgress()) + { + ereport(WARNING, + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("recovery is in progress"), + errhint("Statistics cannot be modified during recovery.")); + PG_RETURN_VOID(); + } + + /* + * First open the relation where we expect to find the statistics. This + * is similar to relation and attribute statistics, so as ACL checks are + * done before any locks are taken, even before any attempts related to + * the extended stats object. + */ + relid = RangeVarGetRelidExtended(makeRangeVar(relnspname, relname, -1), + ShareUpdateExclusiveLock, 0, + RangeVarCallbackForStats, &locked_table); + + /* Now check if the namespace of the stats object exists. */ + nspoid = get_namespace_oid(nspname, true); + if (nspoid == InvalidOid) + { + ereport(WARNING, + errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("could not find schema \"%s\"", nspname)); + PG_RETURN_VOID(); + } + + pg_stext = table_open(StatisticExtRelationId, RowExclusiveLock); + tup = get_pg_statistic_ext(pg_stext, nspoid, stxname); + + if (!HeapTupleIsValid(tup)) + { + table_close(pg_stext, RowExclusiveLock); + ereport(WARNING, + errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("could not find extended statistics object \"%s\".\"%s\"", + nspname, stxname)); + PG_RETURN_VOID(); + } + + stxform = (Form_pg_statistic_ext) GETSTRUCT(tup); + + /* + * This should be consistent, based on the lock taken on the table + * when we started. + */ + if (stxform->stxrelid != relid) + elog(ERROR, "cache lookup failed for extended stats %u: found relation %u but expected %u", + stxform->oid, stxform->stxrelid, relid); + + delete_pg_statistic_ext_data(stxform->oid, inherited); + heap_freetuple(tup); + + table_close(pg_stext, RowExclusiveLock); + + PG_RETURN_VOID(); +} diff --git a/src/backend/statistics/meson.build b/src/backend/statistics/meson.build index 5c89f869812c..9a7bf55e3014 100644 --- a/src/backend/statistics/meson.build +++ b/src/backend/statistics/meson.build @@ -4,6 +4,7 @@ backend_sources += files( 'attribute_stats.c', 'dependencies.c', 'extended_stats.c', + 'extended_stats_funcs.c', 'mcv.c', 'mvdistinct.c', 'relation_stats.c', diff --git a/src/test/regress/expected/stats_import.out b/src/test/regress/expected/stats_import.out index 98ce7dc28410..f859a3a6af08 100644 --- a/src/test/regress/expected/stats_import.out +++ b/src/test/regress/expected/stats_import.out @@ -1084,11 +1084,17 @@ SELECT 3, 'tre', (3, 3.3, 'TRE', '2003-03-03', NULL)::stats_import.complex_type, UNION ALL SELECT 4, 'four', NULL, int4range(0,100), NULL; CREATE INDEX is_odd ON stats_import.test(((comp).a % 2 = 1)); +CREATE STATISTICS stats_import.test_stat + ON name, comp, lower(arange), array_length(tags,1) + FROM stats_import.test; -- Generate statistics on table with data ANALYZE stats_import.test; CREATE TABLE stats_import.test_clone ( LIKE stats_import.test ) WITH (autovacuum_enabled = false); CREATE INDEX is_odd_clone ON stats_import.test_clone(((comp).a % 2 = 1)); +CREATE STATISTICS stats_import.test_stat_clone + ON name, comp, lower(arange), array_length(tags,1) + FROM stats_import.test_clone; -- -- Copy stats from test to test_clone, and is_odd to is_odd_clone -- @@ -1342,6 +1348,146 @@ AND attname = 'i'; (1 row) DROP TABLE stats_temp; +-- Tests for pg_clear_extended_stats(). +-- Invalid argument values. +SELECT pg_clear_extended_stats(schemaname => NULL, + relname => 'rel_foo', + statistics_schemaname => 'schema_foo', + statistics_name => 'stat_bar', + inherited => false); +ERROR: argument "schemaname" must not be null +SELECT pg_clear_extended_stats(schemaname => 'schema_foo', + relname => NULL, + statistics_schemaname => 'schema_foo', + statistics_name => 'stat_bar', + inherited => false); +ERROR: argument "relname" must not be null +SELECT pg_clear_extended_stats(schemaname => 'schema_foo', + relname => 'rel_foo', + statistics_schemaname => NULL, + statistics_name => 'stat_bar', + inherited => false); +ERROR: argument "statistics_schemaname" must not be null +SELECT pg_clear_extended_stats(schemaname => 'schema_foo', + relname => 'rel_foo', + statistics_schemaname => 'schema_foo', + statistics_name => NULL, + inherited => false); +ERROR: argument "statistics_name" must not be null +SELECT pg_clear_extended_stats(schemaname => 'schema_foo', + relname => 'rel_foo', + statistics_schemaname => 'schema_foo', + statistics_name => 'stat_bar', + inherited => NULL); +ERROR: argument "inherited" must not be null +-- Missing objects +SELECT pg_clear_extended_stats(schemaname => 'schema_not_exist', + relname => 'test', + statistics_schemaname => 'schema_not_exist', + statistics_name => 'test_stat', + inherited => false); +ERROR: schema "schema_not_exist" does not exist +SELECT pg_clear_extended_stats(schemaname => 'stats_import', + relname => 'table_not_exist', + statistics_schemaname => 'stats_import', + statistics_name => 'test_stat', + inherited => false); +ERROR: relation "stats_import.table_not_exist" does not exist +SELECT pg_clear_extended_stats(schemaname => 'stats_import', + relname => 'test', + statistics_schemaname => 'schema_not_exist', + statistics_name => 'test_stat', + inherited => false); +WARNING: could not find schema "schema_not_exist" + pg_clear_extended_stats +------------------------- + +(1 row) + +SELECT pg_clear_extended_stats(schemaname => 'stats_import', + relname => 'test', + statistics_schemaname => 'stats_import', + statistics_name => 'ext_stats_not_exist', + inherited => false); +WARNING: could not find extended statistics object "stats_import"."ext_stats_not_exist" + pg_clear_extended_stats +------------------------- + +(1 row) + +-- Check that records are removed after a valid clear call. +SELECT COUNT(*) FROM pg_stats_ext AS e + WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'test_stat' AND e.inherited = false; + count +------- + 1 +(1 row) + +SELECT COUNT(*) FROM pg_stats_ext_exprs AS e + WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'test_stat' AND e.inherited = false; + count +------- + 2 +(1 row) + +SELECT pg_catalog.pg_clear_extended_stats( + schemaname => 'stats_import', + relname => 'test', + statistics_schemaname => 'stats_import', + statistics_name => 'test_stat', + inherited => false); + pg_clear_extended_stats +------------------------- + +(1 row) + +SELECT COUNT(*) FROM pg_stats_ext AS e + WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'test_stat' AND e.inherited = false; + count +------- + 0 +(1 row) + +SELECT COUNT(*) FROM pg_stats_ext_exprs AS e + WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'test_stat' AND e.inherited = false; + count +------- + 0 +(1 row) + +-- Check that MAINTAIN is required when clearing statistics. +CREATE ROLE regress_test_extstat_clear; +GRANT ALL ON SCHEMA stats_import TO regress_test_extstat_clear; +SET ROLE regress_test_extstat_clear; +SELECT pg_catalog.pg_clear_extended_stats( + schemaname => 'stats_import', + relname => 'test', + statistics_schemaname => 'stats_import', + statistics_name => 'test_stat', + inherited => false); +ERROR: permission denied for table test +RESET ROLE; +GRANT MAINTAIN ON stats_import.test TO regress_test_extstat_clear; +SET ROLE regress_test_extstat_clear; +SELECT pg_catalog.pg_clear_extended_stats( + schemaname => 'stats_import', + relname => 'test', + statistics_schemaname => 'stats_import', + statistics_name => 'test_stat', + inherited => false); + pg_clear_extended_stats +------------------------- + +(1 row) + +RESET ROLE; +REVOKE MAINTAIN ON stats_import.test FROM regress_test_extstat_clear; +REVOKE ALL ON SCHEMA stats_import FROM regress_test_extstat_clear; +DROP ROLE regress_test_extstat_clear; DROP SCHEMA stats_import CASCADE; NOTICE: drop cascades to 6 other objects DETAIL: drop cascades to type stats_import.complex_type diff --git a/src/test/regress/sql/stats_import.sql b/src/test/regress/sql/stats_import.sql index d140733a7502..e866cdea1b72 100644 --- a/src/test/regress/sql/stats_import.sql +++ b/src/test/regress/sql/stats_import.sql @@ -766,6 +766,10 @@ SELECT 4, 'four', NULL, int4range(0,100), NULL; CREATE INDEX is_odd ON stats_import.test(((comp).a % 2 = 1)); +CREATE STATISTICS stats_import.test_stat + ON name, comp, lower(arange), array_length(tags,1) + FROM stats_import.test; + -- Generate statistics on table with data ANALYZE stats_import.test; @@ -774,6 +778,10 @@ CREATE TABLE stats_import.test_clone ( LIKE stats_import.test ) CREATE INDEX is_odd_clone ON stats_import.test_clone(((comp).a % 2 = 1)); +CREATE STATISTICS stats_import.test_stat_clone + ON name, comp, lower(arange), array_length(tags,1) + FROM stats_import.test_clone; + -- -- Copy stats from test to test_clone, and is_odd to is_odd_clone -- @@ -970,4 +978,98 @@ AND tablename = 'stats_temp' AND inherited = false AND attname = 'i'; DROP TABLE stats_temp; + +-- Tests for pg_clear_extended_stats(). +-- Invalid argument values. +SELECT pg_clear_extended_stats(schemaname => NULL, + relname => 'rel_foo', + statistics_schemaname => 'schema_foo', + statistics_name => 'stat_bar', + inherited => false); +SELECT pg_clear_extended_stats(schemaname => 'schema_foo', + relname => NULL, + statistics_schemaname => 'schema_foo', + statistics_name => 'stat_bar', + inherited => false); +SELECT pg_clear_extended_stats(schemaname => 'schema_foo', + relname => 'rel_foo', + statistics_schemaname => NULL, + statistics_name => 'stat_bar', + inherited => false); +SELECT pg_clear_extended_stats(schemaname => 'schema_foo', + relname => 'rel_foo', + statistics_schemaname => 'schema_foo', + statistics_name => NULL, + inherited => false); +SELECT pg_clear_extended_stats(schemaname => 'schema_foo', + relname => 'rel_foo', + statistics_schemaname => 'schema_foo', + statistics_name => 'stat_bar', + inherited => NULL); +-- Missing objects +SELECT pg_clear_extended_stats(schemaname => 'schema_not_exist', + relname => 'test', + statistics_schemaname => 'schema_not_exist', + statistics_name => 'test_stat', + inherited => false); +SELECT pg_clear_extended_stats(schemaname => 'stats_import', + relname => 'table_not_exist', + statistics_schemaname => 'stats_import', + statistics_name => 'test_stat', + inherited => false); +SELECT pg_clear_extended_stats(schemaname => 'stats_import', + relname => 'test', + statistics_schemaname => 'schema_not_exist', + statistics_name => 'test_stat', + inherited => false); +SELECT pg_clear_extended_stats(schemaname => 'stats_import', + relname => 'test', + statistics_schemaname => 'stats_import', + statistics_name => 'ext_stats_not_exist', + inherited => false); + +-- Check that records are removed after a valid clear call. +SELECT COUNT(*) FROM pg_stats_ext AS e + WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'test_stat' AND e.inherited = false; +SELECT COUNT(*) FROM pg_stats_ext_exprs AS e + WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'test_stat' AND e.inherited = false; +SELECT pg_catalog.pg_clear_extended_stats( + schemaname => 'stats_import', + relname => 'test', + statistics_schemaname => 'stats_import', + statistics_name => 'test_stat', + inherited => false); +SELECT COUNT(*) FROM pg_stats_ext AS e + WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'test_stat' AND e.inherited = false; +SELECT COUNT(*) FROM pg_stats_ext_exprs AS e + WHERE e.statistics_schemaname = 'stats_import' AND + e.statistics_name = 'test_stat' AND e.inherited = false; + +-- Check that MAINTAIN is required when clearing statistics. +CREATE ROLE regress_test_extstat_clear; +GRANT ALL ON SCHEMA stats_import TO regress_test_extstat_clear; +SET ROLE regress_test_extstat_clear; +SELECT pg_catalog.pg_clear_extended_stats( + schemaname => 'stats_import', + relname => 'test', + statistics_schemaname => 'stats_import', + statistics_name => 'test_stat', + inherited => false); +RESET ROLE; +GRANT MAINTAIN ON stats_import.test TO regress_test_extstat_clear; +SET ROLE regress_test_extstat_clear; +SELECT pg_catalog.pg_clear_extended_stats( + schemaname => 'stats_import', + relname => 'test', + statistics_schemaname => 'stats_import', + statistics_name => 'test_stat', + inherited => false); +RESET ROLE; +REVOKE MAINTAIN ON stats_import.test FROM regress_test_extstat_clear; +REVOKE ALL ON SCHEMA stats_import FROM regress_test_extstat_clear; +DROP ROLE regress_test_extstat_clear; + DROP SCHEMA stats_import CASCADE; diff --git a/doc/src/sgml/func/func-admin.sgml b/doc/src/sgml/func/func-admin.sgml index 2896cd9e4290..e7ea16f73b31 100644 --- a/doc/src/sgml/func/func-admin.sgml +++ b/doc/src/sgml/func/func-admin.sgml @@ -2165,6 +2165,35 @@ SELECT pg_restore_attribute_stats( </para> </entry> </row> + <row> + <entry role="func_table_entry"> + <para role="func_signature"> + <indexterm> + <primary>pg_clear_extended_stats</primary> + </indexterm> + <function>pg_clear_extended_stats</function> ( + <parameter>schemaname</parameter> <type>name</type>, + <parameter>relname</parameter> <type>name</type>, + <parameter>statistics_schemaname</parameter> <type>name</type>, + <parameter>statistics_name</parameter> <type>name</type>, + <parameter>inherited</parameter> <type>boolean</type> ) + <returnvalue>void</returnvalue> + </para> + <para> + Clears data of an extended statistics object, as though the object + was newly-created. The required arguments are + <literal>schemaname</literal> and <literal>relname</literal> to + specify the schema and table name of the relation whose statistics + are cleared, as well as <literal>statistics_schemaname</literal> + and <literal>statistics_name</literal> to specify the schema and + extended statistics name of the extended statistics object to clear. + </para> + <para> + The caller must have the <literal>MAINTAIN</literal> privilege on + the table or be the owner of the database. + </para> + </entry> + </row> </tbody> </tgroup> </table> -- 2.51.0 [application/pgp-signature] signature.asc (833B, ../../[email protected]/3-signature.asc) download ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Extended Statistics set/restore/clear functions. @ 2026-01-15 08:57 Chao Li <[email protected]> parent: Michael Paquier <[email protected]> 1 sibling, 1 reply; 5+ messages in thread From: Chao Li @ 2026-01-15 08:57 UTC (permalink / raw) To: Michael Paquier <[email protected]>; +Cc: Corey Huinker <[email protected]>; Tender Wang <[email protected]>; PostgreSQL Hackers <[email protected]> > On Jan 15, 2026, at 16:09, Michael Paquier <[email protected]> wrote: > > On Wed, Jan 14, 2026 at 12:57:45PM -0500, Corey Huinker wrote: >> Some of the header cleanup work required adding utils/typcache.h to >> extended_stats.c. > > Thanks for sending a rebased set. It is a large patch, but most of > the changes are super mechanical, making it sort of "simpler" to think > about the whole. > > Anyway, I have begun a review of the backend changes, and found one > independent piece that was useful, leading to 32e27bd32082 that I have > just applied after some renames and a couple of fixes. > > Then I have spent a good portion of today looking at the backend > changes, with first a focus to extract the "clear" function as it is > useful on its own, also because its relation lookup logic is the same > as the restore function. > > And then, this block of code has been giving me a long pause, because > it was fishy, and clearly wrong: > + /* no need to fetch reloid, we already have it */ > + RangeVarGetRelidExtended(makeRangeVar(nspname, > + RelationGetRelationName(rel), -1), > + ShareUpdateExclusiveLock, 0, > + RangeVarCallbackForStats, &locked_table); > > A shocking thing here is that we build a RangeVar for the relation of > the extended stats object based on the *extstat namespace*, not the > *relation namespace*, and that we do so *after* opening the extstat > catalog. Anyway, I think that we need to redesign that, and for > consistency's sake do a RangeVar lookup *before* even trying to touch > the stats data or open its catalogs. That would be beneficial on > consistency ground, and one piece where I think that patch is designed > wrongly is that we should give in input of the new clear and restore > functions the *schema* and *relation* names of the table we expect to > find, so as we can enforce first a clean RangeVar lookup, then > manipulate the stats data. This relates to 688dc6299a5b, as well, > except that we would be stuck with an incorrect design after release > if we are not careful because the function schema would be stuck in > time. This has to be right from the start. > > All these changes have given me the attached patch for the clear() > function. Another piece is that we did not really check that a role > has the MAINTAIN rights of the relation where we want to manipulate > the extended stats. > > A final thing is the location of the new SQL function code, let's put > that into a new file, named extended_stats_funcs.c in the patch. > There is nothing in the new restore and clear functions that require > extended_stats.c. > > Finally, for the clear() part, the attached version addresses > everything I have found during my review. We will need to make the > restore() part follow the same design model with the Rangevar lookups > of the parent relation, or we'll have trouble waiting ahead. > -- > Michael > <v24-0001-Add-pg_clear_extended_stats.patch> Hi Michael, Here are some comments for v24: 1. ``` + inherited = PG_GETARG_NAME(INHERITED_ARG); ``` Should this use PG_GETARG_BOOL()? 2 ``` proparallel => 'u', prorettype => 'void', proargtypes => 'text text text text bool’, ``` Should we define proargtypes as “name name name name bool”? As the doc change mentions type “name” for the first 4 parameters. Maybe you want to avoid that because Name is a structure. Anyway, I don’t have a strong opinion here. 3 - extended_stats_funcs.c ``` * Portions Copyright (c) 1996-2026, PostgreSQL Global Development Group ``` This is a brand new file, so the copyright year should be just 2026. If you search for “2025-2026”, you will get a bunch of files, they should be created in 2025. 4 - Given comment 1, no test fails. I think that’s because all test cases use inherited = false. Maybe we need a test case for inherited = true. Best regards, -- Chao Li (Evan) HighGo Software Co., Ltd. https://www.highgo.com/ ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Extended Statistics set/restore/clear functions. @ 2026-01-15 08:59 Corey Huinker <[email protected]> parent: Michael Paquier <[email protected]> 1 sibling, 0 replies; 5+ messages in thread From: Corey Huinker @ 2026-01-15 08:59 UTC (permalink / raw) To: Michael Paquier <[email protected]>; +Cc: Tender Wang <[email protected]>; PostgreSQL Hackers <[email protected]> > Anyway, I have begun a review of the backend changes, and found one > independent piece that was useful, leading to 32e27bd32082 that I have > just applied after some renames and a couple of fixes. > :) > > A shocking thing here is that we build a RangeVar for the relation of > the extended stats object based on the *extstat namespace*, not the > *relation namespace*, and that we do so *after* opening the extstat > catalog. Anyway, I think that we need to redesign that, and for > consistency's sake do a RangeVar lookup *before* even trying to touch > the stats data or open its catalogs. That would be beneficial on > consistency ground, and one piece where I think that patch is designed > wrongly is that we should give in input of the new clear and restore > functions the *schema* and *relation* names of the table we expect to > find, so as we can enforce first a clean RangeVar lookup, then > manipulate the stats data. This relates to 688dc6299a5b, as well, > except that we would be stuck with an incorrect design after release > if we are not careful because the function schema would be stuck in > time. This has to be right from the start. > Correct, that was done in the v7 patchset to conform to 688dc6299a5b. All four names are available through pg_stats_ext, so the additional requirements for pg_dump may not be a an issue. However, I do have some vague memory that all four names was a problem in some way beyond just giving the parameters more chances to disagree with reality. We'll find out soon enough... > All these changes have given me the attached patch for the clear() > function. Another piece is that we did not really check that a role > has the MAINTAIN rights of the relation where we want to manipulate > the extended stats. > > A final thing is the location of the new SQL function code, let's put > that into a new file, named extended_stats_funcs.c in the patch. > There is nothing in the new restore and clear functions that require > extended_stats.c. > > Finally, for the clear() part, the attached version addresses > everything I have found during my review. We will need to make the > restore() part follow the same design model with the Rangevar lookups > of the parent relation, or we'll have trouble waiting ahead. > -- > Michael > +1 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Extended Statistics set/restore/clear functions. @ 2026-01-15 09:35 Michael Paquier <[email protected]> parent: Chao Li <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Michael Paquier @ 2026-01-15 09:35 UTC (permalink / raw) To: Chao Li <[email protected]>; +Cc: Corey Huinker <[email protected]>; Tender Wang <[email protected]>; PostgreSQL Hackers <[email protected]> On Thu, Jan 15, 2026 at 04:57:00PM +0800, Chao Li wrote: > 4 - Given comment 1, no test fails. I think that’s because all test > cases use inherited = false. Maybe we need a test case for inherited > = true. Yeah, I was planning to add one but ran out of steam today. I should have mentioned that earlier. -- Michael Attachments: [application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc) download ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-01-15 09:35 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-12-16 20:24 [PATCH 4/8] BRIN bloom indexes Tomas Vondra <[email protected]> 2026-01-15 08:09 Re: Extended Statistics set/restore/clear functions. Michael Paquier <[email protected]> 2026-01-15 08:57 ` Re: Extended Statistics set/restore/clear functions. Chao Li <[email protected]> 2026-01-15 09:35 ` Re: Extended Statistics set/restore/clear functions. Michael Paquier <[email protected]> 2026-01-15 08:59 ` Re: Extended Statistics set/restore/clear functions. Corey Huinker <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox