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

2011/7/27 Yeb Havinga <yebhavinga(at)gmail(dot)com>:
> On 2011-07-22 17:35, Hitoshi Harada wrote:
>>
>> 2011/7/23 Yeb Havinga<yebhavinga(at)gmail(dot)com>:
>
> 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.
>
> The original and rewritten queries are below. The debug_print_plan output
> shows the subquery is called from a opexpr (< l_quantity, subquery output)
> and the sublink type is EXPR_SUBLINK. Looking at the source code;
> pull_up_sublink only considers ANY and EXISTS sublinks. I'm wondering if
> this could be expanded to deal with EXPR sublinks. Clearly in the example
> Tomas has given this can be done. I'm wondering if there are show stoppers
> that prevent this to be possible in the general case, but can't think of
> any, other than the case of a sublink returning NULL and the opexpr is part
> of a larger OR expression or IS NULL; in which case it should not be pulled
> op, or perhaps it could be pulled up as outer join.
>
> Thoughts?

Good catch. I was not aware of the sublink case so I'm not sure if it
is possible, but I believe it will be worth modifying the optimizer to
handle them in the same way. Since my latest proposal is based on
parameterized NestLoop, the first step is how to transform the sublink
expression into join. I bet there are chances in simple cases since we
have Semi/Anti Join technique. On the other hand, those pseudo-join
types are easily failing to be transformed to join, in such cases
above like it have another filter clause than join qual expression.

If tpc bechmark can be speed up that's a good use case which you
pointed out I'm missing.

Regards,

--
Hitoshi Harada