Postgres does not use HASH index while creating query plan

Question

I have created an HASH index on table t_posts on column topics_id which is the primary key in table t_topics

select * FROM t_topics
 JOIN t_posts ON  t_topics.topics_id = t_posts.topics_id 

When I execute above query the plan generated

Hash Join  (cost=12258.02..346305.42 rows=1813743 width=520)
  Hash Cond: (t_posts.topics_id = t_topics.topics_id)
  ->  Seq Scan on t_posts  (cost=0.00..114209.43 rows=1813743 width=408)
  ->  Hash  (cost=5939.12..5939.12 rows=217112 width=112)"
        ->  Seq Scan on t_topics  (cost=0.00..5939.12 rows=217112 width=112)

Where as the plan should be sequentially scanning t_topics and the use the HASH index on t_posts for t_topics and perform JOIN. Why is the query plan generated like this?

Answer 1

This index would only help in a nested loop join, but the planner chooses to perform a hash join, where an index on the join condition is no help.
Judging from the row count estimates, I'd say that the planner is right.

You can check if your index could be used by a nested loop join by setting enable_hashjoin and enable_mergejoin to off and trying again.