Re: Slow joins against set-returning functions - Mailing list pgsql-performance

From Tom Lane
Subject Re: Slow joins against set-returning functions
Date
Msg-id 28003.1092590490@sss.pgh.pa.us
Whole thread Raw
In response to Slow joins against set-returning functions  (Michael Fuhr <mike@fuhr.org>)
List pgsql-performance
Michael Fuhr <mike@fuhr.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

pgsql-performance by date:

Previous
From: Martin Foster
Date:
Subject: Re: Faster with a sub-query then without
Next
From: Ole Tange
Date:
Subject: Help interpreting explain analyze output