btree_gist

Version requirements

btree_gist is supported only on AnalyticDB for PostgreSQL instances running in elastic storage mode. Minimum version requirements:

• 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 btree_gist on the Extensions page of your AnalyticDB for PostgreSQL instance. For details, see Install, update, and uninstall extensions.

How it works

GiST is a balanced, extensible tree structure in PostgreSQL that generalizes both B-tree and R-tree indexes. Unlike B-tree, which is optimized for a fixed set of scalar comparisons, GiST supports pluggable search strategies, making it suitable for complex data types such as multidimensional data and geographic information system (GIS) data.

btree_gist provides GiST operator classes that implement B-tree-equivalent behavior (equality, range queries, and <> for exclusion constraints) for basic scalar types and all enumeration types. It also defines a distance operator (<->) for nearest neighbor searches on types that have a natural distance metric.

Supported data types

btree_gist supports the following data types for all operators (equality, range, and <>):

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, and Bool.

The distance operator (<->) for nearest neighbor searches is available only on these 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, and Money.

Limitations compared to B-tree

btree_gist operator classes do not outperform standard B-tree indexes for single-column queries on B-tree-indexable types, and they cannot enforce uniqueness. GiST indexes also require more time to create or rebuild.

Examples

CREATE TABLE test (a int4);

-- Create a GiST index to support nearest neighbor searches.
CREATE INDEX testidx ON test USING GIST (a);

-- Standard range query using the GiST index.
SELECT * FROM test WHERE a < 10;

-- Nearest neighbor search: return the 10 rows closest to 42.
SELECT *, a <-> 42 AS dist FROM test ORDER BY a <-> 42 LIMIT 10;

CREATE TABLE zoo (
  cage   INTEGER,
  animal TEXT,
  EXCLUDE USING GIST (cage WITH =, animal WITH <>)
);

-- Inserting a zebra into cage 123 succeeds.
INSERT INTO zoo VALUES(123, 'zebra');
-- INSERT 0 1

-- Inserting another zebra into cage 123 also succeeds (same species).
INSERT INTO zoo VALUES(123, 'zebra');
-- INSERT 0 1

-- Inserting a lion into cage 123 fails (different species in the same cage).
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).

-- Inserting a lion into cage 124 succeeds (different cage).
INSERT INTO zoo VALUES(124, 'lion');
-- INSERT 0 1

When to use btree_gist

btree_gist is the right choice when you need one of the following:

• Nearest neighbor search: Query rows ordered by distance to a reference value using <->, which B-tree indexes do not support.

• Exclusion constraints: Prevent overlapping or conflicting row combinations using EXCLUDE USING GIST with the <> operator.

• Mixed multi-column GiST indexes: Combine a B-tree-compatible column with a GiST-specific type (such as a geometric or range type) in a single index.

For queries that filter on a single B-tree-indexable column (for example, WHERE status = 'active'), a standard B-tree index is faster and more storage-efficient. Use btree_gist only when the query pattern specifically requires GiST capabilities.

Related topics

• Install, update, and uninstall extensions