PostgreSQL 9.5 new feature - lets BRIN be used with R-Tree-like indexing strategies For inclusion opclasses
背景
PostgreSQL 9.5 新增了BRIN索引访问方法，存储了每个连续的数据块的边界值信息，例如最大值，最小值，是否包含空值。
非常适合流式数据自增列的（例如序列，时间戳）索引，索引可以做到很小，而且定位比较精准，适合B-Tree类似的扫描。
参见：
http://blog.163.com/digoal@126/blog/static/163877040201531931956500/
PostgreSQL 9.5 最近又增加了BRIN的类似R-Tree的索引策略，支持包含系的操作符。
目前实现了cidr/inet, range, box类型的包含，相交，在旁，等操作符。
未来还会添加更多类型的操作符，PostGIS中的一些类型也可能会实现BRIN的访问方法。
Add BRIN infrastructure for "inclusion" opclasses
This lets BRIN be used with R-Tree-like indexing strategies.
Also provided are operator classes for range types, box and inet/cidr.
The infrastructure provided here should be sufficient to create operator
classes for similar datatypes; for instance, opclasses for PostGIS
geometries should be doable, though we didn't try to implement one.
(A box/point opclass was also submitted, but we ripped it out before
commit because the handling of floating point comparisons in existing
code is inconsistent and would generate corrupt indexes.)
Author: Emre Hasegeli. Cosmetic changes by me
Review: Andreas Karlsson
BRIN索引访问方法目前支持的操作符如下
postgres=# create or replace function gettypname(oid) returns name as $$
postgres$# select typname from pg_type where oid=$1;
postgres$# $$ language sql strict;
CREATE FUNCTION
postgres=# select oprname,gettypname(oprleft),gettypname(oprright) from pg_operator where oid in (select amopopr from pg_amop where amopmethod=(select oid from pg_am where amname='brin'));
oprname | gettypname | gettypname
---------+-------------+-------------
= | int4 | int8
< | int4 | int8
> | int4 | int8
<= | int4 | int8
>= | int4 | int8
= | char | char
= | name | name
= | int2 | int2
< | int2 | int2
= | int4 | int4
< | int4 | int4
= | text | text
= | tid | tid
< | tid | tid
> | tid | tid
<= | tid | tid
>= | tid | tid
= | int8 | int8
< | int8 | int8
> | int8 | int8
<= | int8 | int8
>= | int8 | int8
= | int8 | int4
< | int8 | int4
> | int8 | int4
<= | int8 | int4
>= | int8 | int4
<< | box | box 严格在左
&< | box | box Does not extend to the right of?
&> | box | box Does not extend to the left of?
>> | box | box 严格在右
<@ | box | box Contained in or on?
@> | box | box Contains?
~= | box | box Same as?
&& | box | box Overlaps? (One point in common makes this true.)
@> | box | point
> | int2 | int2
> | int4 | int4
<= | int2 | int2
<= | int4 | int4
>= | int2 | int2
>= | int4 | int4
= | int2 | int4
= | int4 | int2
< | int2 | int4
< | int4 | int2
> | int2 | int4
> | int4 | int2
<= | int2 | int4
<= | int4 | int2
>= | int2 | int4
>= | int4 | int2
= | abstime | abstime
< | abstime | abstime
> | abstime | abstime
<= | abstime | abstime
>= | abstime | abstime
= | reltime | reltime
< | reltime | reltime
> | reltime | reltime
<= | reltime | reltime
>= | reltime | reltime
= | oid | oid
< | oid | oid
> | oid | oid
<= | oid | oid
>= | oid | oid
= | float4 | float4
< | float4 | float4
> | float4 | float4
<= | float4 | float4
>= | float4 | float4
< | char | char
<= | char | char
> | char | char
>= | char | char
< | name | name
<= | name | name
> | name | name
>= | name | name
< | text | text
<= | text | text
> | text | text
>= | text | text
= | float8 | float8
< | float8 | float8
<= | float8 | float8
> | float8 | float8
>= | float8 | float8
= | bpchar | bpchar
< | bpchar | bpchar
<= | bpchar | bpchar
> | bpchar | bpchar
>= | bpchar | bpchar
= | date | date
< | date | date
<= | date | date
> | date | date
>= | date | date
= | time | time
< | time | time
<= | time | time
> | time | time
>= | time | time
= | timetz | timetz
< | timetz | timetz
<= | timetz | timetz
> | timetz | timetz
>= | timetz | timetz
= | float4 | float8
< | float4 | float8
> | float4 | float8
<= | float4 | float8
>= | float4 | float8
= | float8 | float4
< | float8 | float4
> | float8 | float4
<= | float8 | float4
>= | float8 | float4
= | timestamptz | timestamptz
< | timestamptz | timestamptz
<= | timestamptz | timestamptz
> | timestamptz | timestamptz
>= | timestamptz | timestamptz
= | interval | interval
< | interval | interval
<= | interval | interval
> | interval | interval
>= | interval | interval
= | macaddr | macaddr
< | macaddr | macaddr
<= | macaddr | macaddr
> | macaddr | macaddr
>= | macaddr | macaddr
= | inet | inet
< | inet | inet
<= | inet | inet
> | inet | inet
>= | inet | inet
<< | inet | inet is contained by
<<= | inet | inet is contained by or equals
>> | inet | inet contains
>>= | inet | inet contains or equals
&& | inet | inet contains or is contained by
= | numeric | numeric
< | numeric | numeric
<= | numeric | numeric
> | numeric | numeric
>= | numeric | numeric
= | bit | bit
< | bit | bit
> | bit | bit
<= | bit | bit
>= | bit | bit
= | varbit | varbit
< | varbit | varbit
> | varbit | varbit
<= | varbit | varbit
>= | varbit | varbit
= | int2 | int8
< | int2 | int8
> | int2 | int8
<= | int2 | int8
>= | int2 | int8
= | int8 | int2
< | int8 | int2
> | int8 | int2
<= | int8 | int2
>= | int8 | int2
= | bytea | bytea
< | bytea | bytea
<= | bytea | bytea
> | bytea | bytea
>= | bytea | bytea
= | timestamp | timestamp
< | timestamp | timestamp
<= | timestamp | timestamp
> | timestamp | timestamp
>= | timestamp | timestamp
< | date | timestamp
<= | date | timestamp
= | date | timestamp
>= | date | timestamp
> | date | timestamp
< | date | timestamptz
<= | date | timestamptz
= | date | timestamptz
>= | date | timestamptz
> | date | timestamptz
< | timestamp | date
<= | timestamp | date
= | timestamp | date
>= | timestamp | date
> | timestamp | date
< | timestamptz | date
<= | timestamptz | date
= | timestamptz | date
>= | timestamptz | date
> | timestamptz | date
< | timestamp | timestamptz
<= | timestamp | timestamptz
= | timestamp | timestamptz
>= | timestamp | timestamptz
> | timestamp | timestamptz
< | timestamptz | timestamp
<= | timestamptz | timestamp
= | timestamptz | timestamp
>= | timestamptz | timestamp
> | timestamptz | timestamp
<<| | box | box Is strictly below?
&<| | box | box Does not extend above?
|&> | box | box Does not extend below?
|>> | box | box Is strictly above?
= | uuid | uuid
< | uuid | uuid
> | uuid | uuid
<= | uuid | uuid
>= | uuid | uuid
= | pg_lsn | pg_lsn
< | pg_lsn | pg_lsn
> | pg_lsn | pg_lsn
<= | pg_lsn | pg_lsn
>= | pg_lsn | pg_lsn
= | anyrange | anyrange
< | anyrange | anyrange
<= | anyrange | anyrange
>= | anyrange | anyrange
> | anyrange | anyrange
&& | anyrange | anyrange overlap (have points in common)
@> | anyrange | anyelement contains element
@> | anyrange | anyrange contains range
<@ | anyrange | anyrange range is contained by
<< | anyrange | anyrange strictly left of
>> | anyrange | anyrange strictly right of
&< | anyrange | anyrange does not extend to the right of
&> | anyrange | anyrange does not extend to the left of
-|- | anyrange | anyrange is adjacent to
(237 rows)
操作符含义详见：
http://www.postgresql.org/docs/devel/static/functions.html
参考
1. http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=b0b7be61337fc64147f2ad0af5bf2c0e6b8a709f
2. http://blog.163.com/digoal@126/blog/static/163877040201531931956500/