Re: Pull up aggregate subquery

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Sat, May 21, 2011 at 12:49 PM, Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com> wrote:
>> Do I understand correctly? If so, could someone explain more detail of
>> how to get Parameterized Scan in the planner?

> I think we're going to need Tom to give the definitive word on this,
> but I believe that the current situation is that the executor is
> capable of handling a parameterized scan (yeah!) but the planner
> doesn't know how to generate them (boo!). This is an improvement of
> a sort over the 9.0 code base, where neither the planner nor the
> executor could handle this case, but we need planner to support in
> order to get anywhere useful with it.

Yeah. I fixed the executor in 9.1, but got hung up on how to fix the
planner. So the planner still only knows how to do this for the case of
an inner indexscan, ie, it can't handle parameterizing any piece of a
plan larger than a single indexscan. I have some ideas about fixing
that but have had no time to pursue them since December :-(

> ...we'd need to plan the subquery twice, once with a parameterized
> qual m_id = $1 pushed down, and once without that. We could then
> compare the cost of a nest-loop with the qual to the cost of a merge
> or hash join without it. But this seems very expensive. In the
> particular case you have here, the subquery is simple enough that this
> probably wouldn't be any big deal, but in general there's no reason
> why that subquery couldn't be quite complex - or why it couldn't have
> subqueries of its own that would requite the same treatment
> recursively.

Yeah. For simple scan/join queries it seems likely that we only care
about parameterizing indexscans, since the main opportunity for a win is
to not scan all of a large table. Restricting things that way would
help reduce the number of extra Paths to carry around. But I'm not sure
whether the same argument can be made for arbitrary subqueries.

regards, tom lane