Re: Cost estimation problem on seq scan in a loop

On Mon, Dec 16, 2013 at 11:41 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> Is there some principled way to go about teaching the planner that hashing
> smallish_table on the join filter key is a cheap insurance policy against
> underestimating the row count of the outer loop?

The problem is that cheap protection can end up being very expensive
when it's the most expensive part of the query and it's repeated many
times. In this query it's expecting thousands of rows. The executor
goes to some effort to avoid having to do unnecessary copies of data
and be able to use it straight out of the disk buffers so having to
copy it an unnecessary time to a hash table would be annoying.

What's more likely, I think is having plan nodes that make decisions
at run-time. There's been some movement in this direction already and
lots of discussion about it. Having a join type that retrieves the
first few rows from the lhs and then decides whether to do a hash or
nested loop on the rhs based on how many it finds might be more
tractable than most other strategies.

--
greg