---
title = "How Postgres chooses which index to use"
github_url = "https://github.com/orgs/supabase/discussions/26959"
date_created = "2024-06-03T05:53:16+00:00"
topics = [ "database" ]
keywords = [ "index", "performance", "optimization" ]
database_id = "e7671f20-2639-442e-9df9-4f6fa3766598"
---

> For the curious: [here is a list of all built-in indexes in Postgres](https://www.postgresql.org/docs/current/indexes-types.html)

### Postgres internals

#### How an index is chosen

Postgres, internally, contains a few components that manage query execution:

| Module            | Description                                                                                                   |
| ----------------- | ------------------------------------------------------------------------------------------------------------- |
| Parser            | Converts SQL into an traversable query tree                                                                   |
| Planner/Optimizer | Takes the query tree and uses rules and database statistics to find the optimal strategy for getting the data |
| Executor          | Executes the plan created by the planner                                                                      |

The planner will consider using an index when an indexed column is present in a filter statement, such as:

- `WHERE`
- `LIKE`
- `ILIKE`
- `DISTINCT`
- `SIMILAR TO`
- `JOIN`
- `ORDER BY`

Otherwise, it will likely perform a full table scan (sequential scan).

In the majority of cases, the indexed column must not only be present but also must be filtered by a comparison operator (`=`, `>`, `<>`) that is compatible with the index.

As an example, one can create the following table:

| Column Name | Data Type |
| ----------- | --------- |
| id          | INT       |
| data        | JSONB     |

On the data column, a GIN index can be applied, which is excellent for filtering JSONB datatypes:

```SQL
CREATE INDEX some_arbitary_index_name ON some_table USING gin (data);
```

Here's a [link](https://www.postgresql.org/docs/current/gist-builtin-opclasses.html) to the list operators supported by the GIN index; notably, it does not support greater than `>`:

```sql
-- GIN index will never be used
select *
from some_table
where data -> val > 5;
```

GIN does support the `@>` operator:

```sql
--GIN will be considered
SELECT id FROM some_table
WHERE data @> '[ { "itemId": "p11" } ]';
```

In most cases, developers work with the default BTREE index. It is the most practical and performant in the majority of cases and is compatible with the following filter [operators](https://www.postgresql.org/docs/current/btree-behavior.html):

| Comparison Operator |
| ------------------- |
| `<`                 |
| `<=`                |
| `=`                 |
| `>=`                |
| `>`                 |

An operator's functional equivalents, such as `IN`, `BETWEEN`, and `ANY`, are also valid.

However, just because the base requirements (relevant column, filter, and operators) are present, doesn't mean that an index will be used.

Indexes have a startup cost, so for small tables, Postgres might use a sequential scan if it believes that it will take less time. The database keeps statistics about each table that it uses to inform these choices.

In very rare cases, these statistics can become stale, and Postgres may opt to use a slower index or sequential scan when a better option is available.

You can see the query plan with the `EXPLAIN` keyword:

```
EXPLAIN <your query>
```

To understand how to interpret its output, you can check out this [explainer](https://github.com/orgs/supabase/discussions/22839).

To reset statistics within the database, you can use the following query:

```sql
-- use judiciously
select pg_stat_reset();
```

### Complex or composite indexes

> For a more complete rundown, check the [Postgres Official Docs](https://www.postgresql.org/docs/current/indexes-multicolumn.html)

#### Multi-column indexes

If you make independent indexes on multiple columns, Postgres will likely use each of them independently to find the relevant rows and then combine the results together.

It is possible to make [multi-column indexes](https://www.postgresql.org/docs/current/indexes-multicolumn.html). If you are regularly filtering against multiple columns, there can be performance benefits using them instead of several independent indexes.

```sql
-- multi-column index
create index test2_mm_idx on test2 (major, minor);

-- multi-column comparison:
select name
from test2
where major = constant and minor = constant;
```

#### Ordered indexes

If you're using an ORDER BY clause, [indexes can also be pre-sorted by DESC/ASC](https://www.postgresql.org/docs/current/indexes-ordering.html) for better performance.

```
-- organizes the index in a DESC order, places NULL values at the end
CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);
```

#### Functional indexes

Although not as common, indexes can also be leveraged against modified values, such as when using a LOWER function:

```sql
-- Index on modified column through function
create index test1_lower_col1_idx on test1 (lower(col1));

-- Index will be considered for the following query:
select * from test1 where lower(col1) = 'value';
```

#### Covering indexes

Indexes contain pointers to a specific row, but you could instruct an index to actually hold a copy of a column's value for even faster retrieval. These are known as `covering` indexes. Because maintaining a copy is storage intensive, you should avoid using it for values with large data footprints.[ FULL VIDEO ON TOPIC](https://www.youtube.com/watch?v=bBu_V8CfWgM)

```sql
CREATE INDEX a_b_idx ON x (a,b) INCLUDE (c);
```

#### Indexes on JSONB

Although a GIN/GIST index can be used to index entire JSONB bodies, you can also target just specific Key-values with standard BTREE indexes:

```sql
-- Example table
create table person (
  id serial primary key,
  data jsonb
);

create index index_name on person ((data ->> 'name'));
```
