A hash index is useful only when its supported operations and access costs fit the query. In PostgreSQL 17, a hash index supports equality comparisons on one column, but it stores only a four-byte hash—not the original value—and scans can require table-row checks. The index therefore does not guarantee a faster query. This guide focuses on PostgreSQL 17; MySQL’s hash-index support is different and is limited to MEMORY tables.
Why a hash index may not improve performance
An index can reduce the work needed to find rows, but it does not eliminate the cost of retrieving those rows or make every kind of predicate faster. PostgreSQL’s planner chooses a plan based on the query and data; a sequential scan or another access path can be cheaper when using an index would still require many table visits. The PostgreSQL documentation describes plan selection as matching “the query structure and the properties of the data.” PostgreSQL: Using EXPLAIN.
As an Amazon Associate I earn from qualifying purchases.
The predicate does not match the index
PostgreSQL hash indexes support equality (=) on a single indexed column. They are not an access path for range predicates such as < or BETWEEN, and they do not provide ordering or enforce uniqueness. If the query asks for a range, sorted results, or a uniqueness guarantee, a hash index is the wrong tool for that requirement. PostgreSQL: Hash Indexes.
A hash match is not necessarily a row match
PostgreSQL stores a four-byte hash value rather than the indexed column’s original value. Hash scans are lossy: a matching hash may require PostgreSQL to visit the table row and recheck the actual value. If a query also needs other columns, those rows must be fetched too. The compact representation can be useful for long keys, but it does not guarantee a faster end-to-end query.
#1 Best Overall
- hardcover, brand new
Bucket overflow adds work
When a hash bucket fills, PostgreSQL links overflow pages to it. A scan of that bucket must traverse the chain, so the lookup can involve more page work than a simplified description of “one hash lookup” suggests.
The alternative plan may be cheaper
If a predicate matches many rows, fetching them through an index can cost more than scanning the table. A small table, broad equality match, write-sensitive workload, or query that retrieves many non-indexed columns may likewise fail to benefit. These are workload-dependent trade-offs, not proof of a planner bug; compare the estimates with actual execution before drawing that conclusion.
Rank #2
- Brand: McGraw-Hill Education
- Database System Concepts, 7th Edition
Diagnose the query in PostgreSQL
- Record the environment and query. Note the database product and version, table and storage type, index definition, exact SQL and bind values, table size, and whether the workload is read-heavy or update-heavy. Product and storage-engine differences matter.
- Verify operator fit. For a PostgreSQL hash index, check that the query uses equality on the indexed column. Do not expect it to serve range conditions, ordering, or uniqueness checks.
- Inspect the chosen plan. Run
EXPLAINto see the planned access path. To observe actual execution, useEXPLAIN ANALYZE, optionally withBUFFERSto see buffer activity:
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
EXPLAIN ANALYZE executes the statement. Use care with statements that have side effects, and use a safe environment or a read-only query when appropriate. PostgreSQL: Using EXPLAIN.
- Compare estimated and actual work. Check estimated rows against actual rows, identify the scan node PostgreSQL selected, and review buffer hits and reads alongside execution time. Large estimate errors are worth investigating before changing indexes. Planner estimates and costs depend on statistics and the platform.
- Compare like with like. If evaluating an index change or alternative, use the same query and data and keep cache conditions and measurement method consistent. Treat a speedup as established only if it appears in measurements representative of the target workload.
- Account for the full retrieval cost. Ask how many rows qualify and whether the query needs columns absent from the hash index. Row visits and rechecks may dominate the lookup. Compare a B-tree or another suitable access path if the query needs broader capabilities, then measure rather than assuming which will be faster.
How to compare hash and B-tree indexes
Choose by workload and required operations, not by the index name alone. PostgreSQL’s documentation establishes the hash index’s single-column equality support, lack of uniqueness enforcement, lossy scans, and four-byte stored hash; the speed and size trade-offs for a particular workload must be measured.
| Comparison | PostgreSQL hash index | What to check in an alternative such as a B-tree |
|---|---|---|
| Operators | Equality (=) only |
Whether it supports the query’s equality, range, or ordering needs |
| Columns and uniqueness | One indexed column; does not enforce uniqueness | Whether the needed column combination and uniqueness behavior are supported |
| Stored data and row access | Four-byte hash, not the original value; scans are lossy and may require row checks | Whether the index can supply values the query needs and how many table visits remain |
| Size and runtime | Compact hash entries, but actual size and performance depend on key width and workload | Measure index size, plan, buffer activity, and runtime on representative data |
No universal speedup percentage or row-count threshold follows from PostgreSQL’s documentation. A narrow equality lookup may suit a hash index, but whether it beats another plan depends on the actual data, query, and execution conditions.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.PostgreSQL and MySQL do not mean the same thing by hash index
PostgreSQL 17 documents persistent, on-disk hash indexes that are crash-recoverable. They support one column and equality, do not enforce uniqueness, and store a four-byte hash rather than the indexed value. PostgreSQL: Hash Indexes.
MySQL’s documentation says most MySQL indexes are B-trees and identifies MEMORY tables as supporting hash indexes. Its comparison describes hash indexes as suitable for equality operators = and <=>. Do not assume that a typical MySQL/InnoDB table can use the same persistent hash index described for PostgreSQL; check the storage engine and product documentation for the table in question. MySQL: Indexes: B-Tree and Hash · MySQL: The MEMORY Storage Engine.
Quick Recap
Best Value
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.
