Re: Pull up aggregate subquery

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Mon, May 23, 2011 at 4:02 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 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.

> I must be misunderstanding you, because index scans are the thing we
> already *do* parameterize; and what else would make any sense?

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. I had originally imagined that
we might parameterize any old scan; for example consider replacing

Nestloop
Join Filter: a.x = b.y
-> Seqscan on a
-> Seqscan on b

with

Nestloop
-> Seqscan on a
-> Seqscan on b
Filter: b.y = a.x

Although this isn't nearly as useful as if we could be using an index on
b.y, there would still be some marginal gain to be had, because we'd be
able to reject rows of b without first passing them up to the join node.
But I'm afraid that going all-out like that would slow the planner down
far too much (too many Paths to consider) to be justified by a marginal
runtime gain.

So the idea I have at the moment is that we'll still only parameterize
indexscans, but then allow those to be joined to unrelated relations
while still remaining parameterized. That should reduce the number
of parameterized Paths hanging around, because only joinclauses that
match indexes will give rise to such Paths.

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.

regards, tom lane