btree_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 | btree_gist
unaccent
fuzzystrmatch
pg_trgm
prefix
citext
pg_idkit
pgx_ulid
|
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
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.