68.2. 内建操作符类

68.2.1. 操作符类参数

核心PostgreSQL发布包括了 表 68.1中所示的 BRIN操作符类。

minmax操作符类存储范围内被索引列中出现的最小和最大值。 inclusion操作符类存储包括了范围内被索引列中值的一个值。 bloom操作符类对范围内的所有值构建一个布隆过滤器。 minmax-multi操作符类存储多个最小和最大值,表示范围内出现在索引列里的值。

表 68.1. 内建 BRIN 操作符类

名称可索引操作符
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)
>= (varbit,varbit)

68.2.1. 操作符类参数

一些内置操作符类允许指定参数,以便控制操作符类的行为。 每个操作符类都有自己允许的参数集。 只有bloomminmax-multi操作符类允许指定参数:

bloom 操作符类接受这些参数:

n_distinct_per_range

定义块范围内不同的非空值的估计数量,由BRIN 布隆索引使用,用于确定布隆过滤器的大小。 它的行为类似于ALTER TABLEn_distinct选项。 当设置为正值时,每个块范围假定包含该数量的不同的非空值。 当设置为负值时,该值必须大于或等于-1,不同的非空值的数量假定与块范围内的元组的最大可能数量(大约每个块290行)成线性增长。 默认值是-0.1,不同的非空值的最小数量是16

false_positive_rate

定义由BRIN 布隆索引使用的预期假阳性率,用于确定布隆过滤器的大小。 值必须在0.0001和0.25之间。默认值为0.01,即1%的假阳性率。

minmax-multi 操作符类接受这些参数:

values_per_range

定义由BRIN minmax索引所存储的值的最大数量,以汇总块的范围。 每个值可以表示一个点,或者表示区间的边界。 值必须介于8和256之间,默认值为32。