What’s the difference between a hash index and a B-tree index, and which queries can each support? It depends on the database system and version. In PostgreSQL 17, both index types can support equality lookups, but B-trees also support range searches and sorted output. PostgreSQL hash indexes are limited to equality comparisons, so they suit a narrower set of queries.
Query support at a glance
| Query or requirement | PostgreSQL 17 B-tree | PostgreSQL 17 hash |
|---|---|---|
Equality, such as column = value |
Supported | Supported for the = operator |
Range predicates: <, <=, >=, > |
Supported | Not supported |
BETWEEN and IN |
Can be implemented with B-tree searches | Not supported as range or multiple-value searches |
| Sorted output by indexed key | Can return rows in sorted order | Cannot provide ordering by the indexed key |
| Uniqueness checking | Supported where the index is defined as unique | Not supported |
| Multiple indexed columns | Can be used for multicolumn indexes | Single-column only |
These are capabilities, not guarantees that a planner will choose a particular index for every query. PostgreSQL 17 lists B-tree as the default index type for common situations. Its documentation describes B-trees as suitable for equality and range searches on data with a sortable ordering. PostgreSQL 17: Index Types
When a B-tree supports more than equality
A B-tree is the more flexible option when queries may need to find a value, a span of values, or rows in key order. PostgreSQL 17 can consider B-tree indexes for <, <=, =, >=, and > predicates; BETWEEN and IN searches can also be implemented with B-tree searches.
B-trees can also retrieve rows in sorted order by the indexed key. That can help when a query needs ordered output, though the planner still decides whether using the index is worthwhile for the particular query.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →#1 Best Overall
What a PostgreSQL hash index can and cannot do
In PostgreSQL 17, a hash index supports equality comparisons only, using the = operator. It cannot serve a range condition or supply sorted output by the indexed key. If a query needs ordering, range filtering, or uniqueness enforcement, a PostgreSQL hash index does not provide that capability.
PostgreSQL hash indexes are persistent, on-disk indexes. They store a hash value rather than the original column value: each tuple stores a four-byte hash value. Because collisions are possible and the original value is not stored in the index, hash scans are lossy and may require PostgreSQL to recheck rows against the original condition. The implementation also means hash indexes do not have the same key-column size restriction associated with storing full values.
PostgreSQL 17 hash indexes are single-column and do not support uniqueness checking. These constraints matter when choosing an index for a schema, not just for a query. PostgreSQL 17: Hash Indexes
When a hash index may be worth evaluating
PostgreSQL documentation says hash indexes may be smaller than B-trees for longer keys, such as UUIDs or URLs, because the index stores a four-byte hash value per tuple rather than the full key. That is a possible space advantage, not a rule that hash indexes are always smaller: actual size and performance depend on the data and workload.
Recommended Free Tools
Rank #3
PostgreSQL describes hash indexes as best optimized for equality scans on larger tables in SELECT- and UPDATE-heavy workloads. A B-tree search descends to a leaf, while a hash index accesses the relevant bucket page. But bucket overflow pages can add scans, and an unbalanced hash index can require more block accesses than a B-tree for some data. PostgreSQL therefore presents this as a workload-specific option, not a universal speed advantage.
- Consider a hash index only when the important predicate is equality on one column.
- Check whether the key is long enough for the potential storage difference to matter.
- Use real query plans and measurements on the target data before deciding; the documented behavior does not establish a benchmark or speedup for your workload.
- Prefer a B-tree when the same index must support range predicates, ordered results, uniqueness, or multiple columns.
How MySQL’s documented hash-index case differs
The comparison above describes PostgreSQL 17. MySQL’s 26.7 Reference Manual discusses hash indexes in the context of the MEMORY storage engine: there, hash indexes support equality comparisons using = or the null-safe equality operator <=>, and do not accelerate ORDER BY. This is specific to the documented MEMORY-engine context, not a statement about every MySQL index configuration. MySQL 26.7: Comparison of B-Tree and Hash Indexes
Quick Recap
Choose by the query and schema requirements
- List the predicates. If queries need ranges or use
BETWEENorIN, PostgreSQL 17 B-tree supports those patterns; hash does not. - Check ordering. If output must be sorted by the indexed key, a PostgreSQL 17 B-tree can provide that ordering; hash cannot.
- Check schema needs. If the index must enforce uniqueness or span multiple columns in PostgreSQL 17, hash is not an option.
- Evaluate equality-only workloads. For a large table with equality-heavy SELECT and UPDATE activity, a hash index may be worth measuring, especially with long keys, but overflow behavior and data distribution can affect access cost.
- Confirm the database context. Verify the engine and version before applying these rules; the MySQL MEMORY-engine example is not interchangeable with PostgreSQL’s persistent hash indexes.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
