Hash Indexes vs B-Trees: Which Queries Do They Support?

Updated 10 November 2026 · Standard formula, shown below

Hash indexes are designed for equality lookups, such as finding rows where a key exactly matches a value; they generally do not support range searches or ordered output. B-tree indexes support equality too, and also support ranges, sorting, and prefix searches when the indexed columns and query conditions line up.

How the Indexes Work

An index is an auxiliary data structure that helps a database locate rows without scanning every row in a table. The database still has to maintain the index when indexed values are inserted, changed, or deleted, and an index does not guarantee that every query will use it: the query planner chooses a plan based on costs and available information.

A hash index applies a hash function to an indexed key and uses the result to find a bucket that may contain matching entries. Hash values are not arranged in the same order as the original keys. A B-tree index instead stores keys in a balanced, sorted tree structure, allowing the database to navigate to a key and then continue through nearby keys in order. Many database systems use B-tree variants, such as B+ trees, rather than a textbook B-tree in the narrow sense.

Queries a Hash Index Supports

The core use of a hash index is an equality comparison on the indexed key. For example, an index on account_id may help with:

WHERE account_id = 4821
WHERE account_id IN (4821, 4822), which can be handled as multiple equality lookups in systems that support this use.

Hash indexes are not inherently useful for finding values below or above a boundary, such as account_id > 4821, or for returning rows in key order. Hashing discards the key order needed to locate a contiguous range. A query that asks for both an equality and a range on different columns may still use a hash index for the equality portion, but that depends on the database and the full query plan.

Support is implementation-specific. Some database products have restrictions on hash indexes, and some do not offer them as a general-purpose index type. Their behavior for uniqueness, persistence, replication, and crash recovery also varies. Check the documentation for the particular database rather than assuming every hash index has identical capabilities.

Queries a B-Tree Index Supports

Because B-tree keys are kept in sorted order, a B-tree can serve exact-match queries as well as many ordered and range queries. For an index on created_at, typical useful conditions include:

Equality: WHERE created_at = '2026-05-01'
Ranges: WHERE created_at >= '2026-05-01' AND created_at < '2026-06-01'
Ordering: ORDER BY created_at, sometimes avoiding a separate sort.
Minimum or maximum values: for example, ORDER BY created_at DESC LIMIT 1.

A B-tree can also support a left-anchored prefix search on text in suitable collations and database configurations. A condition such as name LIKE 'Sam%' may be indexable, while name LIKE '%Sam' usually cannot use an ordinary B-tree to seek directly to matching values because the beginning of the string is unknown. Exact behavior depends on the database, collation, operator, and index definition.

Composite B-tree indexes (indexes over multiple columns) are most effective when a query uses the leading, or leftmost, columns. An index on (team_id, joined_at) can commonly help find a team and scan its members by join date. It may not efficiently help with a condition on joined_at alone. Some systems can use skip-scan or other optimizations, but these are not a substitute for understanding the index’s column order.

Quick Comparison

Query pattern	Hash index	B-tree index
Exact equality	Yes, its primary use	Yes
Greater-than or less-than range	Generally no	Yes
Return rows sorted by key	No	Often, if the ordering matches
Find first or last key	No ordered traversal	Often
Prefix text match	Not as an ordered prefix scan	Often, subject to conditions

These are capabilities, not promises of speed. A database may prefer a table scan when a query returns a large share of the table, when an index is poorly selective, or when fetching table rows through the index would cost more than scanning them.

Choosing an Index and Understanding Costs

Choose based on the actual query patterns, not on the assumption that one index type is universally faster. A B-tree is a sound general-purpose choice when a column is used for equality and range comparisons, ordering, or a mix of those operations. A hash index may be appropriate when workloads are dominated by equality lookups and the database’s hash-index implementation fits the application.

List the important filters, joins, and sort orders used by the application.
Check whether queries use expressions or casts that prevent ordinary index use.
Inspect query plans and, where possible, compare them with representative data and workloads.
Account for storage and write costs: every additional index consumes space and adds work to data changes.

Do not create an index just because a column appears in a query. A low-selectivity condition, such as a boolean column with most rows sharing one value, may not benefit much from a simple index. A composite index, a partial index, or another specialized index may fit better, but those choices depend on database features and query shape.

Common Misconceptions and Limitations

“Hash is always faster for equality.” Not necessarily. Real performance depends on the implementation, data distribution, cache behavior, index size, concurrency, and workload. A B-tree also supports equality and can be competitive or preferable for many ordinary workloads.

“If an index supports a query, the database must use it.” The planner may choose a sequential scan or another index if it estimates that plan to be cheaper. Statistics, parameter values, and the number of matching rows can affect that decision.

“An index makes writes free.” Indexes add maintenance work to inserts, updates, and deletes and may increase storage and backup size. Too many indexes can slow write-heavy workloads.

Finally, index support is not identical across database systems. Operator support, null handling, uniqueness, index-only scans, text collation rules, and operational behavior differ. Confirm the details in the documentation and test with the real query plan for the database in use.

Frequently Asked Questions

Can a hash index be used for a range query?
Usually not. Hashing does not preserve key order, so a hash index cannot efficiently walk values between two bounds in the way a B-tree can.

Can a B-tree index do exact-match lookups?
Yes. B-trees support equality lookups as well as range scans and ordered access, which is why they are common general-purpose indexes.

Which index is better for a primary key?
It depends on the database and workload, but B-tree indexes are common for primary keys because they support equality, ordering, and range operations. Follow the database’s documented behavior for primary-key indexes.

Does a B-tree help with LIKE '%text%'?
An ordinary B-tree generally cannot seek efficiently for a pattern that starts with a wildcard. Full-text, trigram, or other specialized indexes may help, depending on the database and the search requirements.

Why is my query not using its index?
Possible reasons include low selectivity, a table small enough to scan, a function or type conversion on the indexed column, mismatched composite-index column order, stale statistics, or a planner estimate that favors another plan. Check the execution plan before changing indexes.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.