Postgresql 中文操作指南
F.9. btree_gist — GiST operator classes with B-tree behavior #
btree_gist 提供了 GiST 索引操作符类,为数据类型 int2、int4、int8、float4、float8、numeric、timestamp with time zone、timestamp without time zone、time with time zone、time without time zone、date、interval、oid、money、char、varchar、text、bytea、bit、varbit、macaddr、macaddr8、inet、cidr、uuid、bool 和所有 enum 类型实现等效于 B 树的行为。
btree_gist provides GiST index operator classes that implement B-tree equivalent behavior for the data types int2, int4, int8, float4, float8, numeric, timestamp with time zone, timestamp without time zone, time with time zone, time without time zone, date, interval, oid, money, char, varchar, text, bytea, bit, varbit, macaddr, macaddr8, inet, cidr, uuid, bool and all enum types.
一般来说,这些操作符类不会优于等效的标准 B 树索引方法,并且它们缺少标准 B 树代码的一项主要功能:强制唯一性。但是,它们提供了一些 B 树索引不可用的其他功能,如下文所述。此外,当需要多列 GiST 索引(其中某些列的数据类型只能通过 GiST 索引,但其他列只是简单的数据类型)时,这些操作符类很有用。最后,这些操作符类对于 GiST 测试以及作为开发其他 GiST 操作符类的基础很有用。
In general, these operator classes will not outperform the equivalent standard B-tree index methods, and they lack one major feature of the standard B-tree code: the ability to enforce uniqueness. However, they provide some other features that are not available with a B-tree index, as described below. Also, these operator classes are useful when a multicolumn GiST index is needed, wherein some of the columns are of data types that are only indexable with GiST but other columns are just simple data types. Lastly, these operator classes are useful for GiST testing and as a base for developing other GiST operator classes.
除了典型的 B 树搜索运算符之外,btree_gist 还为 <> (“不等于”) 提供索引支持。这可能与 exclusion constraint 结合使用,如下所述。
In addition to the typical B-tree search operators, btree_gist also provides index support for <> (“not equals”). This may be useful in combination with an exclusion constraint, as described below.
同样,对于具有自然距离度量的类型,btree_gist 定义了距离运算符 <→,并使用此运算符为最近邻搜索提供 GiST 索引支持。为 int2、int4、int8、float4、float8、timestamp with time zone、timestamp without time zone、time without time zone、date、interval、oid 和 money 提供了距离运算符。
Also, for data types for which there is a natural distance metric, btree_gist defines a distance operator <→, and provides GiST index support for nearest-neighbor searches using this operator. Distance operators are provided for int2, int4, int8, float4, float8, timestamp with time zone, timestamp without time zone, time without time zone, date, interval, oid, and money.
此模块被认为是“受信任的”,也就是说,它可以由在当前数据库上具有 CREATE 权限的非超级用户安装。
This module is considered “trusted”, that is, it can be installed by non-superusers who have CREATE privilege on the current database.
F.9.1. Example Usage #
使用 btree_gist 代替 btree 的简单示例:
Simple example using btree_gist instead of btree:
CREATE TABLE test (a int4);
-- create index
CREATE INDEX testidx ON test USING GIST (a);
-- query
SELECT * FROM test WHERE a < 10;
-- nearest-neighbor search: find the ten entries closest to "42"
SELECT *, a <-> 42 AS dist FROM test ORDER BY a <-> 42 LIMIT 10;
使用一个 exclusion constraint 来执行以下规则:一个动物园笼子里只能包含一种动物:
Use an exclusion constraint to enforce the rule that a cage at a zoo can contain only one kind of animal:
=> CREATE TABLE zoo (
cage INTEGER,
animal TEXT,
EXCLUDE USING GIST (cage WITH =, animal WITH <>)
);
=> INSERT INTO zoo VALUES(123, 'zebra');
INSERT 0 1
=> INSERT INTO zoo VALUES(123, 'zebra');
INSERT 0 1
=> INSERT INTO zoo VALUES(123, 'lion');
ERROR: conflicting key value violates exclusion constraint "zoo_cage_animal_excl"
DETAIL: Key (cage, animal)=(123, lion) conflicts with existing key (cage, animal)=(123, zebra).
=> INSERT INTO zoo VALUES(124, 'lion');
INSERT 0 1
F.9.2. Authors #
Teodor Sigaev ( < link:mailto:teodor@stack.net[teodor@stack.net]> )、Oleg Bartunov ( < link:mailto:oleg@sai.msu.su[oleg@sai.msu.su]> )、Janko Richter ( < link:mailto:jankorichter@yahoo.de[jankorichter@yahoo.de]> ) 和 Paul Jungwirth ( < link:mailto:pj@illuminatedcomputing.com[pj@illuminatedcomputing.com]> )。有关更多信息,请参阅链接:http://www.sai.msu.su/ megera/postgres/gist/[http://www.sai.msu.su/ megera/postgres/gist/。
Teodor Sigaev (<link:mailto:teodor@stack.net[teodor@stack.net]>), Oleg Bartunov (<link:mailto:oleg@sai.msu.su[oleg@sai.msu.su]>), Janko Richter (<link:mailto:jankorichter@yahoo.de[jankorichter@yahoo.de]>), and Paul Jungwirth (<link:mailto:pj@illuminatedcomputing.com[pj@illuminatedcomputing.com]>). See http://www.sai.msu.su/megera/postgres/gist/ for additional information.