--plan 1, 10 seconds were spent on sequential scan on term_weekly table.
dev=# explain analyze select distinct cs_id from lookup_weekly n inner join term_weekly s on s.b_id=n.b_id and s.date=n.date where term in ('cat'::text);
--plan 2, only 1 second spent on index scan of term_weekly table, however, as it selects the big table to do the hashing, it takes 22 seconds for the hash to complete. The advantage get from index has been totally lost because of this join order.
Am I reading something wrong here? I haven't looked all the plan, but the second is faster (overall), so why do you think you need a hint or change what the planner choose? For me looks like using the index is the best for this situation. Could you try running this multiple times and taking the min/max/avg time of both?
The difference in time could be a caching effect, not a reproducible difference.
The 2nd plan uses 3GB of memory, and there might be better uses for that memory.
Currently memory is un-costed, other than "cliff costing" once you thinks it will exceed work_mem, which I think is a problem. Just because I will let you use 4GB of memory if you will really benefit from it, doesn't mean you should use 4GB gratuitously.
Suya, what happens if you lower work_mem setting? Does it revert to the plan you want?
