Re: Pull up aggregate subquery

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Tue, May 24, 2011 at 11:11 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> The point I was trying to make is that the ultimate reason for having a
>> parameterized portion-of-a-plan will be that there's a parameterized
>> indexscan somewhere down at the bottom.

> Oh, I see. I have a general gripe with nested loop plans: we already
> consider too many of them. IIRC, when I last fooled around with this,
> the number of nested loop paths that we generate far exceeded the
> number of merge or hash join paths, and most of those paths suck and
> are a complete waste of time.

Hm, really? My experience is that it's the mergejoin paths that breed
like rabbits, because there are so many potential sort orders.

>> But I think this is all fairly unrelated to the case that Hitoshi is on
>> about. As you said earlier, it seems like we'd have to derive both
>> parameterized and unparameterized plans for the subquery, which seems
>> mighty expensive.

> That was my first thought, too, but then I wondered if I was getting
> cheap.

Yeah, it's certainly possible that we're worrying too much. Usually
I only get concerned about added planner logic if it will impact the
planning time for simple queries. "Simple" tends to be in the eye of
the beholder, but something with a complicated aggregate subquery is
probably not simple by anyone's definition.

In this case the sticky point is that there could be multiple possible
sets of clauses available to be pushed down, depending on what you
assume is the outer relation for the eventual upper-level nestloop.
So worst case, you could have not just one parameterized plan to
generate in addition to the regular kind, but 2^N of them ...

regards, tom lane