> This page location: Extensions > btree_gist
> Full Neon documentation index: https://neon.com/docs/llms.txt

# The btree_gist extension

Combine GiST and B-tree indexing capabilities for efficient multi-column queries and constraints

The `btree_gist` extension for Postgres provides a specialized set of **GiST operator classes**. These allow common, "B-tree-like" data types (such as integers, text, or timestamps) to be included in **GiST (Generalized Search Tree) indexes**. This is especially useful when you need to create **multicolumn GiST indexes** that combine GiST-native types (like geometric data or range types) with these simpler B-tree types. `btree_gist` also plays a key role in defining **exclusion constraints** involving standard data types.

For example, if an application needs to query for events happening within a specific geographic area (a `geometry` type) _and_ within a certain `event_time` (a timestamp), `btree_gist` allows a single, optimized GiST index to cover both conditions.

## Enable the `btree_gist` extension

You can enable the extension by running the following `CREATE EXTENSION` statement in the [Neon SQL Editor](https://neon.com/docs/get-started/query-with-neon-sql-editor) or from a client such as [psql](https://neon.com/docs/connect/query-with-psql-editor) that is connected to your Neon database.

```sql
CREATE EXTENSION IF NOT EXISTS btree_gist;
```

**Version availability:**

Please refer to the [list of all extensions](https://neon.com/docs/extensions/pg-extensions) available in Neon for up-to-date extension version information.

## `btree_gist`: Combining index strengths

When working with geospatial data or range types, GiST indexes are often the go-to choice due to their ability to efficiently handle complex data structures. However, many applications also rely on standard B-tree-friendly columns for filtering and sorting.

More often than not, queries need to filter on both GiST-friendly columns (for example, `location GEOMETRY`, `booking_period TSTZRANGE`) and B-tree friendly columns (for example, `status TEXT`, `created_at TIMESTAMPTZ`, `item_id INTEGER`). While Postgres can use separate indexes, a combined index can be more efficient.

The `btree_gist` extension does this by providing GiST **operator classes** for many standard B-tree-indexable data types. These operator classes tell the GiST indexing mechanism how to handle these scalar types within its framework.

For instance, with `btree_gist` (and often `postgis` for geometry types), a single GiST index can be defined on `(event_location GEOMETRY, event_timestamp TIMESTAMPTZ)`.

**Example:**

```sql
-- Ensure postgis extension is enabled
CREATE EXTENSION IF NOT EXISTS postgis; -- For GEOMETRY type

-- Create the table
CREATE TABLE scheduled_events (
    event_id SERIAL PRIMARY KEY,
    event_location GEOMETRY(Point, 4326), -- A GiST-friendly type
    event_timestamp TIMESTAMPTZ           -- A B-tree-friendly type
);

CREATE INDEX idx_events_location_time
ON scheduled_events
USING GIST (event_location, event_timestamp);
```

This composite index can then be used by Postgres to optimize queries filtering on both `event_location` and `event_timestamp` simultaneously:

```sql
SELECT * FROM scheduled_events
WHERE ST_DWithin(event_location, ST_SetSRID(ST_MakePoint(-73.985, 40.758), 4326)::geography, 1000) -- Within 1km
  AND event_timestamp >= '2025-03-01 00:00:00Z'
  AND event_timestamp < '2025-04-01 00:00:00Z';
```

Without `btree_gist`, `event_timestamp` could not be directly included in the GiST index alongside `event_location` in this straightforward manner.

## Usage scenarios

Let's explore practical examples where `btree_gist` is beneficial.

### Filtering events by location and time

Consider a `map_events` table where queries often search for events in a specific geographical bounding box and within a particular date range.

#### Table schema

```sql
-- Ensure PostGIS is enabled
-- CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE map_events (
    id SERIAL PRIMARY KEY,
    name TEXT,
    geom GEOMETRY(Point, 4326), -- GiST-friendly spatial data
    event_date DATE             -- B-tree friendly date
);

INSERT INTO map_events (name, geom, event_date) VALUES
('Music Festival', ST_SetSRID(ST_MakePoint(-0.1276, 51.5074), 4326), '2025-02-20'),
('Art Exhibition', ST_SetSRID(ST_MakePoint(-0.1200, 51.5000), 4326), '2025-02-22'),
('Tech Conference', ST_SetSRID(ST_MakePoint(2.3522, 48.8566), 4326), '2025-03-05');
```

#### `btree_gist` index creation

A composite GiST index covers both `geom` and `event_date`.

```sql
CREATE INDEX idx_map_events_geom_date
ON map_events
USING GIST (geom, event_date);
```

#### Example query

Find events in London (approximated by a bounding box) occurring in February 2025:

```sql
SELECT name, event_date
FROM map_events
WHERE geom && ST_MakeEnvelope(-0.5, 51.25, 0.3, 51.7, 4326) -- Approximate bounding box for London
  AND event_date >= '2025-02-01'
  AND event_date < '2025-03-01';
```

The `idx_map_events_geom_date` index allows Postgres to efficiently process both the spatial overlap (`&&`) and the date range conditions.

### Enforcing exclusion constraints for room bookings

`btree_gist` is essential for creating exclusion constraints that involve B-tree types alongside GiST-native types like ranges.

Room bookings are a classic example: you need to ensure no two bookings overlap for the same room.

#### Table schema

```sql
CREATE TABLE room_bookings (
    booking_id SERIAL PRIMARY KEY,
    room_id INTEGER,            -- B-tree friendly integer
    booking_period TSTZRANGE    -- GiST-friendly range type
);
```

#### `btree_gist` index creation for exclusion constraint

The exclusion constraint uses a GiST index. `room_id WITH =` will use `btree_gist`.

```sql
ALTER TABLE room_bookings
ADD CONSTRAINT no_overlapping_bookings
EXCLUDE USING GIST (room_id WITH =, booking_period WITH &&);
```

The `WITH =` operator for `room_id` uses `btree_gist`, and `WITH &&` (overlap) is native to range types with GiST.

#### Example operations

```sql
-- Successful booking
INSERT INTO room_bookings (room_id, booking_period)
VALUES (101, '[2025-04-10 14:00, 2025-04-10 16:00)');

-- Attempting to book the same room for an overlapping period
INSERT INTO room_bookings (room_id, booking_period)
VALUES (101, '[2025-04-10 15:00, 2025-04-10 17:00)');
-- This will fail: ERROR:  conflicting key value violates exclusion constraint "no_overlapping_bookings"

-- Booking a different room for an overlapping period is fine
INSERT INTO room_bookings (room_id, booking_period)
VALUES (102, '[2025-04-10 15:00, 2025-04-10 17:00)');
```

## Important considerations and Best practices

- **Use case specificity:** `btree_gist` is not a general replacement for B-tree indexes. It excels when combining B-tree types with GiST-specific types/features in one index or for exclusion constraints.
- **Performance:** For queries filtering _solely_ on a B-tree-indexable column (for example, `WHERE status = 'active'`), a dedicated B-tree index is typically faster and more space-efficient.
- **Index size and write overhead:** GiST indexes can be larger and have slightly higher write overhead (for `INSERT`/`UPDATE`/`DELETE`) than B-tree indexes.

## Conclusion

The `btree_gist` extension provides a vital bridge, allowing standard B-tree-indexable data types to be included in GiST indexes. This enables efficient multi-column queries across diverse data types (for example, spatial and temporal) and supports sophisticated exclusion constraints.

## Resources

- [PostgreSQL `btree_gist` documentation](https://www.postgresql.org/docs/current/btree-gist.html)
- [PostgreSQL Indexes](https://neon.com/postgresql/postgresql-indexes)
- [How and when to use btree_gist](https://neon.com/blog/btree_gist)
- [PostgreSQL Index Types](https://neon.com/postgresql/postgresql-indexes/postgresql-index-types)
- [`postgis` extension](https://neon.com/docs/extensions/postgis)

---

## Related docs (Extensions)

- [Extension explorer](https://neon.com/docs/extensions/extension-explorer)
- [anon](https://neon.com/docs/extensions/postgresql-anonymizer)
- [btree_gin](https://neon.com/docs/extensions/btree_gin)
- [citext](https://neon.com/docs/extensions/citext)
- [cube](https://neon.com/docs/extensions/cube)
- [dblink](https://neon.com/docs/extensions/dblink)
- [dict_int](https://neon.com/docs/extensions/dict_int)
- [earthdistance](https://neon.com/docs/extensions/earthdistance)
- [fuzzystrmatch](https://neon.com/docs/extensions/fuzzystrmatch)
- [hstore](https://neon.com/docs/extensions/hstore)
- [intarray](https://neon.com/docs/extensions/intarray)
- [ltree](https://neon.com/docs/extensions/ltree)
- [neon](https://neon.com/docs/extensions/neon)
- [neon_utils](https://neon.com/docs/extensions/neon-utils)
- [online_advisor](https://neon.com/docs/extensions/online_advisor)
- [pgcrypto](https://neon.com/docs/extensions/pgcrypto)
- [pgvector](https://neon.com/docs/extensions/pgvector)
- [pgrag](https://neon.com/docs/extensions/pgrag)
- [pg_cron](https://neon.com/docs/extensions/pg_cron)
- [pg_graphql](https://neon.com/docs/extensions/pg_graphql)
- [pg_mooncake](https://neon.com/docs/extensions/pg_mooncake)
- [pg_partman](https://neon.com/docs/extensions/pg_partman)
- [pg_prewarm](https://neon.com/docs/extensions/pg_prewarm)
- [pg_session_jwt](https://neon.com/docs/extensions/pg_session_jwt)
- [pg_stat_statements](https://neon.com/docs/extensions/pg_stat_statements)
- [pg_repack](https://neon.com/docs/extensions/pg_repack)
- [pg_search](https://neon.com/docs/extensions/pg_search)
- [pg_tiktoken](https://neon.com/docs/extensions/pg_tiktoken)
- [pg_trgm](https://neon.com/docs/extensions/pg_trgm)
- [pg_uuidv7](https://neon.com/docs/extensions/pg_uuidv7)
- [pgrowlocks](https://neon.com/docs/extensions/pgrowlocks)
- [pgstattuple](https://neon.com/docs/extensions/pgstattuple)
- [postgis](https://neon.com/docs/extensions/postgis)
- [postgis-related](https://neon.com/docs/extensions/postgis-related-extensions)
- [postgres_fdw](https://neon.com/docs/extensions/postgres_fdw)
- [tablefunc](https://neon.com/docs/extensions/tablefunc)
- [timescaledb](https://neon.com/docs/extensions/timescaledb)
- [unaccent](https://neon.com/docs/extensions/unaccent)
- [uuid-ossp](https://neon.com/docs/extensions/uuid-ossp)
- [wal2json](https://neon.com/docs/extensions/wal2json)
- [xml2](https://neon.com/docs/extensions/xml2)