Free tools Windows power users keep installed
One-click scans. No signup required.
Yes—but only for the right workload and database. Hash indexes can suit repeated equality lookups, particularly on unique or nearly unique values, when the database supports them. They are not a general replacement for B-trees: they do not support range scans, and crowded hash buckets can add lookup work. Check the engine’s rules and benchmark both index types with representative data before choosing.
What a hash index is good at
A hash index maps a key to a hash value and uses that value to find candidate rows. Its strength is equality matching: a query asks whether a value equals a particular key. It is not designed to walk keys in sorted order.
That makes a hash index worth considering when a large table receives frequent equality lookups and the indexed values are unique, nearly unique, or otherwise spread across buckets without many rows per bucket. This is a workload-dependent possibility, not a guaranteed performance improvement.
When a B-tree is the better fit
Choose a B-tree when queries need to do more than test equality. B-trees can support range conditions, ordered results, and other access patterns that depend on key order; hash indexes cannot provide those range scans. A B-tree is also the more broadly available default in the database systems covered here.
#1 Best Overall
- Equality lookup: A hash index may be a candidate if the engine and table type support it.
- Range or ordering: Use a B-tree or another index type suited to the operator; a hash index cannot serve the range scan.
- Uniqueness enforcement: Do not rely on a PostgreSQL hash index to enforce uniqueness; it does not perform uniqueness checking.
- Many duplicates: Check how the engine handles bucket crowding. In PostgreSQL, overflow pages can increase the blocks a scan must visit.
How support differs by database
| Database and context | Hash-index behavior | Practical implication |
|---|---|---|
| PostgreSQL 17 | Persistent, on-disk, crash-recoverable; supports the = operator, indexes one column, and does not enforce uniqueness. Each index tuple stores a 4-byte hash value rather than the source value, so scans are lossy and verify candidates against table rows. PostgreSQL 17 hash indexes |
May reduce index size for long values, but bucket overflow and rechecks affect the actual cost. The manual identifies equality scans on larger tables in SELECT- and UPDATE-heavy workloads as a suitable case. |
| MySQL 26.7 | Most indexes, including primary-key, unique, and ordinary indexes, are B-trees. Hash indexes are supported for MEMORY tables; the documented equality comparisons are = and <=>. MySQL 26.7 index usage · MySQL 26.7 B-tree and hash comparison |
Do not generalize the MEMORY-table behavior to InnoDB or all MySQL storage engines. |
| SQL Server, memory-optimized tables | Microsoft documents hash and nonclustered indexes in the memory-optimized-table context. Bucket count is a design choice informed by distinct key values; an undersized count can affect DML and recovery as well as equality tests. SQL Server index design guide · SQL Server hash-index troubleshooting | Use Microsoft’s bucket-count and distinct-key guidance for this specific context, not as a universal rule for other databases. |
Why key distribution and index size matter
In PostgreSQL, storing a 4-byte hash value instead of the original indexed value can make the index smaller when keys are long. But the original value is not in the hash index, so a scan is lossy: the database must check candidate rows to confirm matches. A smaller index therefore does not automatically mean a faster query.
Distribution matters as well. PostgreSQL says hash indexes are most suitable for unique or nearly unique values, or for a low number of rows per bucket. When a bucket fills, overflow pages are added; scans may need to follow them, and a poorly balanced index can require more block accesses than a B-tree. Duplicate-heavy data can therefore undermine the appeal of hashing.
How to decide for your workload
- List the operators your queries use. Separate equality lookups from ranges, sorting, and other operations. If the workload needs range scans, a hash index is not the answer.
- Confirm support for your engine, version, and table type. PostgreSQL’s persistent hash indexes, MySQL’s
MEMORY-table hash indexes, and SQL Server’s memory-optimized-table hash indexes are different contexts. - Check key shape and cardinality. Estimate distinct values and how many rows are likely to share a value or bucket. For SQL Server memory-optimized hash indexes, account for expected distinct keys when sizing buckets.
- Compare plans and timings on representative data. Test the hash index against a B-tree alternative using the database version, schema, data distribution, and query mix you actually use.
- Include writes and operations in the comparison. Measure insert and update behavior, maintenance, memory or bucket configuration where applicable, and recovery—not just a single equality lookup.
Vendor documentation describes supported behavior and possible advantages, not a speedup for every deployment. Keep a hash index only when measurements show it helps the real workload without compromising required query operators or operational needs.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Common questions
Can a hash index serve a range query?
No. Hash indexes do not preserve key order for range scans. PostgreSQL documents support for =; MySQL’s comparison page describes = and <=>.
Rank #3
Can a PostgreSQL hash index enforce uniqueness?
No. PostgreSQL hash indexes do not allow uniqueness checking, so use an appropriate unique constraint or index when duplicate prevention is required.
Does MySQL use hash indexes by default?
No. MySQL 26.7 documents B-trees for most indexes, with hash indexes supported for MEMORY tables. The behavior depends on the table’s storage engine.
Will a hash index always make equality lookups faster?
No. The result depends on the engine, data distribution, query plan, and write and operational costs. Benchmark against the B-tree alternative on representative data.
Quick Recap
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.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problems
