Re: Pull up aggregate sublink (was: Parameterized aggregate subquery (was: Pull up aggregate subquery))

Yeb Havinga <yebhavinga(at)gmail(dot)com> writes:
> A few days ago I read Tomas Vondra's blog post about dss tpc-h queries
> on PostgreSQL at
> http://fuzzy.cz/en/articles/dss-tpc-h-benchmark-with-postgresql/ - in
> which he showed how to manually pull up a dss subquery to get a large
> speed up. Initially I thought: cool, this is probably now handled by
> Hitoshi's patch, but it turns out the subquery type in the dss query is
> different.

Actually, I believe this example is the exact opposite of the
transformation Hitoshi proposes. Tomas was manually replacing an
aggregated subquery by a reference to a grouped table, which can be
a win if the subquery would be executed enough times to amortize
calculation of the grouped table over all the groups (some of which
might never be demanded by the outer query). Hitoshi was talking about
avoiding calculations of grouped-table elements that we don't need,
which would be a win in different cases. Or at least that was the
thrust of his original proposal; I'm not sure where the patch went since
then.

This leads me to think that we need to represent both cases as the same
sort of query and make a cost-based decision as to which way to go.
Thinking of it as a pull-up or push-down transformation is the wrong
approach because those sorts of transformations are done too early to
be able to use cost comparisons.

regards, tom lane