Index types and when the planner uses them

PostgreSQL provides several index types: B-tree, Hash, GiST, SP-GiST, GIN, BRIN, and the extension bloom. Each index type uses a different algorithm best suited to different types of indexable clauses; by default CREATE INDEX creates B-tree indexes, which fit the most common situations, and other types are selected by writing the keyword USING followed by the index type name. B-trees can handle equality and range queries on data that can be sorted into some ordering, and the planner will consider using a B-tree index whenever an indexed column is involved in a comparison using <, <=, =, >=, or >; constructs equivalent to combinations of these operators, such as BETWEEN and IN, can also be implemented with a B-tree index search, as can IS NULL and IS NOT NULL conditions on an index column. The optimizer can also use a B-tree index for the pattern matching operators LIKE and ~ when the pattern is a constant anchored to the beginning of the string, for example col LIKE 'foo%' but not col LIKE '%bar', and B-tree indexes can be used to retrieve data in sorted order. Hash indexes store a 32-bit hash code derived from the value of the indexed column and can only handle simple equality comparisons using =. GIN indexes are inverted indexes appropriate for data values that contain multiple component values, such as arrays; GiST and SP-GiST are infrastructures within which many different indexing strategies can be implemented, and both can optimize nearest-neighbor searches for operator classes that support distance ordering. Scope: PostgreSQL current-release documentation. Reviewed October 4, 2026.