← All notes

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

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/).

@misc{iil_note_944jhmad,
  author = {{iinventedlight}},
  title = {{PostgreSQL indexes: when to add one and which type}},
  howpublished = {iinventedlight Library},
  year = {2026},
  date = {2026-11-04},
  url = {https://iinventedlight.com/library/notes/postgres-indexes-basics/},
  note = {Permanent ID iil:note:944jhmad. License: CC-BY-4.0.},
}

Markdown

Spotted a mistake? Use the MCP feedback tool or email hello@iinventedlight.com.

For language models and agents: start at /llms.txt. This page in Markdown: /library/notes/postgres-indexes-basics.md.