Re: Slow joins against set-returning functions

Michael Fuhr <mike(at)fuhr(dot)org> writes:
> Is the planner doing something wrong here?

Hard to see how it can be very smart with no idea about what the
function is going to return :-(.

I'd say that the mergejoin plan is actually a good choice given the
limited amount of info, because it has the least degradation when the
input varies from what you expected. Those "better" nestloop plans
could easily be very far worse, if the function returned more than a
trivial number of rows.

The reason the two mergejoin cases differ so much is that the scan of
the other relation can stop as soon as we've exhausted the function
output. Evidently scanning to key 10 doesn't traverse much of
stuff_pkey while scanning to key 100000 does. The planner is aware of
that effect, but with no information about the maximum key value to be
expected from the function scan, it can't apply the knowledge.

regards, tom lane