Re: Pull up aggregate subquery

2011/5/5 Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>:
> https://commitfest.postgresql.org/action/patch_view?id=548
>
> I'll work further if I find time.

After more thought, pulling up aggregate subquery in narrow
conditional cases is quite hard path, especially when the joinrel is
more than 2. It will be hard to check pulling up is safe for other
relations than the target relation.

It was a big shame I missed Tom Lane's session in PGCon, but finding
"Parameterized Scan" in his slides, it occurred to me that it might
help my problem, too. Before hitting the "pull up" idea, I once
thought if it would be possible to push outer Var of join down to
Agg's HAVING, which is transferred to underlying SeqScan's filter.
Resulted in something like:

NestLoop
-> SeqScan M (filter: M.val = '1')
-> GroupAggregate
-> SeqScan M (filter: L.m_id = M.id)

However, currently we don't have such mechanism to push down Var as a
qual to non-NestLoop. Yeah, it could be even now, but we should avoid
N-loop of Agg. We want to scan Agg once, with Param $1 = M.id =
multiple values. Since I didn't attend his session I'm afraid I don't
understand "Parameterized Scan" correctly, but once we've got such
mechanism, one example introduced in Robert Haas's blog[1] (originally
shown by Andrew Gierth[2]) and LATERAL maybe.

Do I understand correctly? If so, could someone explain more detail of
how to get Parameterized Scan in the planner?

Regards,

[1]: http://rhaas.blogspot.com/2010/04/finding-our-way-to-lateral.html
[2]: http://archives.postgresql.org/pgsql-hackers/2009-09/msg00525.php

--
Hitoshi Harada