> ## Documentation Index
> Fetch the complete documentation index at: https://thenile.dev/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Btree_gist

> Allows GiST indexes to support B-tree indexable data types.

The [btree\_gist](https://www.postgresql.org/docs/current/btree-gist.html) extension in PostgreSQL allows GiST indexes to support B-tree indexable data types. It is useful for indexing columns that typically use B-tree indexes but require additional GiST-specific features such as **multicolumn indexing**, **range queries**, and **support for exclusion constraints**.
Your Nile database arrives with `btree_gist` extension already enabled, so there's no need to run `create extension`.

## Creating and Populating a Sample Table

Before creating the index, let's create a sample table and populate it with data:

```sql theme={null}
CREATE TABLE my_table (
    id SERIAL PRIMARY KEY,
    column1 TEXT,
    column2 INT,
    column3 TSTZRANGE
);

INSERT INTO my_table (column1, column2, column3) VALUES
    ('apple', 10, '[2024-01-01 10:00:00, 2024-01-01 12:00:00]'),
    ('banana', 20, '[2024-02-01 11:30:00, 2024-02-01 13:30:00]'),
    ('grape', 15, '[2024-03-01 14:15:00, 2024-03-01 16:15:00]'),
    ('orange', 25, '[2024-04-01 09:45:00, 2024-04-01 11:45:00]'),
    ('lemon', 30, '[2024-05-01 16:20:00, 2024-05-01 18:20:00]');
```

## Creating a GiST Index with btree\_gist

A GiST index with `btree_gist` can be used for multi-column searches and exclusion constraints. Here's how to create one:

```sql theme={null}
CREATE INDEX gist_idx ON my_table
USING gist (column1, column2, column3);
```

<Info>
  Note: `USING gist` specifies that this is a GiST index. The extension allows
  `column1` (text), `column2` (integer), and `column3` (timestamp) to be indexed
  efficiently using GiST.
</Info>

## Querying with GiST Index

Once the GiST index is created, it can be used to optimize queries filtering on indexed columns:

```sql theme={null}
SELECT * FROM my_table WHERE column1 = 'apple' AND column2 = 10;
```

## Limitations

* GiST indexes generally **do not provide the same performance as B-tree indexes** for single-column lookups.
* They **excel at multi-column queries** and range searches but can be **slower for simple equality lookups**.
* `btree_gist` is useful primarily for **exclusion constraints** rather than improving query performance.

## Removing a GiST Index

If you need to remove a GiST index:

```sql theme={null}
DROP INDEX gist_idx;
```

## Conclusion

The `btree_gist` extension enhances GiST indexes by allowing them to handle B-tree indexable data types efficiently. It is particularly useful for **multi-column indexing**, **range queries**, and **exclusion constraints**.

For more details, refer to the [PostgreSQL documentation](https://www.postgresql.org/docs/current/btree-gist.html).