btree_gin

support for indexing common datatypes in GIN

https://www.postgresql.org/docs/current/btree-gin.html

Overview

Package	Version	Category	License	Language
`btree_gin`	`1.3`	FUNC	PostgreSQL	C

ID	Extension	Bin	Lib	Load	Create	Trust	Reloc	Schema
4950	`btree_gin`	No	Yes	No	Yes	Yes	No	-

Related	`ora_btree_gin` `btree_gist` `parray_gin` `intarray` `rum` `bloom` `db2fce` `orafce`

Version

PG18	PG17	PG16	PG15	PG14
1.3	1.3	1.3	1.3	1.3

Install

Note: This is a built-in contrib extension of PostgreSQL

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.