2009/5/11 Pavel Stehule <pavel.steh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers