Compare which conditions an index can support
A hash index uses a hash code derived from a value to locate candidates. A B-tree-family index organizes ordered keys so that ranges can be narrowed. Both can avoid scanning every table row, but they do not support identical predicates. This article uses ordinary PostgreSQL 18 indexes as the concrete environment. Do not transfer an in-memory hash table’s expected O(1) label directly into a guarantee about complete database query time. Index-page access, heap access, visibility checks and the number of qualifying rows still contribute work. Begin with the query’s operator and required output, then ask which parts the index can actually support.
| PostgreSQL 18 requirement | Hash index | B-tree index |
|---|---|---|
| Simple equality | Supported with compatible conditions | Supported with compatible conditions |
| Ranges | Not the basic supported predicate | Supported with compatible conditions |
| Ordered output | Does not provide key order | Can support compatible ordering |
Equality can make both candidates
For a simple condition such as WHERE customer_id=42, both B-tree and hash can be candidates under the supported data type and operator class. A matching hash code does not establish equality of original values, so correct handling must account for collisions. If customer 42 owns a large fraction of the table, using an index can also be less attractive than scanning. Thus an equality predicate does not automatically imply that a hash index is the best plan. Consider distribution, table and index sizes, requested columns and result cardinality. The database’s complete access path matters, not merely the conceptual number of dictionary lookups for one isolated key.
Ordering supports ranges and ordered output
A request filtering created_at from a given time and ordering by created_at can benefit from an appropriately ordered B-tree index. PostgreSQL hash indexes support simple equality comparisons rather than obtaining that range or order from the same index. Composite B-tree key order adds another requirement. An index on (tenant_id, created_at) relates naturally to locating records for one tenant in time order; it is not interchangeable with every permutation of those columns. Existence of an index is separate from compatibility with the predicate and requested order. Inspect the actual operator class, keys and plan rather than assuming that an index must be useful simply because it contains a familiar column name.
Account for index maintenance as well as reads
An additional index can add maintenance work during inserts and updates, page management, WAL volume and storage use. If several index types cover the same column, establish what each improves for the actual workload. A B-tree supporting both equality and ranges does not prove that one index suffices for every application; a hash index specializing in equality does not prove it is always smaller or faster. Compare read-write mix, duplicate keys, key width, working set and cache state. Separate conceptual properties from the product’s specific restrictions and operational costs. The decision needs to account for changes paid by writers as well as access-path benefits observed by readers.
Validate the choice with execution plans
Use EXPLAIN to inspect candidate plans. When execution evidence is needed, remember that EXPLAIN ANALYZE actually runs the statement. Compare estimated and actual rows, loops and relevant buffer metrics, rather than treating the appearance of an index name as success. Test data can differ from production in skew even when row counts match, so verify distribution as well as size. Choose an appropriate environment for experiments, especially those involving writes or removing indexes, and understand their scope. The objective is to improve a defined equality, range, ordering or maintenance requirement, then validate the smallest useful index set under that requirement. A conceptual preference is a hypothesis, not a measured result.