Re: hash join vs nested loop join

>
> With a low cache hit rate, that would generally be when the number
> of lookups into the table exceeds about 10% of the table's rows.
>
>
> So far, my main performance issue comes down to this pattern where
Postgres chooses hash join that's slower than a nest loop indexed join. By
changing those cost parameters, this query works as expected now, but there
are others fall into the same category and appear to be harder to convince
the optimiser.

I'm still a bit worried about this query as Postgres gets the record count
right, and knows the index is a primary key index, therefore it knows it's
0.05m out of 170m records (0.03%) but still chooses the sequential scan.
Hopefully this is just related to that big index penalty bug introduced in
9.2.