A hash index helps only when the database can use its supported operation and the resulting lookup costs less than the alternatives. In PostgreSQL 17, hash indexes support single-column equality searches, but they store only a four-byte hash—not the original value—and may need to check matching table rows. The planner can therefore choose another plan, and an index that appears suitable may not make the full query faster.
This guide focuses on PostgreSQL 17’s persistent hash indexes. MySQL’s hash-index support is different: the MySQL manual describes hash indexes for MEMORY tables, while most MySQL indexes are B-trees.
What a PostgreSQL hash index can—and cannot—do
PostgreSQL 17 hash indexes are persistent, on-disk indexes that are crash recoverable. They index one column and support equality comparisons with =. They do not support range searches or enforce uniqueness. See the PostgreSQL 17 hash index documentation.
A hash index stores a four-byte hash value rather than the original column value. That compact representation can be useful for long keys, but hash scans are lossy: a matching hash may require PostgreSQL to fetch the table row and check the original value. The index lookup is only one part of the query’s cost.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →Why the index may not make the query faster
The predicate does not match the index
A PostgreSQL hash index is an option for a single-column equality predicate such as WHERE account_code = 'A17'. It is not a suitable access path for a range condition such as WHERE created_at >= DATE '2026-01-01', nor does it provide ordering or uniqueness enforcement.
The planner expects another plan to cost less
PostgreSQL’s planner considers the query structure and data properties when selecting a plan. If a query is expected to return many rows, navigating an index and fetching those rows can cost more than scanning the table. A sequential scan is not automatically evidence of a problem. The PostgreSQL guide notes that “Choosing the right plan to match the query structure and the properties of the data is absolutely critical for good performance”; the system uses a planner to choose plans. See Using EXPLAIN.
Fetching and checking rows dominates the lookup
Because the hash index does not hold the original column value, PostgreSQL may need to visit table rows and recheck matches. If the query returns many rows or requests additional columns, that retrieval work can outweigh the compact index lookup.
The bucket has overflow pages
When a hash bucket fills, PostgreSQL chains overflow pages to it. Scanning that bucket requires traversing the chain, adding work beyond examining the primary bucket page. This is an implementation detail to consider when interpreting a plan and runtime, not proof by itself that a particular index is slow.
Recommended Free Tools
The workload is a poor fit, or the comparison is unfair
A narrow equality lookup on a large table is the natural shape for a PostgreSQL hash index, but it is not a guarantee of lower latency. A small table, a broad match, a write-sensitive workload, or a query that needs many table columns can change the trade-off. Results also depend on the data and measurement conditions; the official documentation does not promise a general speedup or provide a universal threshold.
Diagnose the query in PostgreSQL
- Record the environment. Note the PostgreSQL version, table and index definitions, exact SQL and bind values, table size, and whether the workload is read-heavy or update-heavy. If you are using another database, record its product and storage engine too; the same phrase “hash index” does not imply the same implementation.
- Check that the operator fits. For PostgreSQL, verify that the predicate is a single-column equality using
=. Do not expect a hash index to serve a range condition, ordering requirement, or uniqueness check. - Inspect the chosen plan. Run
EXPLAINwith the query to see the plan PostgreSQL expects. To observe actual row counts and execution time, useEXPLAIN (ANALYZE, BUFFERS).ANALYZEexecutes the statement, so take care with statements that have side effects. - Compare estimated and actual work. Check estimated rows against rows produced, the scan node selected, buffer hits and reads, and execution time. Large estimate errors are a reason to investigate planner estimates before changing indexes; estimates and costs depend on statistics and the platform.
- Compare equivalent runs. If testing a hash index against no index or another index, keep the query, data, cache conditions, and measurement method as consistent as possible. Treat this as sound diagnostic practice, not a database-guaranteed benchmark protocol. Do not claim a speedup until it is measured on the target workload.
- Account for everything the query retrieves. Consider how many rows qualify and whether the query needs columns that are not present in the hash index. Table visits and rechecks may dominate. A B-tree may better match broader access needs, but test it against the actual query rather than assuming it will be faster.
How to compare a hash index with an alternative
Compare the indexes against the query’s requirements, not just their names. PostgreSQL’s documented hash-index properties establish the operator and storage distinctions below; actual speed and size depend on the workload and key values.
| Comparison point | PostgreSQL hash index | Questions for an alternative |
|---|---|---|
| Supported operations | Single-column equality with =. |
Does it support the query’s equality, range, or ordering operations? |
| Columns and uniqueness | One column; does not enforce uniqueness. | Can it cover the required columns or enforce a needed uniqueness constraint? |
| Values available from the index | Stores a four-byte hash, not the original value; scans are lossy and may require row checks. | Can it supply the values the query needs, or will table visits still be required? |
| Rows and table visits | Benefit depends on qualifying rows, rechecks, and access cost. | How many rows qualify, and how much retrieval work does the plan require? |
| Index size and observed performance | The hash entry is compact, but no general size or speed advantage is guaranteed. | Measure size and plan/runtime with the actual key width, data, and workload. |
Do not assume MySQL uses PostgreSQL’s hash-index model
MySQL documents that most indexes are B-trees and that MEMORY tables support hash indexes. Its index comparison describes hash indexes as suited to equality operators = and <=>. That is not equivalent to PostgreSQL’s persistent on-disk hash index: confirm the MySQL storage engine and table type before applying PostgreSQL guidance. See the MySQL 8.0 B-tree and hash index comparison and MySQL MEMORY storage engine documentation.
What a measurement can establish
There is no documentation-backed percentage or row-count threshold at which a hash index becomes faster. The manuals describe supported operations, implementation behavior, and planner choices, not a universal latency guarantee. Your query plan and measurements on representative data are the evidence for whether the index helps your workload.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
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.
