btree_gin

support for indexing common datatypes in GIN

btree_gin : support for indexing common datatypes in GIN

Overview

ID	Extension	Package	Version	Category	License	Language
4950	btree_gin	btree_gin	`1.3`	FUNC	PostgreSQL	C

Attribute	Has Binary	Has Library	Need Load	Has DDL	Relocatable	Trusted
--s-dt-	No	Yes	No	Yes	no	yes

Relationships
See Also	ora_btree_gin btree_gist parray_gin intarray rum bloom db2fce orafce

Packages

PG18	PG17	PG16	PG15	PG14
1.3	1.3	1.3	1.3	1.3

This is a built-in contrib extension ship with the PostgreSQL kernel

Install

Create this extension with:

CREATE EXTENSION btree_gin;

Usage

btree_gin: B-tree equivalent GIN operator classes

Provides GIN index operator classes for data types that normally only support B-tree indexing. Useful for multicolumn GIN indexes that combine GIN-indexable and B-tree-indexable columns.

CREATE EXTENSION btree_gin;

Supported 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, macaddr, macaddr8, inet, cidr, uuid, bit, varbit, bool, name, bpchar, and all enum types.

Examples

-- GIN index on an integer column
CREATE INDEX idx ON test USING GIN (a);
SELECT * FROM test WHERE a < 10;

-- Multicolumn GIN index combining full-text search with a scalar filter
CREATE INDEX idx ON articles USING GIN (body_tsvector, category);
SELECT * FROM articles
WHERE body_tsvector @@ to_tsquery('PostgreSQL')
  AND category = 'tech';

Note: btree_gin does not outperform standard B-tree indexes for single-column queries. Its main benefit is combining scalar columns with GIN-native columns (like tsvector or arrays) in a single multicolumn index.