---
source: https://iinventedlight.com/library/notes/postgres-indexes-basics.md
title: "PostgreSQL indexes: when to add one and which type"
license: CC-BY-4.0
license_url: https://creativecommons.org/licenses/by/4.0/
source_license: "PostgreSQL License; restated in our own words"
attribution: "Summary by iinventedlight (AI-assisted). Sources: PostgreSQL Global Development Group, PostgreSQL docs 11.1: Indexes, Introduction, PostgreSQL License; PostgreSQL Global Development Group, PostgreSQL docs 11.2: Index Types, PostgreSQL License."
author: "iinventedlight (synthesis of cited sources); text drafted with AI assistance"
date: 2026-11-04
status: published
checked_on: 2026-11-05
as_of: 2026-11-05
confidence: medium
untested: "Steps and code were not run; only the fetched documentation page was read."
category: coding-references
topic_area: "code-backend: database indexes"
tags: sql, postgresql, indexes, performance, database
sources:
  - id: S1
    title: "PostgreSQL docs 11.1: Indexes, Introduction"
    publisher: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/indexes-intro.html
    accessed: 2026-11-05
    license: "PostgreSQL License"
  - id: S2
    title: "PostgreSQL docs 11.2: Index Types"
    publisher: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/indexes-types.html
    accessed: 2026-11-05
    license: "PostgreSQL License"
---
# PostgreSQL indexes: when to add one and which type

## Short answer
An index lets PostgreSQL find rows without scanning the whole table. It helps `WHERE`, `JOIN`, `UPDATE` and `DELETE` on the indexed columns. The price is extra work on every write. The default B-tree fits most cases. [S1][S2]

## Create and drop
```sql
CREATE INDEX test1_id_index ON test1 (id);   -- B-tree by default
CREATE INDEX ON docs USING GIN (tags);        -- arrays / jsonb
DROP INDEX test1_id_index;
```
Indexes can be added or dropped at any time. [S1]

## Pick a type
| type | good for |
|---|---|
| B-tree (default) | equality and range on sortable data [S2] |
| Hash | equality only; stores a 32-bit hash [S2] |
| GIN | values with many parts: arrays, jsonb, full text [S2] |
| GiST / SP-GiST | geometric, range and nearest-neighbor searches |
| BRIN | very large tables whose values follow the physical row order [S2] |

## Trade-off
Every insert, update and delete must also update the index. Do not index columns you rarely search on. [S1]

## Claims
- id: C1
  claim: Indexes can be added to and removed from tables at any time.
  source: S1
  evidence: "Indexes can be added to and removed from tables at any time."
- id: C2
  claim: An index on a join-condition column can speed up queries with joins.
  source: S1
  evidence: "can also significantly speed up queries with joins"
- id: C3
  claim: Indexes add overhead to data manipulation operations.
  source: S1
  evidence: "This adds overhead to data manipulation operations."
- id: C4
  claim: CREATE INDEX creates B-tree indexes by default.
  source: S2
  evidence: "By default, the CREATE INDEX command creates B-tree indexes"
- id: C5
  claim: GIN indexes suit values with multiple components, such as arrays.
  source: S2
  evidence: "appropriate for data values that contain multiple component values"

## Sources
- [S1] PostgreSQL docs 11.1: Indexes, Introduction. PostgreSQL Global Development Group. https://www.postgresql.org/docs/current/indexes-intro.html . License: PostgreSQL License. Accessed 2026-11-05.
- [S2] PostgreSQL docs 11.2: Index Types. PostgreSQL Global Development Group. https://www.postgresql.org/docs/current/indexes-types.html . License: PostgreSQL License. Accessed 2026-11-05.

License: this note is CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/). Summary by iinventedlight (AI-assisted).

Source notice: PostgreSQL documentation, Portions Copyright (c) 1996-2026, The PostgreSQL Global Development Group; Portions Copyright (c) 1994, The Regents of the University of California. Used under the PostgreSQL License (https://www.postgresql.org/about/licence/).