Re: query plan question, nested loop vs hash join

Andrey Lizenko <lizenko79(at)gmail(dot)com> writes:
> What is the reason of "Seq Scan on activities_example" in the first case?
> Is it possible to force optimizer choose the second plan without doing
> "set enable_hashjoin = off;" ?

Disabling hashjoins altogether would be a pretty dangerous "fix".

I think the real issue here is that you have an entirely cached-in-memory
database and therefore you ought to reduce random_page_cost. The
planner's estimates for the first query seem to more or less match reality
(on the assumption that 1 msec equals about 100 cost units on your
machine). The cost estimates for the second one are way off though,
mainly in that the repeated indexscans are far cheaper than the planner
thinks. Getting that cost estimate down requires reducing random_page_cost
or increasing effective_cache_size or some combination.

You can find the conventional wisdow about this sort of thing at
https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

regards, tom lane