Index AM
Version coverage matrixDefinitions for PostgreSQL 18
Reading PG 18·Select a recorded version
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 operators:
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 table rows. Like GiST, SP-GiST and GIN, BRIN can support many different indexing strategies, and the particular operators with which a BRIN index can be used vary depending on the indexing strategy. For data types that have a linear sort order, the indexed data corresponds to the minimum and maximum values of the values in the column for each block range. This supports indexed queries using these operators:
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 the presence of specific component values.
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 strategy (the operator class ). As an example, the standard distribution of PostgreSQL includes GiST operator classes for several two-dimensional geometric data types, which support indexed queries using these operators:
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 comparison using the equal operator:
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 (tries). As an example, the standard distribution of PostgreSQL includes SP-GiST operator classes for two-dimensional points, which support indexed queries using these operators:
Capability matrix · PostgreSQL 18
Select a cell to read the same-version evidence. Not established means the selected manual does not justify a yes or no.
| Capability | B-tree | BRIN | GIN | GiST | Hash | SP-GiST |
|---|---|---|---|---|---|---|
| Sorted output | Yes | No | No | No | No | No |
| Unique keys | Yes | No | No | No | No | No |
| Multiple key columns | Yes | Yes | Yes | Yes | No | No |
| INCLUDE columns | Yes | No | No | Yes | No | Yes |
| Index-only scans | Yes | No | No | Conditional | No | Conditional |
| Distance ordering | Not established | Not established | Not established | Conditional | Not established | Conditional |
| Parallel index scan | Yes | No | No | No | No | No |
| Parallel index build | Yes | Yes | Yes | No | No | No |