Re: materialization blocks hash join

Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> writes:
> That's because eqjoinsel_inner won't have any statistics for either side
> of the join, so it'll use default ndistinct values (200), resulting in
> estimate of 0.5% for the join condition.

Right.

> But this should not affect the choice of join algorithm, I think,
> because that's only the output of the join.

Lack of stats will also discourage use of a hash join, because the
default assumption in the absence of stats is that the join column
has a pretty non-flat distribution, risking clumping into a few
hash buckets. Merge join is less sensitive to the data distribution
so it tends to come out as preferred in such cases.

regards, tom lane