Wiki / Index AM
Index AM
Compare PostgreSQL’s six built-in index methods: query support, ordering, uniqueness, storage options and version history.
Reading PostgreSQL 18.6.
6 built-in methods; 6 documented in PostgreSQL 18.6.
Methods and version coverage
-
B-tree Ordered search
B-tree
B-trees can handle equality and range queries on data that can be sorted into some ordering. In particular, the PostgreSQL query planner will consider using a B-tree index whenever an indexed column is involved in a comparison using one of these ope…
-
Hash Equality search
Hash
Hash indexes store a 32-bit hash code derived from the value of the indexed column. Hence, such indexes can only handle simple equality comparisons. The query planner will consider using a hash index whenever an indexed column is involved in a compa…
-
GiST Extensible search
GiST
GiST indexes are not a single kind of index, but rather an infrastructure within which many different indexing strategies can be implemented. Accordingly, the particular operators with which a GiST index can be used vary depending on the indexing st…
-
SP-GiST Extensible search
SP-GiST
SP-GiST indexes, like GiST indexes, offer an infrastructure that supports various kinds of searches. SP-GiST permits implementation of a wide range of different non-balanced disk-based data structures, such as quadtrees, k-d trees, and radix trees (…
-
GIN Component search
GIN
GIN indexes are “ inverted indexes ” which are appropriate for data values that contain multiple component values, such as arrays. An inverted index contains a separate entry for each component value, and can efficiently handle queries that test for…
-
BRIN Block summaries
BRIN
BRIN indexes (a shorthand for Block Range INdexes) store summaries about the values stored in consecutive physical block ranges of a table. Thus, they are most effective for columns whose values are well-correlated with the physical order of the tab…
Squares show sampled presence and changes to documented capabilities. The first sample is not necessarily a method's introduction.
Capability matrix · PostgreSQL 18.6
Select a cell to see its source and conditions. “Not established” means this manual extraction does not justify a yes or no.
| Capability | B-tree | Hash | GiST | SP-GiST | GIN | BRIN |
|---|---|---|---|---|---|---|
| Sorted output | Yes | No | No | No | No | No |
| Unique keys | Yes | No | No | No | No | No |
| Multiple key columns | Yes | No | Yes | No | Yes | Yes |
| INCLUDE columns | Yes | No | Yes | Yes | No | No |
| Index-only scans | Yes | No | Conditional | Conditional | No | No |
| Distance ordering | Not established | Not established | Conditional | Conditional | Not established | Not established |
| Parallel index scan | Yes | No | No | No | No | No |
| Parallel index build | Yes | No | No | No | Yes | Yes |
An access method supplies a framework; supported operators and index-only scans can depend on the operator class, data type and indexed column. Conditional and unrecorded capabilities are shown explicitly.
Version comparison
GIN
- Parallel index build: No → Yes
This comparison covers capability states and the names of documented storage options. It does not compare every algorithm, performance characteristic, option definition or release-note change.
Choosing an index
Start with the query operators and the indexed data type, then choose an operator class supported by the access method. Sorted output, nearest-neighbor ordering, covering columns and index-only scans are separate capabilities.
These entries cover the built-in index methods. Table access methods and foreign data wrappers have different interfaces; extensions such as bloom are outside this inventory.
Operator classes and families · pg_am catalog · Version changes