Decision: Which index type?
The right index type depends on the query's access pattern (equality, range, or text/JSON search), the column's cardinality, and the read/write balance. The wrong index both wastes space and slows down writes. This tree starts from the shape of the query and guides you among B-tree, Hash, Bitmap, GIN/GiST, partial, and covering index options, and reminds you that column order is critical in a composite index.
How does your query use this column?
The access pattern is the most decisive factor in choosing an index. Do you only match the column with '=', do you do range/sorting (>, <, BETWEEN, ORDER BY), or do you search full text, a JSON field, or geometric data/arrays? Start here; do not choose an index type until the need is clear.
- Mixed equality and range/sorting (=, >, <, BETWEEN, ORDER BY)
- Equality only (=), no range or sorting
- Full-text, JSON/JSONB field, array, or geometric search
- The query always targets the same small subset of rows (e.g. WHERE status='active')
What is the column's cardinality (number of distinct values)?
If range/sorting is needed, the default is B-tree; however, for columns with very few distinct values (low cardinality: gender, status, yes/no) in a read-heavy data warehouse, a Bitmap index may be better. Does the column have many unique values, or a few repeating values?
- High cardinality (many unique values) or OLTP workload
- Low cardinality and read-heavy / data warehouse (DWH)
Does the query read only the columns in the index?
The access pattern suits a B-tree. One last optimization: if all columns the query returns and filters on are in the index, the engine can answer from the index alone without visiting the table (index-only / covering scan). Does your query read a few columns, or does it need most of the row?
- Yes, a few columns; it can be answered without visiting the table
- No, standard access is enough
Recommendation: B-tree (default)
A B-tree is a balanced tree structure that supports a wide range of access patterns: equality, range, sorting (ORDER BY), prefix matching with LIKE 'abc%', and MIN/MAX. It is the default index type in most relational engines and the right starting point when in doubt.
Recommendation: Hash index (equality only)
A hash index stores the hash of the column value and is very fast for exact equality ('=') lookups only. It does not support range, sorting, or prefix searches, so its use case is narrow.
Recommendation: Bitmap index (low cardinality / DWH)
A bitmap index keeps a bit array for each distinct value and combines multiple low-cardinality conditions very quickly with bitwise AND/OR. It is strong in read-heavy workloads such as data warehousing and reporting.
Recommendation: GIN / GiST (text, JSON, array, geometry)
PostgreSQL has specialized index families for access types a standard B-tree cannot handle. GIN points to many components within a value (words, JSONB keys, array elements); GiST suits queries such as geometric/range/nearest-neighbor.
Recommendation: Partial / filtered index
A partial index indexes only the rows that satisfy a condition (WHERE ...) instead of the whole table. If queries always target the same small subset, the index becomes much smaller and faster, and write cost drops.
Recommendation: Covering (INCLUDE) index
A covering index holds all the columns the query needs, letting the engine answer from the index alone without visiting the table (heap) at all (index-only scan). It gives a significant speedup for frequently run queries that return few columns.