Postgresql 中文操作指南
71.2. Built-in Operator Classes #
PostgreSQL 核心发行版包括 Table 71.1中所示的 BRIN 运算符类。
minmax 运算符类存储了在范围中出现在索引列中的最小值和最大值。inclusion 运算符类存储了一个值，该值包括了在范围中出现在索引列内的值。bloom 运算符类为范围内的所有值构建一个布隆过滤器。minmax-multi 运算符类存储多个最小值和最大值，表示在范围中出现在索引列内的值。
Table 71.1. Built-in BRIN Operator Classes
Name |
Indexable Operators |
bit_minmax_ops |
= (bit,bit) |
< (bit,bit) |
> (bit,bit) |
⇐ (bit,bit) |
>= (bit,bit) |
box_inclusion_ops |
@> (box,point) |
<< (box,box) |
&< (box,box) |
&> (box,box) |
>> (box,box) |
<@ (box,box) |
@> (box,box) |
~= (box,box) |
&& (box,box) |
_<< |
(box,box)_ |
_&< |
(box,box)_ |
_ |
&> (box,box)_ |
_ |
>> (box,box)_ |
bpchar_bloom_ops |
= (character,character) |
bpchar_minmax_ops |
= (character,character) |
< (character,character) |
⇐ (character,character) |
> (character,character) |
>= (character,character) |
bytea_bloom_ops |
= (bytea,bytea) |
bytea_minmax_ops |
= (bytea,bytea) |
< (bytea,bytea) |
⇐ (bytea,bytea) |
> (bytea,bytea) |
>= (bytea,bytea) |
char_bloom_ops |
= ("char","char") |
char_minmax_ops |
= ("char","char") |
< ("char","char") |
⇐ ("char","char") |
> ("char","char") |
>= ("char","char") |
date_bloom_ops |
= (date,date) |
date_minmax_ops |
= (date,date) |
< (date,date) |
⇐ (date,date) |
> (date,date) |
>= (date,date) |
date_minmax_multi_ops |
= (date,date) |
< (date,date) |
⇐ (date,date) |
> (date,date) |
>= (date,date) |
float4_bloom_ops |
= (float4,float4) |
float4_minmax_ops |
= (float4,float4) |
< (float4,float4) |
> (float4,float4) |
⇐ (float4,float4) |
>= (float4,float4) |
float4_minmax_multi_ops |
= (float4,float4) |
< (float4,float4) |
> (float4,float4) |
⇐ (float4,float4) |
>= (float4,float4) |
float8_bloom_ops |
= (float8,float8) |
float8_minmax_ops |
= (float8,float8) |
< (float8,float8) |
⇐ (float8,float8) |
> (float8,float8) |
>= (float8,float8) |
float8_minmax_multi_ops |
= (float8,float8) |
< (float8,float8) |
⇐ (float8,float8) |
> (float8,float8) |
>= (float8,float8) |
inet_inclusion_ops |
<< (inet,inet) |
<⇐ (inet,inet) |
>> (inet,inet) |
>>= (inet,inet) |
= (inet,inet) |
&& (inet,inet) |
inet_bloom_ops |
= (inet,inet) |
inet_minmax_ops |
= (inet,inet) |
< (inet,inet) |
⇐ (inet,inet) |
> (inet,inet) |
>= (inet,inet) |
inet_minmax_multi_ops |
= (inet,inet) |
< (inet,inet) |
⇐ (inet,inet) |
> (inet,inet) |
>= (inet,inet) |
int2_bloom_ops |
= (int2,int2) |
int2_minmax_ops |
= (int2,int2) |
< (int2,int2) |
> (int2,int2) |
⇐ (int2,int2) |
>= (int2,int2) |
int2_minmax_multi_ops |
= (int2,int2) |
< (int2,int2) |
> (int2,int2) |
⇐ (int2,int2) |
>= (int2,int2) |
int4_bloom_ops |
= (int4,int4) |
int4_minmax_ops |
= (int4,int4) |
< (int4,int4) |
> (int4,int4) |
⇐ (int4,int4) |
>= (int4,int4) |
int4_minmax_multi_ops |
= (int4,int4) |
< (int4,int4) |
> (int4,int4) |
⇐ (int4,int4) |
>= (int4,int4) |
int8_bloom_ops |
= (bigint,bigint) |
int8_minmax_ops |
= (bigint,bigint) |
< (bigint,bigint) |
> (bigint,bigint) |
⇐ (bigint,bigint) |
>= (bigint,bigint) |
int8_minmax_multi_ops |
= (bigint,bigint) |
< (bigint,bigint) |
> (bigint,bigint) |
⇐ (bigint,bigint) |
>= (bigint,bigint) |
interval_bloom_ops |
= (interval,interval) |
interval_minmax_ops |
= (interval,interval) |
< (interval,interval) |
⇐ (interval,interval) |
> (interval,interval) |
>= (interval,interval) |
interval_minmax_multi_ops |
= (interval,interval) |
< (interval,interval) |
⇐ (interval,interval) |
> (interval,interval) |
>= (interval,interval) |
macaddr_bloom_ops |
= (macaddr,macaddr) |
macaddr_minmax_ops |
= (macaddr,macaddr) |
< (macaddr,macaddr) |
⇐ (macaddr,macaddr) |
> (macaddr,macaddr) |
>= (macaddr,macaddr) |
macaddr_minmax_multi_ops |
= (macaddr,macaddr) |
< (macaddr,macaddr) |
⇐ (macaddr,macaddr) |
> (macaddr,macaddr) |
>= (macaddr,macaddr) |
macaddr8_bloom_ops |
= (macaddr8,macaddr8) |
macaddr8_minmax_ops |
= (macaddr8,macaddr8) |
< (macaddr8,macaddr8) |
⇐ (macaddr8,macaddr8) |
> (macaddr8,macaddr8) |
>= (macaddr8,macaddr8) |
macaddr8_minmax_multi_ops |
= (macaddr8,macaddr8) |
< (macaddr8,macaddr8) |
⇐ (macaddr8,macaddr8) |
> (macaddr8,macaddr8) |
>= (macaddr8,macaddr8) |
name_bloom_ops |
= (name,name) |
name_minmax_ops |
= (name,name) |
< (name,name) |
⇐ (name,name) |
> (name,name) |
>= (name,name) |
numeric_bloom_ops |
= (numeric,numeric) |
numeric_minmax_ops |
= (numeric,numeric) |
< (numeric,numeric) |
⇐ (numeric,numeric) |
> (numeric,numeric) |
>= (numeric,numeric) |
numeric_minmax_multi_ops |
= (numeric,numeric) |
< (numeric,numeric) |
⇐ (numeric,numeric) |
> (numeric,numeric) |
>= (numeric,numeric) |
oid_bloom_ops |
= (oid,oid) |
oid_minmax_ops |
= (oid,oid) |
< (oid,oid) |
> (oid,oid) |
⇐ (oid,oid) |
>= (oid,oid) |
oid_minmax_multi_ops |
= (oid,oid) |
< (oid,oid) |
> (oid,oid) |
⇐ (oid,oid) |
>= (oid,oid) |
pg_lsn_bloom_ops |
= (pg_lsn,pg_lsn) |
pg_lsn_minmax_ops |
= (pg_lsn,pg_lsn) |
< (pg_lsn,pg_lsn) |
> (pg_lsn,pg_lsn) |
⇐ (pg_lsn,pg_lsn) |
>= (pg_lsn,pg_lsn) |
pg_lsn_minmax_multi_ops |
= (pg_lsn,pg_lsn) |
< (pg_lsn,pg_lsn) |
> (pg_lsn,pg_lsn) |
⇐ (pg_lsn,pg_lsn) |
>= (pg_lsn,pg_lsn) |
range_inclusion_ops |
= (anyrange,anyrange) |
< (anyrange,anyrange) |
⇐ (anyrange,anyrange) |
>= (anyrange,anyrange) |
> (anyrange,anyrange) |
&& (anyrange,anyrange) |
@> (anyrange,anyelement) |
@> (anyrange,anyrange) |
<@ (anyrange,anyrange) |
<< (anyrange,anyrange) |
>> (anyrange,anyrange) |
&< (anyrange,anyrange) |
&> (anyrange,anyrange) |
_- |
- (anyrange,anyrange)_ |
text_bloom_ops |
= (text,text) |
text_minmax_ops |
= (text,text) |
< (text,text) |
⇐ (text,text) |
> (text,text) |
>= (text,text) |
tid_bloom_ops |
= (tid,tid) |
tid_minmax_ops |
= (tid,tid) |
< (tid,tid) |
> (tid,tid) |
⇐ (tid,tid) |
>= (tid,tid) |
tid_minmax_multi_ops |
= (tid,tid) |
< (tid,tid) |
> (tid,tid) |
⇐ (tid,tid) |
>= (tid,tid) |
timestamp_bloom_ops |
= (timestamp,timestamp) |
timestamp_minmax_ops |
= (timestamp,timestamp) |
< (timestamp,timestamp) |
⇐ (timestamp,timestamp) |
> (timestamp,timestamp) |
>= (timestamp,timestamp) |
timestamp_minmax_multi_ops |
= (timestamp,timestamp) |
< (timestamp,timestamp) |
⇐ (timestamp,timestamp) |
> (timestamp,timestamp) |
>= (timestamp,timestamp) |
timestamptz_bloom_ops |
= (timestamptz,timestamptz) |
timestamptz_minmax_ops |
= (timestamptz,timestamptz) |
< (timestamptz,timestamptz) |
⇐ (timestamptz,timestamptz) |
> (timestamptz,timestamptz) |
>= (timestamptz,timestamptz) |
timestamptz_minmax_multi_ops |
= (timestamptz,timestamptz) |
< (timestamptz,timestamptz) |
⇐ (timestamptz,timestamptz) |
> (timestamptz,timestamptz) |
>= (timestamptz,timestamptz) |
time_bloom_ops |
= (time,time) |
time_minmax_ops |
= (time,time) |
< (time,time) |
⇐ (time,time) |
> (time,time) |
>= (time,time) |
time_minmax_multi_ops |
= (time,time) |
< (time,time) |
⇐ (time,time) |
> (time,time) |
>= (time,time) |
timetz_bloom_ops |
= (timetz,timetz) |
timetz_minmax_ops |
= (timetz,timetz) |
< (timetz,timetz) |
⇐ (timetz,timetz) |
> (timetz,timetz) |
>= (timetz,timetz) |
timetz_minmax_multi_ops |
= (timetz,timetz) |
< (timetz,timetz) |
⇐ (timetz,timetz) |
> (timetz,timetz) |
>= (timetz,timetz) |
uuid_bloom_ops |
= (uuid,uuid) |
uuid_minmax_ops |
= (uuid,uuid) |
< (uuid,uuid) |
> (uuid,uuid) |
⇐ (uuid,uuid) |
>= (uuid,uuid) |
uuid_minmax_multi_ops |
= (uuid,uuid) |
< (uuid,uuid) |
> (uuid,uuid) |
⇐ (uuid,uuid) |
>= (uuid,uuid) |
varbit_minmax_ops |
= (varbit,varbit) |
< (varbit,varbit) |
> (varbit,varbit) |
⇐ (varbit,varbit) |
71.2.1. Operator Class Parameters #
某些内置运算符类别允许指定影响运算符类别行为的参数。每个运算符类别都有其自己的一组允许的参数。只有 bloom 和 minmax-multi 运算符类别允许指定参数：
bloom 运算符类别接受以下参数：
-
n_distinct_per_range
-
定义 BRIN 布隆索引用于调整布隆过滤器的估计唯一非空值的数目。它与 ALTER TABLE 的 n_distinct 选项的工作方式相似。将该值设为正值时，将假定每个块范围都包含此数量的唯一非空值。将该值设为负值时，该值必须大于或等于 -1，将假定唯一非空值的数目随着块范围中元组的最大可能数目呈线性增长（每个块大约有 290 行）。默认值是 -0.1 ，唯一非空值的最小数目是 16 。
-
-
false_positive_rate
-
定义 BRIN bloom 索引用来调整 Bloom 过滤器的期望误报率。值必须在 0.0001 和 0.25 之间。默认值为 0.01，即 1% 的误报率。
-
minmax-multi 运算符类别接受以下参数：
-
values_per_range
-
定义 BRIN minmax 索引用来汇总块范围时存储的最大值数量。每个值可能代表一个点，或者一个区间的边界。值必须在 8 和 256 之间，默认值为 32。
-