AnalyticDB:btree_gist

Introduction

The btree_gist extension provides GiST index operator classes that implement B-tree equivalent behavior for basic data types^① and all enumeration types. In most cases, the operator classes do not outperform the corresponding standard B-tree index methods, and the operator classes lack the ability to enforce uniqueness, which is a major feature of the standard B-tree code. However, the operator classes provide specific features that are not available with a B-tree index.

In addition to the typical B-tree search operators, the btree_gist extension provides index support for the NotEqual (<>) operator. This may be useful in combination with an exclusion constraint.

For data types that involve a natural distance metric, the btree_gist extension defines a distance operator (<->) and provides GiST index support for nearest neighbor searches that use the operator. Distance operators are provided only for specific basic data types^②.

Usage notes

Only AnalyticDB for PostgreSQL instances in elastic storage mode that meet the following minor version requirements support the btree_gist extension:

• AnalyticDB for PostgreSQL V6.0: V6.6.2.1 or later.

• AnalyticDB for PostgreSQL V7.0: V7.0.6.1 or later.

Install the extension

Install the btree_gist extension on the Extensions page of the AnalyticDB for PostgreSQL instance in which you want to use the extension. For more information, see Install, update, and uninstall extensions.

GiST index structure

GiST is a generalized index framework in PostgreSQL that allows you to create indexes for complex data types. GiST is a balanced tree structure and can be considered a generalization of B-tree and R-tree indexes. GiST supports various search policies.

GiST indexes provide the following features:

• GiST indexes allow you to configure custom index policies for new data types. In addition to common data types such as integers and strings, you can create indexes for GIS data, full-text search data, arrays, and other more complex data structures. The btree_gist extension is designed based on the extensibility of GiST indexes.

• GiST indexes support various queries, such as Equal, NotEqual, range query, and nearest neighbor search.

• GiST indexes are suitable for complex data types that are not supported by B-tree indexes, such as multidimensional data and GIS data.

• GiST indexes support efficient data searches and can significantly accelerate queries for complex data types.

However, GiST indexes have a performance compromise in specific aspects compared with traditional index structures such as B-tree. Management of GiST indexes, such as create and delete operations, may be more complex and less efficient than management of B-tree indexes. Compared with B-tree indexes, GiST indexes require more time to create or rebuild. Exercise caution when you select GiST indexes.

Examples

Replacement of B-tree indexes

Use the btree_gist extension to implement a natural distance measure for scalar values.

For example, execute the following statements to query the 10 data entries whose values in column a are closest to 42.

CREATE TABLE test (a int4);
-- Create an index. 
CREATE INDEX testidx ON test USING GIST (a);
-- Query data. 
SELECT * FROM test WHERE a < 10;
-- Perform a nearest neighbor search to find the 10 data entries whose values in column a are closest to 42. 
SELECT *, a <-> 42 AS dist FROM test ORDER BY a <-> 42 LIMIT 10;

Conclusion constraints

Use the btree_gist extension to implement conclusion constraints.

For example, one cage in a zoo can contain only one type of animal. Cage 123, which contains a zebra, is allowed to receive another zebra but not a lion. Cage 124, which does not contain animals, is allowed to receive a lion.

=> CREATE TABLE zoo (
  cage   INTEGER,
  animal TEXT,
  EXCLUDE USING GIST (cage WITH =, animal WITH <>)
);
=> INSERT INTO zoo VALUES(123, 'zebra');
INSERT 0 1 -- The result indicates that one row is written and no row is deleted. 

=> 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