PostgreSQL’s default B-tree index supports several common query requirements: equality, ranges, ordered access, and uniqueness enforcement. A hash index supports a narrower equality access path. Choosing between them starts with the required operators and constraints, then proceeds to measurement. Saying that hashes are always faster for equality, or that PostgreSQL almost never chooses them, replaces a workload question with an unsupported generalization.

Capabilities before timings

A B-tree retains ordering under its operator class and collation. That can support a bounded range and an ORDER BY without a separate sort, as well as a single-key equality lookup. B-trees can also support IS NULL and IS NOT NULL. An index’s existence still does not force its use: reading a large fraction of a table may favor another plan.

Text patterns have additional requirements. A constant anchored prefix such as LIKE 'abc%' can use an appropriate B-tree access path, but collation and operator class matter. Outside the C locale, a pattern-specific operator class may be necessary. A leading wildcard is not made into a selective ordered range merely by creating a normal B-tree index.

A PostgreSQL hash index stores a four-byte hash code rather than the complete indexed value. It supports equality, lacks key ordering, supports only a single indexed column, and cannot enforce uniqueness. Its smaller entries can help some equality workloads with large values, but heap verification and bucket behavior still contribute to cost. The data structure alone does not establish the winner.

When it breaks

Replacing a B-tree with a hash index can remove useful range or ordered access. It also removes an access path for IS NULL: PostgreSQL hash indexes do not support that predicate. Replacing the index behind a uniqueness requirement is a more serious mistake because a hash index cannot supply the required contract at all.

Null semantics deserve a separate check. A default unique B-tree permits multiple nulls because they are treated as distinct for uniqueness. If the application requires at most one null, that must be expressed with the appropriate constraint semantics, not inferred from the word unique. Supported access operations and enforced data rules are related but different decisions.

Check it quickly

Run this experiment with PostgreSQL 18 and psql. The temporary table contains twenty thousand integer keys plus two null-key rows. It first has a hash index, then replaces that index with a unique B-tree. Rollback removes the experiment.

\set ON_ERROR_STOP on
BEGIN;
CREATE TEMP TABLE points (key integer,value text);
INSERT INTO points SELECT g,'value-'||g FROM generate_series(1,20000) AS g;
INSERT INTO points VALUES (NULL,'no-key-a'),(NULL,'no-key-b');
CREATE INDEX points_hash ON points USING hash(key);
ANALYZE points;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT value FROM points WHERE key=12345;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT key,value FROM points WHERE key>=12340 AND key<12350 ORDER BY key;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT value FROM points WHERE key IS NULL;
DO $$ BEGIN
  CREATE UNIQUE INDEX points_hash_unique ON points USING hash(key);
  RAISE EXCEPTION 'hash uniqueness unexpectedly supported';
EXCEPTION WHEN feature_not_supported THEN RAISE NOTICE 'hash cannot enforce uniqueness';
END $$;
DROP INDEX points_hash;
CREATE UNIQUE INDEX points_btree ON points(key);
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT value FROM points WHERE key=12345;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT key,value FROM points WHERE key>=12340 AND key<12350 ORDER BY key;
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT value FROM points WHERE key IS NULL;
DO $$ BEGIN
  IF (SELECT value FROM points WHERE key=12345) IS DISTINCT FROM 'value-12345' OR
     (SELECT count(*) FROM points WHERE key>=12340 AND key<12350) <> 10 OR
     (SELECT count(*) FROM points WHERE key IS NULL) <> 2 THEN RAISE EXCEPTION 'index changed answers'; END IF;
END $$;
ROLLBACK;

On PostgreSQL 18.6, the hash equality query used points_hash and returned value-12345. The ten-key range used a sequential scan plus sort, while the null query used a sequential scan returning two rows. Attempting a unique hash index raised the expected unsupported-feature exception, which the block catches deliberately; any different failure still stops the script.

After replacement, the B-tree supported the equality, bounded ordered range, and null lookup in this fixture. The range returned ten rows and the null lookup still returned two. Building the unique index succeeded despite those nulls, matching PostgreSQL’s default null-distinct uniqueness behavior. Assertions verify that changing access paths did not change query answers.

Plan choice and page counts can vary with relation size, cache state, statistics, and configuration. This experiment establishes supported operations and an observed selection, not a benchmark proving one method faster on every machine. The unsupported uniqueness capability does not become supported when costs or data sizes change.

Measure the complete workload

For a real candidate, compare representative equality lookups, range queries, ordering requirements, write activity, storage, and maintenance. Test alternatives in a controlled environment instead of building competing large indexes casually on production. A hash index may earn its storage on a measured equality workload, while the B-tree may still be required for uniqueness or other queries. Keep every retained index tied to an actual access or integrity requirement, and account for its write cost as well as its read benefit.

Sources