2009/5/11 Pavel Stehule <pavel.stehule@gmail.com>:
> I am thinking so Grouping Sets based on CTE should be more commitable
> code. It doesn't mean so your ideas are wrong, but these
> optimalization should to work on CTE too.
>
> select * from table group by rollup(a,b,c)
>
> have to have generate same plan as
>
> with q as (select * from table)
> select * from q group by a,b,c
> union all
> select * from q group by a,b
> union all
> select * from q group by a
> union all
> select * from q;
>
> and CTE is more general then Grouping Sets, so it is better do
> optimalization over CTE than Grouping Sets.
If you need to buffer tuples from the outer plan and to rescan it
multiple times, tuplestore seems more appropriate solution than using
CTE node, from semantic point of view. During CTE and window functions
development, tuplestore now has that kind of capability and CTE node
is only a wrapper of tuplestore.
Moreover, I guess you don't even need to buffer tuples to aggregate by
different keys. What you have to do is only to prepare more than one
hash tables (, or set up sort order if the plan detects hash table is
too large to fit in the memory), and one time seq scan will do. The
trans values are only to be stored in the memory, not the outer plan's
results. It will win greately in performance.
Regards,
--
Hitoshi Harada