Re: strange explain analyze output

On Thu, 2008-08-28 at 00:42 -0400, Tom Lane wrote:
> The reason that these statements are not inconsistent is that the
> Sort is the inner relation for a mergejoin. In the presence of
> duplicate keys in the outer relation, a mergejoin will "rewind" and
> rescan duplicate keys in the inner relation; that is, any row in the
> inner relation will be fetched approximately as many times as it has
> matches in the outer relation. So it looks like you've got roughly
> 1000X duplication in these tables?
>

Thanks for the explanation.

> (BTW, the planner knows that this is expensive and will avoid mergejoins
> when there are many duplicate keys. But apparently a hash join seemed
> even worse for the stats of this particular pair of tables.)
>

Then wouldn't the planner have estimated more rows returned by the sort
(including rescanned rows) than the HashAgg? It estimated exactly the
same number as it estimated for the output of the HashAgg.

Regards,
Jeff Davis