On Sat, Oct 17, 2009 at 10:09 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Robert Haas <robertmh...@gmail.com> writes:
>> That still leaves a lot of silly paths, though. In many cases, if
>> you're thinking about joining A to {B C} using an index-accelerated
>> path, you'd be just as well off joining to B first and then to C. So
>> it might be that we only need to consider index-accelerated paths when
>> there is no legal join between the LHS and a subset of the RHS.
>
> Yeah. If there are no join order constraints, it's always possible to
> form a plan such that you join a given rel only once you have all the
> rels needed (to provide values for the inner indexscan) on the lefthand
> side. I think this is probably the main reason why the issue was not
> treated in the planner originally. So maybe the way to think about
> this is as a way of dealing with join order constraints without losing
> the benefits of inner indexscans.
>
>> The other problem I see here is that the bottom-up approach that we
>> use in general is going to be difficult to apply here, because the set
>> of paths will vary depending on what parameters are pushed down from
>> the outer side.
>
> Well, we deal with that already --- the set of possible inner indexscan
> paths already varies depending on what the LHS is. I think the point
> here is that we'd be considering an inner path that's against an LHS
> that it's not legal to join the inner rel to *directly*. Such a path
> would only be legal if we later join to that LHS at a higher join level.
> So we'd be keeping around some tentative paths that might not ever form
> a valid join plan.
>
> Maybe we should turn around the way that inner indexscan paths are
> made. Currently we form them on-the-fly while considering a valid
> join combination. Maybe we should build them all at the first level
> (driving this off the set of available join clauses for each base rel)
> and mark each such path as "requires a join to this other set of rels
> to be valid". But then we'd go ahead and join such paths to *other*
> rels, keeping the resulting join paths still marked as requiring the
> same future join. Once that join actually happens, the resulting path
> becomes fully valid. Only a join to a proper subset of the future-join
> requirement would be disallowed meanwhile.
>
> I'm not even sure this would be slower or more complicated than what we
> do now --- if you look at the logic that caches potential inner
> indexscan plans, it's almost doing this already. It would result in
> considering more join paths, but only ones that have some plausible use.
Wow, that's pretty sneaky. I had to read this email twice before I
understood what you were proposing. It sounds to me like it will
work. I'm not 100% sure what the impact on planner performance will
be, but it's at least plausible that it will be OK.
I think you should only ever join an incomplete inner-indexscan path
to (1) another inner-indexscan path or (2) the cheapest total path for
*exactly* the future-join requirement. Anything else doesn't seem
productive.
...Robert
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
