Re: Implementation of GROUPING SETS (T431: Extended grouping capabilities)

Hello all.
Please, approve my ideas for implementation.

Standart has feature T431: Extended grouping capabilities.
This feature i found in TODO-list:
http://wiki.postgresql.org/wiki/Todo -> SQL Commands -> TO DO

MS SQL 2005 partial support this feature:
http://www.kodyaz.com/articles/sql-server-2005-cube-rollup-cannot-compute-distinct-aggregates.aspx
http://blogs.msdn.com/craigfr/archive/2007/09/21/aggregation-with-rollup.aspx

MS SQL 2008 support this feature:
http://blogs.msdn.com/craigfr/archive/2007/10/11/grouping-sets-in-sql-server-2008.aspx

Oracle support this feature:
http://www.compshack.com/sql/oracle-group-rollup

So, it's short notes about GROUPING SETS, but more complete
information have in a official documentation of MS SQL and Oracle
(copyright limited for send as attach).

First. GROUPG SETS.

select A,B,C,SUM(D) from table group by GROUPING SETS( (A,B,C), (A),
() ) - it's example of use grouping sets.
Semantic of this construction - make group by over source more, than
one group of column.
It's very wide key - A,B C. In result set of this example we can find
result set of select select A,B,C,SUM(D) from table group by A,B,C -
as subset. It's mind: "GROUP BY A,B,C" - subset of "GROUP BY GROUPING
SETS( (A,B,C), (A), () )
Two subset - is GROUP BY A B, and instead C column we look NULL.
Third subset - GROUP BY (), instead A,B,C - NULL, one row - it's name
"GRAND TOTAL". - calculate over all subset without grouping

Also have function "GROUPING" it's function say about null - "real
null" (from table) or generated by "GROUP BY GROUPING SETS"

My point: this feature can implement over GROUP BY and UNION ALL
We can make decomposition of "GROUP BY GROUPING SETS( (A,B,C),(A),()
)" to select A,B,C fron table GROUP BY A,B,C .UNION ALL select
A,B,NULL from table group BY A,B UNION ALL NUll,NUll,NULL from table
group by();

So, it's very simple, don't require modification of executor and
callibrate cost - only parser and semantic anylysis,
'
So, ROLLUP(A1,...An) is alias to "GROUP BY GROUPING SETS(
(A1,...,An),(A1,...,An-1),... (An-1,An),(An),() ),
CUBE - analogue.

If this idea it's good - i can write code base on old patch
http://archives.postgresql.org/pgsql-hackers/2008-10/msg00838.php or
from clean list (as you wish).

In future i know how to implement ROLLUP more optimal (executor
iterator) and use this ROLLUP for optimisation another GROUP BY,
GROUPING SETS.

Thanks.

Hello

some other info is on http://wiki.postgresql.org/wiki/Grouping_Sets

Maybe in e few weak I'll have some a prototype based on CTE. My older
technique based on hashed tables should be well, but it carry lot of
unshared code. Using CTE means so we can optimize CTE and GROUPING
SETS together. I plan to transform query

SELECT * FROM some GROUP BY GROUPING SETS(a, b)

to

WITH q AS (SELECT * FROM some)
SELECT * FROM q GROUP BY a
UNION ALL
SELECT * FROM q GROUP BY b

2009/5/10 Олег Царев <zabivator(at)gmail(dot)com>:
> Hello all.
> Please, approve my ideas for implementation.
>
> Standart has feature T431: Extended grouping capabilities.
> This feature i found in TODO-list:
> http://wiki.postgresql.org/wiki/Todo -> SQL Commands -> TO DO
>
> MS SQL 2005 partial support this feature:
> http://www.kodyaz.com/articles/sql-server-2005-cube-rollup-cannot-compute-distinct-aggregates.aspx
> http://blogs.msdn.com/craigfr/archive/2007/09/21/aggregation-with-rollup.aspx
>
> MS SQL 2008 support this feature:
> http://blogs.msdn.com/craigfr/archive/2007/10/11/grouping-sets-in-sql-server-2008.aspx
>
> Oracle support this feature:
> http://www.compshack.com/sql/oracle-group-rollup
>
> So, it's short notes about GROUPING SETS, but more complete
> information have in a official documentation of MS SQL and Oracle
> (copyright limited for send as attach).
>
> First. GROUPG SETS.
>
> select A,B,C,SUM(D) from table group by GROUPING SETS( (A,B,C), (A),
> () ) - it's example of use grouping sets.
> Semantic of this construction - make group by over source more, than
> one group of column.
> It's very wide key - A,B C. In result set of this example we can find
> result set of select   select A,B,C,SUM(D) from table group by A,B,C -
> as subset. It's mind: "GROUP BY A,B,C" - subset of "GROUP BY GROUPING
> SETS( (A,B,C), (A), () )
> Two subset - is GROUP BY A B, and instead C column we look NULL.
> Third subset - GROUP BY (), instead A,B,C - NULL, one row - it's name
> "GRAND TOTAL". - calculate over all subset without grouping
>
> Also have function "GROUPING"  it's function say about null - "real
> null" (from table) or generated by "GROUP BY GROUPING SETS"
>
> My point: this feature can implement over GROUP BY and UNION ALL
> We can make decomposition of "GROUP BY GROUPING SETS( (A,B,C),(A),()
> )" to  select A,B,C fron table GROUP BY A,B,C .UNION  ALL select
> A,B,NULL from table group BY A,B UNION ALL NUll,NUll,NULL from table
> group by();
>
> So, it's very simple, don't require modification of executor and
> callibrate cost - only parser and semantic anylysis,
> '
> So, ROLLUP(A1,...An) is alias to "GROUP BY GROUPING SETS(
> (A1,...,An),(A1,...,An-1),... (An-1,An),(An),() ),
> CUBE - analogue.
>
> If this idea it's good -  i can write code base on old patch
> http://archives.postgresql.org/pgsql-hackers/2008-10/msg00838.php or
> from clean list (as you wish).

This is suboptimal. When SELECT * FROM X GROUP BY GROUPING SETS ...,
where X is some joined data, then you repeat JOIN on every grouping
set. So your solution is simple for implementation, but it should be
really slow.

Regards
Pavel Stehule

>
> In future i know how to implement ROLLUP more optimal (executor
> iterator) and use this ROLLUP for optimisation another GROUP BY,
> GROUPING SETS.
>
> Thanks.
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Hello, Pavel.

I read you letter, and found some points:

a) Simple transformation any GROUPING SETS in GROUP BY + UNION ALL
require clone source for every group.
It's so slow.
My point: we can make for start simple implementation.
b) Your sollution it's so good, IMHO
WITH q AS (SELECT * FROM some)
SELECT * FROM q GROUP BY a
UNION ALL
SELECT * FROM q GROUP BY b
But can you describe me how it's used on planner (calculate cost) and exector.
Sharing source - it's mind - we don't process tree, we process
DIRECTED GRAPH. Hard, many bugs, so on, not?
PostgreSQL support sharing nodes of executor? How? Where i can read?
Next. Ok, we spool source. After we use simple hash group/sort group +
union all? Or more advanced techniques? If different group by - it's
not different from my idea, it's logic additional (spool source for
fast rewind)

c) Don't forget also about node requiments - if we use hash table as
container for source - we reduce some sorting properties - and query
select A,B from TABLE group by ((A,B),(B)) order by a,b require
sorting on output of grouping sets.
It's mind - we need insert sort.

d) We can make SORT ROLLUP and SORT REDUCE ROLLUP.
first - sort group by use sorting for grouping data and calculate aggregations.
So, 'order by A,B,C' also 'order by A,B' also order by 'A' also order by ''
What it's mind? It's mind we can use sort features for implement SORT
ROLLUP. For every sub group we calculate self aggregates... oh, i bad
say. Look on "select a,sum(b) from table group by a". Sort group
grouping by a, and for every a-group calculate aggregations (sum), for
ROLLUP A,B we can make three aggregations ; for A,B than A than () -
and use one path on source calculate aggregations on every step, and
for split source on different subsets.
It's more perfomance, than different group by and union all.

Look for MS SQL:
http://www.kodyaz.com/articles/sql-server-2005-cube-rollup-cannot-compute-distinct-aggregates.aspx
Why MS SQL don't support distinct aggregations? I think - because
every distinct aggregations in MS SQL require hash, and many
aggregations - it's so slow.

Thank you!
2009/5/10 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> Hello
>
> some other info is on  http://wiki.postgresql.org/wiki/Grouping_Sets
>
> Maybe in e few weak I'll have some a prototype based on CTE. My older
> technique based on hashed tables should be well, but it carry lot of
> unshared code. Using CTE means so we can optimize CTE and GROUPING
> SETS together. I plan to transform query
>
> SELECT * FROM some GROUP BY GROUPING SETS(a, b)
>
> to
>
> WITH q AS (SELECT * FROM some)
>  SELECT * FROM q GROUP BY a
>  UNION ALL
>  SELECT * FROM q GROUP BY b
>
>
> 2009/5/10 Олег Царев <zabivator(at)gmail(dot)com>:
>> Hello all.
>> Please, approve my ideas for implementation.
>>
>> Standart has feature T431: Extended grouping capabilities.
>> This feature i found in TODO-list:
>> http://wiki.postgresql.org/wiki/Todo -> SQL Commands -> TO DO
>>
>> MS SQL 2005 partial support this feature:
>> http://www.kodyaz.com/articles/sql-server-2005-cube-rollup-cannot-compute-distinct-aggregates.aspx
>> http://blogs.msdn.com/craigfr/archive/2007/09/21/aggregation-with-rollup.aspx
>>
>> MS SQL 2008 support this feature:
>> http://blogs.msdn.com/craigfr/archive/2007/10/11/grouping-sets-in-sql-server-2008.aspx
>>
>> Oracle support this feature:
>> http://www.compshack.com/sql/oracle-group-rollup
>>
>> So, it's short notes about GROUPING SETS, but more complete
>> information have in a official documentation of MS SQL and Oracle
>> (copyright limited for send as attach).
>>
>> First. GROUPG SETS.
>>
>> select A,B,C,SUM(D) from table group by GROUPING SETS( (A,B,C), (A),
>> () ) - it's example of use grouping sets.
>> Semantic of this construction - make group by over source more, than
>> one group of column.
>> It's very wide key - A,B C. In result set of this example we can find
>> result set of select   select A,B,C,SUM(D) from table group by A,B,C -
>> as subset. It's mind: "GROUP BY A,B,C" - subset of "GROUP BY GROUPING
>> SETS( (A,B,C), (A), () )
>> Two subset - is GROUP BY A B, and instead C column we look NULL.
>> Third subset - GROUP BY (), instead A,B,C - NULL, one row - it's name
>> "GRAND TOTAL". - calculate over all subset without grouping
>>
>> Also have function "GROUPING"  it's function say about null - "real
>> null" (from table) or generated by "GROUP BY GROUPING SETS"
>>
>> My point: this feature can implement over GROUP BY and UNION ALL
>> We can make decomposition of "GROUP BY GROUPING SETS( (A,B,C),(A),()
>> )" to  select A,B,C fron table GROUP BY A,B,C .UNION  ALL select
>> A,B,NULL from table group BY A,B UNION ALL NUll,NUll,NULL from table
>> group by();
>>
>> So, it's very simple, don't require modification of executor and
>> callibrate cost - only parser and semantic anylysis,
>> '
>> So, ROLLUP(A1,...An) is alias to "GROUP BY GROUPING SETS(
>> (A1,...,An),(A1,...,An-1),... (An-1,An),(An),() ),
>> CUBE - analogue.
>>
>> If this idea it's good -  i can write code base on old patch
>> http://archives.postgresql.org/pgsql-hackers/2008-10/msg00838.php or
>> from clean list (as you wish).
>
> This is suboptimal. When SELECT * FROM X GROUP BY GROUPING SETS ...,
> where X is some joined data, then you repeat JOIN on every grouping
> set. So your solution is simple for implementation, but it should be
> really slow.
>
> Regards
> Pavel Stehule
>
>>
>> In future i know how to implement ROLLUP more optimal (executor
>> iterator) and use this ROLLUP for optimisation another GROUP BY,
>> GROUPING SETS.
>>
>> Thanks.
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>>
>

2009/5/10 Олег Царев <zabivator(at)gmail(dot)com>:
> Hello, Pavel.
>
> I read you letter, and found some points:
>
> a) Simple transformation any GROUPING SETS in GROUP BY + UNION ALL
> require clone source for every group.
> It's so slow.
> My point: we can make for start simple implementation.
> b) Your sollution it's so good, IMHO
> WITH q AS (SELECT * FROM some)
>  SELECT * FROM q GROUP BY a
>  UNION ALL
>  SELECT * FROM q GROUP BY b
> But can you describe me how it's used on planner (calculate cost) and exector.

look on CTE source code or ask to CTE author.

> Sharing source - it's mind - we don't process tree, we process
> DIRECTED GRAPH. Hard, many bugs, so on, not?

for example - code for DISTINCT is shared with code for aggregation.
My idea is based on similarity

GROUPING SETS is subset of CTE, nonrecursive CTE is subset of UNION ALL

> PostgreSQL support sharing nodes of executor? How? Where i can read?

I don't know, what do you thing exactly sharing nodes? Before CTE I
had to write special holder node, but it isn't necessary now.

> Next. Ok, we spool source. After we use simple hash group/sort group +
> union all? Or more advanced techniques?  If different group by - it's
> not different from my idea, it's logic additional (spool source for
> fast rewind)

I thing so trivial implementation via UNION ALL should work, but any
optimisations means lot of work - and when you add rewind
functionality, you will got trivial nonrecursive CTE implementation.
When I wrote patch, there wasn't possible an use CTE, so I played wit
own nodes. Now, the more clean solution is probably based on current
CTE base.

>
> c) Don't forget also about node requiments - if we use hash table as
> container for source -  we reduce some sorting properties - and query
> select A,B from TABLE group by ((A,B),(B)) order by a,b require
> sorting on output of grouping sets.
> It's mind - we need insert sort.

Yes, probably there should be possible some techniques - that should
to eliminate final sort op. I am not sure if it is really important.
Now I thinking what is the bigger devil a) patch complexity b) some
suboptimality (mainly redundant call of agg nodes). For me a.
Aggregates are not gratis, but significantly more expensive is
repeated seq data scan. So it is first goal. Later we should to thing
about next optimalizations.

>
> d) We can make SORT ROLLUP and SORT REDUCE ROLLUP.
> first - sort group by use sorting for grouping data and calculate aggregations.
> So, 'order by A,B,C' also 'order by A,B' also order by 'A' also order by ''
> What it's mind? It's mind we can use sort features for implement SORT
> ROLLUP. For every sub group we calculate self aggregates... oh, i bad
> say. Look on "select a,sum(b) from table group by a".  Sort group
> grouping by a, and for every a-group calculate aggregations (sum), for
> ROLLUP A,B we can make three aggregations ; for A,B than A than () -
> and use one path on source calculate aggregations on every step, and
> for split source on different subsets.
> It's more perfomance, than different group by and union all.

Maybe, I don't know. What I know:

* UNION ALL will have better result on indexed sets and MIN, MAX
aggregates. CTE isn't optimized now for these cases.
* UNION ALL will be slow for large sets (over cache),
* CTE needs some more optimalizations,
* pg core hackers dislike big and complex patches,
* pg core hackers like any improved current code base.

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.

Regards
Pavel Stehule
>
> Look for MS SQL:
> http://www.kodyaz.com/articles/sql-server-2005-cube-rollup-cannot-compute-distinct-aggregates.aspx
> Why MS SQL don't support distinct aggregations? I think - because
> every distinct aggregations in MS SQL require hash, and many
> aggregations - it's so slow.
>
> Thank you!
> 2009/5/10 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>> Hello
>>
>> some other info is on  http://wiki.postgresql.org/wiki/Grouping_Sets
>>
>> Maybe in e few weak I'll have some a prototype based on CTE. My older
>> technique based on hashed tables should be well, but it carry lot of
>> unshared code. Using CTE means so we can optimize CTE and GROUPING
>> SETS together. I plan to transform query
>>
>> SELECT * FROM some GROUP BY GROUPING SETS(a, b)
>>
>> to
>>
>> WITH q AS (SELECT * FROM some)
>>  SELECT * FROM q GROUP BY a
>>  UNION ALL
>>  SELECT * FROM q GROUP BY b
>>
>>
>> 2009/5/10 Олег Царев <zabivator(at)gmail(dot)com>:
>>> Hello all.
>>> Please, approve my ideas for implementation.
>>>
>>> Standart has feature T431: Extended grouping capabilities.
>>> This feature i found in TODO-list:
>>> http://wiki.postgresql.org/wiki/Todo -> SQL Commands -> TO DO
>>>
>>> MS SQL 2005 partial support this feature:
>>> http://www.kodyaz.com/articles/sql-server-2005-cube-rollup-cannot-compute-distinct-aggregates.aspx
>>> http://blogs.msdn.com/craigfr/archive/2007/09/21/aggregation-with-rollup.aspx
>>>
>>> MS SQL 2008 support this feature:
>>> http://blogs.msdn.com/craigfr/archive/2007/10/11/grouping-sets-in-sql-server-2008.aspx
>>>
>>> Oracle support this feature:
>>> http://www.compshack.com/sql/oracle-group-rollup
>>>
>>> So, it's short notes about GROUPING SETS, but more complete
>>> information have in a official documentation of MS SQL and Oracle
>>> (copyright limited for send as attach).
>>>
>>> First. GROUPG SETS.
>>>
>>> select A,B,C,SUM(D) from table group by GROUPING SETS( (A,B,C), (A),
>>> () ) - it's example of use grouping sets.
>>> Semantic of this construction - make group by over source more, than
>>> one group of column.
>>> It's very wide key - A,B C. In result set of this example we can find
>>> result set of select   select A,B,C,SUM(D) from table group by A,B,C -
>>> as subset. It's mind: "GROUP BY A,B,C" - subset of "GROUP BY GROUPING
>>> SETS( (A,B,C), (A), () )
>>> Two subset - is GROUP BY A B, and instead C column we look NULL.
>>> Third subset - GROUP BY (), instead A,B,C - NULL, one row - it's name
>>> "GRAND TOTAL". - calculate over all subset without grouping
>>>
>>> Also have function "GROUPING"  it's function say about null - "real
>>> null" (from table) or generated by "GROUP BY GROUPING SETS"
>>>
>>> My point: this feature can implement over GROUP BY and UNION ALL
>>> We can make decomposition of "GROUP BY GROUPING SETS( (A,B,C),(A),()
>>> )" to  select A,B,C fron table GROUP BY A,B,C .UNION  ALL select
>>> A,B,NULL from table group BY A,B UNION ALL NUll,NUll,NULL from table
>>> group by();
>>>
>>> So, it's very simple, don't require modification of executor and
>>> callibrate cost - only parser and semantic anylysis,
>>> '
>>> So, ROLLUP(A1,...An) is alias to "GROUP BY GROUPING SETS(
>>> (A1,...,An),(A1,...,An-1),... (An-1,An),(An),() ),
>>> CUBE - analogue.
>>>
>>> If this idea it's good -  i can write code base on old patch
>>> http://archives.postgresql.org/pgsql-hackers/2008-10/msg00838.php or
>>> from clean list (as you wish).
>>
>> This is suboptimal. When SELECT * FROM X GROUP BY GROUPING SETS ...,
>> where X is some joined data, then you repeat JOIN on every grouping
>> set. So your solution is simple for implementation, but it should be
>> really slow.
>>
>> Regards
>> Pavel Stehule
>>
>>>
>>> In future i know how to implement ROLLUP more optimal (executor
>>> iterator) and use this ROLLUP for optimisation another GROUP BY,
>>> GROUPING SETS.
>>>
>>> Thanks.
>>>
>>> --
>>> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-hackers
>>>
>>
>

I will write separated mail about rollup.
Can you send me some links or information about "CTE"? What is it?
Also, I need some deep knownledge about postgresql aggregation
calculation (executor part) - can you help me (links, books, name of
source files, etc)?.
After get additional information i will can continue discussion.
Thanls
2009/5/10 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> 2009/5/10 Олег Царев <zabivator(at)gmail(dot)com>:
>> Hello, Pavel.
>>
>> I read you letter, and found some points:
>>
>> a) Simple transformation any GROUPING SETS in GROUP BY + UNION ALL
>> require clone source for every group.
>> It's so slow.
>> My point: we can make for start simple implementation.
>> b) Your sollution it's so good, IMHO
>> WITH q AS (SELECT * FROM some)
>>  SELECT * FROM q GROUP BY a
>>  UNION ALL
>>  SELECT * FROM q GROUP BY b
>> But can you describe me how it's used on planner (calculate cost) and exector.
>
> look on CTE source code or ask to CTE author.
>
>> Sharing source - it's mind - we don't process tree, we process
>> DIRECTED GRAPH. Hard, many bugs, so on, not?
>
> for example - code for DISTINCT is shared with code for aggregation.
> My idea is based on similarity
>
> GROUPING SETS is subset of CTE, nonrecursive CTE is subset of UNION ALL
>
>> PostgreSQL support sharing nodes of executor? How? Where i can read?
>
> I don't know, what do you thing exactly sharing nodes? Before CTE I
> had to write special holder node, but it isn't necessary now.
>
>> Next. Ok, we spool source. After we use simple hash group/sort group +
>> union all? Or more advanced techniques?  If different group by - it's
>> not different from my idea, it's logic additional (spool source for
>> fast rewind)
>
> I thing so trivial implementation via UNION ALL should work, but any
> optimisations means lot of work - and when you add rewind
> functionality, you will got trivial nonrecursive CTE implementation.
> When I wrote patch, there wasn't possible an use CTE, so I played wit
> own nodes. Now, the more clean solution is probably based on current
> CTE base.
>
>>
>> c) Don't forget also about node requiments - if we use hash table as
>> container for source -  we reduce some sorting properties - and query
>> select A,B from TABLE group by ((A,B),(B)) order by a,b require
>> sorting on output of grouping sets.
>> It's mind - we need insert sort.
>
> Yes, probably there should be possible some techniques - that should
> to eliminate final sort op. I am not sure if it is really important.
> Now I thinking what is the bigger devil a) patch complexity b) some
> suboptimality  (mainly redundant call of agg nodes). For me a.
> Aggregates are not gratis, but significantly more expensive is
> repeated seq data scan. So it is first goal. Later we should to thing
> about next optimalizations.
>
>>
>> d) We can make SORT ROLLUP and SORT REDUCE ROLLUP.
>> first - sort group by use sorting for grouping data and calculate aggregations.
>> So, 'order by A,B,C' also 'order by A,B' also order by 'A' also order by ''
>> What it's mind? It's mind we can use sort features for implement SORT
>> ROLLUP. For every sub group we calculate self aggregates... oh, i bad
>> say. Look on "select a,sum(b) from table group by a".  Sort group
>> grouping by a, and for every a-group calculate aggregations (sum), for
>> ROLLUP A,B we can make three aggregations ; for A,B than A than () -
>> and use one path on source calculate aggregations on every step, and
>> for split source on different subsets.
>> It's more perfomance, than different group by and union all.
>
> Maybe, I don't know. What I know:
>
> * UNION ALL will have better result on indexed sets and MIN, MAX
> aggregates. CTE isn't optimized now for these cases.
> * UNION ALL will be slow for large sets (over cache),
> * CTE needs some more optimalizations,
> * pg core hackers dislike big and complex patches,
> * pg core hackers like any improved current code base.
>
> 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.
>
> Regards
> Pavel Stehule
>>
>> Look for MS SQL:
>> http://www.kodyaz.com/articles/sql-server-2005-cube-rollup-cannot-compute-distinct-aggregates.aspx
>> Why MS SQL don't support distinct aggregations? I think - because
>> every distinct aggregations in MS SQL require hash, and many
>> aggregations - it's so slow.
>>
>> Thank you!
>> 2009/5/10 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>>> Hello
>>>
>>> some other info is on  http://wiki.postgresql.org/wiki/Grouping_Sets
>>>
>>> Maybe in e few weak I'll have some a prototype based on CTE. My older
>>> technique based on hashed tables should be well, but it carry lot of
>>> unshared code. Using CTE means so we can optimize CTE and GROUPING
>>> SETS together. I plan to transform query
>>>
>>> SELECT * FROM some GROUP BY GROUPING SETS(a, b)
>>>
>>> to
>>>
>>> WITH q AS (SELECT * FROM some)
>>>  SELECT * FROM q GROUP BY a
>>>  UNION ALL
>>>  SELECT * FROM q GROUP BY b
>>>
>>>
>>> 2009/5/10 Олег Царев <zabivator(at)gmail(dot)com>:
>>>> Hello all.
>>>> Please, approve my ideas for implementation.
>>>>
>>>> Standart has feature T431: Extended grouping capabilities.
>>>> This feature i found in TODO-list:
>>>> http://wiki.postgresql.org/wiki/Todo -> SQL Commands -> TO DO
>>>>
>>>> MS SQL 2005 partial support this feature:
>>>> http://www.kodyaz.com/articles/sql-server-2005-cube-rollup-cannot-compute-distinct-aggregates.aspx
>>>> http://blogs.msdn.com/craigfr/archive/2007/09/21/aggregation-with-rollup.aspx
>>>>
>>>> MS SQL 2008 support this feature:
>>>> http://blogs.msdn.com/craigfr/archive/2007/10/11/grouping-sets-in-sql-server-2008.aspx
>>>>
>>>> Oracle support this feature:
>>>> http://www.compshack.com/sql/oracle-group-rollup
>>>>
>>>> So, it's short notes about GROUPING SETS, but more complete
>>>> information have in a official documentation of MS SQL and Oracle
>>>> (copyright limited for send as attach).
>>>>
>>>> First. GROUPG SETS.
>>>>
>>>> select A,B,C,SUM(D) from table group by GROUPING SETS( (A,B,C), (A),
>>>> () ) - it's example of use grouping sets.
>>>> Semantic of this construction - make group by over source more, than
>>>> one group of column.
>>>> It's very wide key - A,B C. In result set of this example we can find
>>>> result set of select   select A,B,C,SUM(D) from table group by A,B,C -
>>>> as subset. It's mind: "GROUP BY A,B,C" - subset of "GROUP BY GROUPING
>>>> SETS( (A,B,C), (A), () )
>>>> Two subset - is GROUP BY A B, and instead C column we look NULL.
>>>> Third subset - GROUP BY (), instead A,B,C - NULL, one row - it's name
>>>> "GRAND TOTAL". - calculate over all subset without grouping
>>>>
>>>> Also have function "GROUPING"  it's function say about null - "real
>>>> null" (from table) or generated by "GROUP BY GROUPING SETS"
>>>>
>>>> My point: this feature can implement over GROUP BY and UNION ALL
>>>> We can make decomposition of "GROUP BY GROUPING SETS( (A,B,C),(A),()
>>>> )" to  select A,B,C fron table GROUP BY A,B,C .UNION  ALL select
>>>> A,B,NULL from table group BY A,B UNION ALL NUll,NUll,NULL from table
>>>> group by();
>>>>
>>>> So, it's very simple, don't require modification of executor and
>>>> callibrate cost - only parser and semantic anylysis,
>>>> '
>>>> So, ROLLUP(A1,...An) is alias to "GROUP BY GROUPING SETS(
>>>> (A1,...,An),(A1,...,An-1),... (An-1,An),(An),() ),
>>>> CUBE - analogue.
>>>>
>>>> If this idea it's good -  i can write code base on old patch
>>>> http://archives.postgresql.org/pgsql-hackers/2008-10/msg00838.php or
>>>> from clean list (as you wish).
>>>
>>> This is suboptimal. When SELECT * FROM X GROUP BY GROUPING SETS ...,
>>> where X is some joined data, then you repeat JOIN on every grouping
>>> set. So your solution is simple for implementation, but it should be
>>> really slow.
>>>
>>> Regards
>>> Pavel Stehule
>>>
>>>>
>>>> In future i know how to implement ROLLUP more optimal (executor
>>>> iterator) and use this ROLLUP for optimisation another GROUP BY,
>>>> GROUPING SETS.
>>>>
>>>> Thanks.
>>>>
>>>> --
>>>> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
>>>> To make changes to your subscription:
>>>> http://www.postgresql.org/mailpref/pgsql-hackers
>>>>
>>>
>>
>

2009/5/11 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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

2009/5/12 Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>:
> 2009/5/11 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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.

it was my first solution. But I would to prepare one non hash method.
But now I thinking about some special executor node, that fill all
necessary hash parallel. It's special variant of hash agreggate.

regards
Pavel Stehule

>
>
> Regards,
>
> --
> Hitoshi Harada
>

On Tue, May 12, 2009 at 2:21 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> 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.
>
> it was my first solution. But I would to prepare one non hash method.
> But now I thinking about some special executor node, that fill all
> necessary hash parallel. It's special variant of hash agreggate.

I think HashAggregate will often be the fastest method of executing
this kind of operation, but it would be nice to have an alternative
(such as repeatedly sorting a tuplestore) to handle non-hashable
datatypes or cases where the HashAggregate would eat too much memory.

But that leads me to a question - does the existing HashAggregate code
make any attempt to obey work_mem? I know that the infrastructure is
present for HashJoin/Hash, but on a quick pass I didn't notice
anything similar in HashAggregate.

And on a slightly off-topic note for this thread, is there any
compelling reason why we have at least three different hash
implementations in the executor? HashJoin/Hash uses one for regular
batches and one for the skew batch, and I believe that HashAggregate
does something else entirely. It seems like it might improve code
maintainability, if nothing else, to unify these to the extent
possible.

...Robert

Hello Oleg

I am sending a new CTE based variant of my GROUPING SETS patch,

this patch has some bugs but it is good prototype (it's more stable
than old patch):

postgres=# select selling_date, baguette, sum(items) from
baguette_selling group by grouping sets(1,2);
selling_date | baguette | sum
--------------+----------+-----
2007-10-30 | | 17
2007-10-31 | | 12
| golf | 9
| buster | 20
(4 rows)

postgres=# select selling_date, baguette, sum(items),
grouping(selling_date), grouping(baguette), grouping_id(selling_date,
baguette) from baguette_selling group by grouping sets(1,2);
selling_date | baguette | sum | grouping | grouping | grouping_id
--------------+----------+-----+----------+----------+-------------
2007-10-30 | | 17 | 1 | 0 | 2
2007-10-31 | | 12 | 1 | 0 | 2
| golf | 9 | 0 | 1 | 1
| buster | 20 | 0 | 1 | 1
(4 rows)

postgres=# select selling_date, baguette, sum(items),
grouping(selling_date), grouping(baguette), grouping_id(selling_date,
baguette) from baguette_selling group by grouping sets(1,2,());
selling_date | baguette | sum | grouping | grouping | grouping_id
--------------+----------+-----+----------+----------+-------------
2007-10-30 | | 17 | 1 | 0 | 2
2007-10-31 | | 12 | 1 | 0 | 2
| golf | 9 | 0 | 1 | 1
| buster | 20 | 0 | 1 | 1
| | 29 | 0 | 0 | 0
(5 rows)

I thing so parser part is well and correct (and ported to 8.4).

CTE works well, but not 100% effective, and will be better to use
direct tuplestore interface (as second technique - when hash tables
can't to be used).

I am thinking, so the best solution is enhancing current Aggregate
node for support of GroupingSets. The code base on UNION ALL is +/-
equal to CTE, and I don't thing, so this should be optimal. But work
freely, please. I have not free time for this patch next two months.
So if you have time, it's your.

regards
Pavel Stehule

2009/5/10 Олег Царев <zabivator(at)gmail(dot)com>:
> Hello all.
> Please, approve my ideas for implementation.
>
> Standart has feature T431: Extended grouping capabilities.
> This feature i found in TODO-list:
> http://wiki.postgresql.org/wiki/Todo -> SQL Commands -> TO DO
>
> MS SQL 2005 partial support this feature:
> http://www.kodyaz.com/articles/sql-server-2005-cube-rollup-cannot-compute-distinct-aggregates.aspx
> http://blogs.msdn.com/craigfr/archive/2007/09/21/aggregation-with-rollup.aspx
>
> MS SQL 2008 support this feature:
> http://blogs.msdn.com/craigfr/archive/2007/10/11/grouping-sets-in-sql-server-2008.aspx
>
> Oracle support this feature:
> http://www.compshack.com/sql/oracle-group-rollup
>
> So, it's short notes about GROUPING SETS, but more complete
> information have in a official documentation of MS SQL and Oracle
> (copyright limited for send as attach).
>
> First. GROUPG SETS.
>
> select A,B,C,SUM(D) from table group by GROUPING SETS( (A,B,C), (A),
> () ) - it's example of use grouping sets.
> Semantic of this construction - make group by over source more, than
> one group of column.
> It's very wide key - A,B C. In result set of this example we can find
> result set of select   select A,B,C,SUM(D) from table group by A,B,C -
> as subset. It's mind: "GROUP BY A,B,C" - subset of "GROUP BY GROUPING
> SETS( (A,B,C), (A), () )
> Two subset - is GROUP BY A B, and instead C column we look NULL.
> Third subset - GROUP BY (), instead A,B,C - NULL, one row - it's name
> "GRAND TOTAL". - calculate over all subset without grouping
>
> Also have function "GROUPING"  it's function say about null - "real
> null" (from table) or generated by "GROUP BY GROUPING SETS"
>
> My point: this feature can implement over GROUP BY and UNION ALL
> We can make decomposition of "GROUP BY GROUPING SETS( (A,B,C),(A),()
> )" to  select A,B,C fron table GROUP BY A,B,C .UNION  ALL select
> A,B,NULL from table group BY A,B UNION ALL NUll,NUll,NULL from table
> group by();
>
> So, it's very simple, don't require modification of executor and
> callibrate cost - only parser and semantic anylysis,
> '
> So, ROLLUP(A1,...An) is alias to "GROUP BY GROUPING SETS(
> (A1,...,An),(A1,...,An-1),... (An-1,An),(An),() ),
> CUBE - analogue.
>
> If this idea it's good -  i can write code base on old patch
> http://archives.postgresql.org/pgsql-hackers/2008-10/msg00838.php or
> from clean list (as you wish).
>
> In future i know how to implement ROLLUP more optimal (executor
> iterator) and use this ROLLUP for optimisation another GROUP BY,
> GROUPING SETS.
>
> Thanks.
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Hello all!
If no one objecte (all agree, in other say) i continue work on patch -
particulary, i want support second strategy (tuple store instead of
hash-table) for save order of source (more cheap solution in case with
grouping sets + order by), investigate and brainstorm another
optimisation, writing regression tests and technical documentation.
But I need some time for complete my investigation internals of
PostgreSQL, particulary CTE.

Thanks.

2009/5/13 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> Hello Oleg
>
> I am sending a new CTE based variant of my GROUPING SETS patch,
>
> this patch has some bugs but it is good prototype (it's more stable
> than old patch):
>
> postgres=# select selling_date, baguette, sum(items) from
> baguette_selling group by grouping sets(1,2);
>  selling_date | baguette | sum
> --------------+----------+-----
>  2007-10-30   |          |  17
>  2007-10-31   |          |  12
>              | golf     |   9
>              | buster   |  20
> (4 rows)
>
> postgres=# select selling_date, baguette, sum(items),
> grouping(selling_date), grouping(baguette), grouping_id(selling_date,
> baguette) from baguette_selling group by grouping sets(1,2);
>  selling_date | baguette | sum | grouping | grouping | grouping_id
> --------------+----------+-----+----------+----------+-------------
>  2007-10-30   |          |  17 |        1 |        0 |           2
>  2007-10-31   |          |  12 |        1 |        0 |           2
>              | golf     |   9 |        0 |        1 |           1
>              | buster   |  20 |        0 |        1 |           1
> (4 rows)
>
> postgres=# select selling_date, baguette, sum(items),
> grouping(selling_date), grouping(baguette), grouping_id(selling_date,
> baguette) from baguette_selling group by grouping sets(1,2,());
>  selling_date | baguette | sum | grouping | grouping | grouping_id
> --------------+----------+-----+----------+----------+-------------
>  2007-10-30   |          |  17 |        1 |        0 |           2
>  2007-10-31   |          |  12 |        1 |        0 |           2
>              | golf     |   9 |        0 |        1 |           1
>              | buster   |  20 |        0 |        1 |           1
>              |          |  29 |        0 |        0 |           0
> (5 rows)
>
> I thing so parser part is well and correct (and ported to 8.4).
>
> CTE works well, but not 100% effective, and will be better to use
> direct tuplestore interface (as second technique - when hash tables
> can't to be used).
>
> I am thinking, so the best solution is enhancing current Aggregate
> node for support of GroupingSets. The code base on UNION ALL is +/-
> equal to CTE, and I don't thing, so this should be optimal. But work
> freely, please. I have not free time for this patch next two months.
> So if you have time, it's your.
>
> regards
> Pavel Stehule
>
>
>
> 2009/5/10 Олег Царев <zabivator(at)gmail(dot)com>:
>> Hello all.
>> Please, approve my ideas for implementation.
>>
>> Standart has feature T431: Extended grouping capabilities.
>> This feature i found in TODO-list:
>> http://wiki.postgresql.org/wiki/Todo -> SQL Commands -> TO DO
>>
>> MS SQL 2005 partial support this feature:
>> http://www.kodyaz.com/articles/sql-server-2005-cube-rollup-cannot-compute-distinct-aggregates.aspx
>> http://blogs.msdn.com/craigfr/archive/2007/09/21/aggregation-with-rollup.aspx
>>
>> MS SQL 2008 support this feature:
>> http://blogs.msdn.com/craigfr/archive/2007/10/11/grouping-sets-in-sql-server-2008.aspx
>>
>> Oracle support this feature:
>> http://www.compshack.com/sql/oracle-group-rollup
>>
>> So, it's short notes about GROUPING SETS, but more complete
>> information have in a official documentation of MS SQL and Oracle
>> (copyright limited for send as attach).
>>
>> First. GROUPG SETS.
>>
>> select A,B,C,SUM(D) from table group by GROUPING SETS( (A,B,C), (A),
>> () ) - it's example of use grouping sets.
>> Semantic of this construction - make group by over source more, than
>> one group of column.
>> It's very wide key - A,B C. In result set of this example we can find
>> result set of select   select A,B,C,SUM(D) from table group by A,B,C -
>> as subset. It's mind: "GROUP BY A,B,C" - subset of "GROUP BY GROUPING
>> SETS( (A,B,C), (A), () )
>> Two subset - is GROUP BY A B, and instead C column we look NULL.
>> Third subset - GROUP BY (), instead A,B,C - NULL, one row - it's name
>> "GRAND TOTAL". - calculate over all subset without grouping
>>
>> Also have function "GROUPING"  it's function say about null - "real
>> null" (from table) or generated by "GROUP BY GROUPING SETS"
>>
>> My point: this feature can implement over GROUP BY and UNION ALL
>> We can make decomposition of "GROUP BY GROUPING SETS( (A,B,C),(A),()
>> )" to  select A,B,C fron table GROUP BY A,B,C .UNION  ALL select
>> A,B,NULL from table group BY A,B UNION ALL NUll,NUll,NULL from table
>> group by();
>>
>> So, it's very simple, don't require modification of executor and
>> callibrate cost - only parser and semantic anylysis,
>> '
>> So, ROLLUP(A1,...An) is alias to "GROUP BY GROUPING SETS(
>> (A1,...,An),(A1,...,An-1),... (An-1,An),(An),() ),
>> CUBE - analogue.
>>
>> If this idea it's good -  i can write code base on old patch
>> http://archives.postgresql.org/pgsql-hackers/2008-10/msg00838.php or
>> from clean list (as you wish).
>>
>> In future i know how to implement ROLLUP more optimal (executor
>> iterator) and use this ROLLUP for optimisation another GROUP BY,
>> GROUPING SETS.
>>
>> Thanks.
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>>
>

On Tue, May 12, 2009 at 11:20:14PM +0200, Pavel Stehule wrote:
> this patch has some bugs but it is good prototype (it's more stable
> than old patch):

I'm not sure if you're at the point that you're interested in bug reports, but
here's something that didn't behave as expected:

5432 josh(at)josh*# create table gsettest (prod_id integer, cust_id integer,
quantity integer);
CREATE TABLE
5432 josh(at)josh*# insert into gsettest select floor(random() * 10)::int,
floor(random() * 20)::int, floor(random() * 10)::int from generate_series(1,
100);
INSERT 0 100
5432 josh(at)josh*# select prod_id, cust_id, sum(quantity) from gsettest group by
cube (prod_id, cust_id) order by 1, 2;
prod_id | cust_id | sum
---------+---------+-----
5 | 7 | 4
8 | 16 | 3
9 | 19 | 8
4 | 13 | 3
8 | 8 | 15
5 | 2 | 4
7 | 6 | 7
6 | 6 | 3
</snip>

Note that the results aren't sorted. The following, though, works around it:

5432 josh(at)josh*# select * from (select prod_id, cust_id, sum(quantity) from
gsettest group by cube (prod_id, cust_id)) f order by 1, 2;
prod_id | cust_id | sum
---------+---------+-----
0 | 2 | 8
0 | 4 | 8
0 | 5 | 2
0 | 7 | 11
0 | 8 | 7
0 | 9 | 1
0 | 12 | 3
0 | 14 | 7
0 | 16 | 5
0 | 17 | 8
0 | 18 | 9
0 | 19 | 2
0 | | 71
</snip>

EXPLAIN output is as follows:
5432 josh(at)josh*# explain select prod_id, cust_id, sum(quantity) from gsettest
group by cube (prod_id, cust_id) order by 1, 2;
QUERY PLAN
---------------------------------------------------------------------------
Append (cost=193.54..347.71 rows=601 width=9)
CTE **g**
-> Sort (cost=135.34..140.19 rows=1940 width=12)
Sort Key: gsettest.prod_id, gsettest.cust_id
-> Seq Scan on gsettest (cost=0.00..29.40 rows=1940 width=12)
-> HashAggregate (cost=53.35..55.85 rows=200 width=12)
-> CTE Scan on "**g**" (cost=0.00..38.80 rows=1940 width=12)
-> HashAggregate (cost=48.50..51.00 rows=200 width=8)
-> CTE Scan on "**g**" (cost=0.00..38.80 rows=1940 width=8)
-> HashAggregate (cost=48.50..51.00 rows=200 width=8)
-> CTE Scan on "**g**" (cost=0.00..38.80 rows=1940 width=8)
-> Aggregate (cost=43.65..43.66 rows=1 width=4)
-> CTE Scan on "**g**" (cost=0.00..38.80 rows=1940 width=4)
(13 rows)

...and without the ORDER BY clause just to prove that it really is the reason
for the Sort step...

5432 josh(at)josh*# explain select prod_id, cust_id, sum(quantity) from gsettest
group by cube (prod_id, cust_id);
QUERY PLAN
------------------------------------------------------------------------
Append (cost=82.75..236.92 rows=601 width=9)
CTE **g**
-> Seq Scan on gsettest (cost=0.00..29.40 rows=1940 width=12)
-> HashAggregate (cost=53.35..55.85 rows=200 width=12)
-> CTE Scan on "**g**" (cost=0.00..38.80 rows=1940 width=12)
-> HashAggregate (cost=48.50..51.00 rows=200 width=8)
-> CTE Scan on "**g**" (cost=0.00..38.80 rows=1940 width=8)
-> HashAggregate (cost=48.50..51.00 rows=200 width=8)
-> CTE Scan on "**g**" (cost=0.00..38.80 rows=1940 width=8)
-> Aggregate (cost=43.65..43.66 rows=1 width=4)
-> CTE Scan on "**g**" (cost=0.00..38.80 rows=1940 width=4)
(11 rows)

I'm hoping I'll get a chance to poke at the patch some. This could be very
useful...

- Josh / eggyknap

2009/5/13 Joshua Tolley <eggyknap(at)gmail(dot)com>:
> On Tue, May 12, 2009 at 11:20:14PM +0200, Pavel Stehule wrote:
>> this patch has some bugs but it is good prototype (it's more stable
>> than old patch):
>
> I'm not sure if you're at the point that you're interested in bug reports, but
> here's something that didn't behave as expected:
>
> 5432 josh(at)josh*# create table gsettest (prod_id integer, cust_id integer,
> quantity integer);
> CREATE TABLE
> 5432 josh(at)josh*# insert into gsettest select floor(random() * 10)::int,
> floor(random() * 20)::int, floor(random() * 10)::int from generate_series(1,
> 100);
> INSERT 0 100
> 5432 josh(at)josh*# select prod_id, cust_id, sum(quantity) from gsettest group by
> cube (prod_id, cust_id) order by 1, 2;
>  prod_id | cust_id | sum
> ---------+---------+-----
>       5 |       7 |   4
>       8 |      16 |   3
>       9 |      19 |   8
>       4 |      13 |   3
>       8 |       8 |  15
>       5 |       2 |   4
>       7 |       6 |   7
>       6 |       6 |   3
> </snip>
>
> Note that the results aren't sorted. The following, though, works around it:

I thing, so result should not be sorted - it's same like normal group by.

regards
Pavel Stehule

>
> 5432 josh(at)josh*# select * from (select prod_id, cust_id, sum(quantity) from
> gsettest group by cube (prod_id, cust_id)) f order by 1, 2;
>  prod_id | cust_id | sum
> ---------+---------+-----
>       0 |       2 |   8
>       0 |       4 |   8
>       0 |       5 |   2
>       0 |       7 |  11
>       0 |       8 |   7
>       0 |       9 |   1
>       0 |      12 |   3
>       0 |      14 |   7
>       0 |      16 |   5
>       0 |      17 |   8
>       0 |      18 |   9
>       0 |      19 |   2
>       0 |         |  71
> </snip>
>
> EXPLAIN output is as follows:
> 5432 josh(at)josh*# explain select prod_id, cust_id, sum(quantity) from gsettest
> group by cube (prod_id, cust_id) order by 1, 2;
>                                QUERY PLAN
> ---------------------------------------------------------------------------
>  Append  (cost=193.54..347.71 rows=601 width=9)
>   CTE **g**
>     ->  Sort  (cost=135.34..140.19 rows=1940 width=12)
>           Sort Key: gsettest.prod_id, gsettest.cust_id
>           ->  Seq Scan on gsettest  (cost=0.00..29.40 rows=1940 width=12)
>   ->  HashAggregate  (cost=53.35..55.85 rows=200 width=12)
>         ->  CTE Scan on "**g**"  (cost=0.00..38.80 rows=1940 width=12)
>   ->  HashAggregate  (cost=48.50..51.00 rows=200 width=8)
>         ->  CTE Scan on "**g**"  (cost=0.00..38.80 rows=1940 width=8)
>   ->  HashAggregate  (cost=48.50..51.00 rows=200 width=8)
>         ->  CTE Scan on "**g**"  (cost=0.00..38.80 rows=1940 width=8)
>   ->  Aggregate  (cost=43.65..43.66 rows=1 width=4)
>         ->  CTE Scan on "**g**"  (cost=0.00..38.80 rows=1940 width=4)
> (13 rows)
>
> ...and without the ORDER BY clause just to prove that it really is the reason
> for the Sort step...
>
> 5432 josh(at)josh*# explain select prod_id, cust_id, sum(quantity) from gsettest
> group by cube (prod_id, cust_id);
>                               QUERY PLAN
> ------------------------------------------------------------------------
>  Append  (cost=82.75..236.92 rows=601 width=9)
>   CTE **g**
>     ->  Seq Scan on gsettest  (cost=0.00..29.40 rows=1940 width=12)
>   ->  HashAggregate  (cost=53.35..55.85 rows=200 width=12)
>         ->  CTE Scan on "**g**"  (cost=0.00..38.80 rows=1940 width=12)
>   ->  HashAggregate  (cost=48.50..51.00 rows=200 width=8)
>         ->  CTE Scan on "**g**"  (cost=0.00..38.80 rows=1940 width=8)
>   ->  HashAggregate  (cost=48.50..51.00 rows=200 width=8)
>         ->  CTE Scan on "**g**"  (cost=0.00..38.80 rows=1940 width=8)
>   ->  Aggregate  (cost=43.65..43.66 rows=1 width=4)
>         ->  CTE Scan on "**g**"  (cost=0.00..38.80 rows=1940 width=4)
> (11 rows)
>
> I'm hoping I'll get a chance to poke at the patch some. This could be very
> useful...
>
> - Josh / eggyknap
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkoKOdUACgkQRiRfCGf1UMOpFQCeJGQftMheSi6blMwheK4HI89p
> E7cAnjdWi4FaerR/+RTBeSv9Zc0RRXQ3
> =xW04
> -----END PGP SIGNATURE-----
>
>

On Wed, May 13, 2009 at 06:29:41AM +0200, Pavel Stehule wrote:
> 2009/5/13 Joshua Tolley <eggyknap(at)gmail(dot)com>:
> > On Tue, May 12, 2009 at 11:20:14PM +0200, Pavel Stehule wrote:
> >> this patch has some bugs but it is good prototype (it's more stable
> >> than old patch):
> >
> > I'm not sure if you're at the point that you're interested in bug reports, but
> > here's something that didn't behave as expected:
> >
> > 5432 josh(at)josh*# create table gsettest (prod_id integer, cust_id integer,
> > quantity integer);
> > CREATE TABLE
> > 5432 josh(at)josh*# insert into gsettest select floor(random() * 10)::int,
> > floor(random() * 20)::int, floor(random() * 10)::int from generate_series(1,
> > 100);
> > INSERT 0 100
> > 5432 josh(at)josh*# select prod_id, cust_id, sum(quantity) from gsettest group by
> > cube (prod_id, cust_id) order by 1, 2;
> >  prod_id | cust_id | sum
> > ---------+---------+-----
> >       5 |       7 |   4
> >       8 |      16 |   3
> >       9 |      19 |   8
> >       4 |      13 |   3
> >       8 |       8 |  15
> >       5 |       2 |   4
> >       7 |       6 |   7
> >       6 |       6 |   3
> > </snip>
> >
> > Note that the results aren't sorted. The following, though, works around it:
>
> I thing, so result should not be sorted - it's same like normal group by.

Normal GROUP BY wouldn't have ignored the ORDER BY clause I included.

- Josh

2009/5/13 Joshua Tolley <eggyknap(at)gmail(dot)com>:
> On Wed, May 13, 2009 at 06:29:41AM +0200, Pavel Stehule wrote:
>> 2009/5/13 Joshua Tolley <eggyknap(at)gmail(dot)com>:
>> > On Tue, May 12, 2009 at 11:20:14PM +0200, Pavel Stehule wrote:
>> >> this patch has some bugs but it is good prototype (it's more stable
>> >> than old patch):
>> >
>> > I'm not sure if you're at the point that you're interested in bug reports, but
>> > here's something that didn't behave as expected:
>> >
>> > 5432 josh(at)josh*# create table gsettest (prod_id integer, cust_id integer,
>> > quantity integer);
>> > CREATE TABLE
>> > 5432 josh(at)josh*# insert into gsettest select floor(random() * 10)::int,
>> > floor(random() * 20)::int, floor(random() * 10)::int from generate_series(1,
>> > 100);
>> > INSERT 0 100
>> > 5432 josh(at)josh*# select prod_id, cust_id, sum(quantity) from gsettest group by
>> > cube (prod_id, cust_id) order by 1, 2;
>> >  prod_id | cust_id | sum
>> > ---------+---------+-----
>> >       5 |       7 |   4
>> >       8 |      16 |   3
>> >       9 |      19 |   8
>> >       4 |      13 |   3
>> >       8 |       8 |  15
>> >       5 |       2 |   4
>> >       7 |       6 |   7
>> >       6 |       6 |   3
>> > </snip>
>> >
>> > Note that the results aren't sorted. The following, though, works around it:
>>
>> I thing, so result should not be sorted - it's same like normal group by.
>
> Normal GROUP BY wouldn't have ignored the ORDER BY clause I included.
>

sorry, now I understand - simply it is a bug. I fixed it

Thank You
Pavel

> - Josh
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkoKxLQACgkQRiRfCGf1UMOj/wCgkPnRiheRr+BNPLBCjzA9XlFW
> 0rsAoI0eOGSGlxIv0eNB8zqum7kw/Cqw
> =FCTz
> -----END PGP SIGNATURE-----
>
>

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> But that leads me to a question - does the existing HashAggregate code
> make any attempt to obey work_mem?

No.

regards, tom lane

On Wed, May 13, 2009 at 03:12:51PM +0200, Pavel Stehule wrote:
> 2009/5/13 Joshua Tolley <eggyknap(at)gmail(dot)com>:
> > On Wed, May 13, 2009 at 06:29:41AM +0200, Pavel Stehule wrote:
> >> 2009/5/13 Joshua Tolley <eggyknap(at)gmail(dot)com>:
> >> > On Tue, May 12, 2009 at 11:20:14PM +0200, Pavel Stehule wrote:
> >> >> this patch has some bugs but it is good prototype (it's more stable
> >> >> than old patch):
> >> >
> >> > I'm not sure if you're at the point that you're interested in bug reports, but
> >> > here's something that didn't behave as expected:
> >> >
> >> > 5432 josh(at)josh*# create table gsettest (prod_id integer, cust_id integer,
> >> > quantity integer);
> >> > CREATE TABLE
> >> > 5432 josh(at)josh*# insert into gsettest select floor(random() * 10)::int,
> >> > floor(random() * 20)::int, floor(random() * 10)::int from generate_series(1,
> >> > 100);
> >> > INSERT 0 100
> >> > 5432 josh(at)josh*# select prod_id, cust_id, sum(quantity) from gsettest group by
> >> > cube (prod_id, cust_id) order by 1, 2;
> >> >  prod_id | cust_id | sum
> >> > ---------+---------+-----
> >> >       5 |       7 |   4
> >> >       8 |      16 |   3
> >> >       9 |      19 |   8
> >> >       4 |      13 |   3
> >> >       8 |       8 |  15
> >> >       5 |       2 |   4
> >> >       7 |       6 |   7
> >> >       6 |       6 |   3
> >> > </snip>
> >> >
> >> > Note that the results aren't sorted. The following, though, works around it:
> >>
> >> I thing, so result should not be sorted - it's same like normal group by.
> >
> > Normal GROUP BY wouldn't have ignored the ORDER BY clause I included.
>
> sorry, now I understand - simply it is a bug. I fixed it

Where's the new patch?

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Here is.

I checked result with Oracle and basic results are same with one exception

this patch doesn't well do with expr specified sets

this result is correct

postgres=# select selling_date, baguette, canteen, sum(items),
grouping(baguette), grouping(selling_date),
grouping_id(baguette,selling_date) from baguette_selling group by
grouping sets(baguette, selling_date, canteen,());
selling_date | baguette | canteen | sum | grouping | grouping | grouping_id
--------------+----------+---------+-----+----------+----------+-------------
| golf | | 9 | 0 | 1 | 1
| buster | | 20 | 0 | 1 | 1
2007-10-30 | | | 17 | 1 | 0 | 2
2007-10-31 | | | 12 | 1 | 0 | 2
| | Prague | 14 | 1 | 1 | 3
| | Berlin | 15 | 1 | 1 | 3
| | | 29 | 1 | 1 | 3
(7 rows)

but this result not:

postgres=# select extract(day from selling_date), selling_date,
baguette, canteen, sum(items), grouping(baguette),
grouping(selling_date), grouping_id(baguette,selling_date) from
baguette_selling group by grouping sets(baguette, selling_date,
canteen, extract(day from selling_date))
;
date_part | selling_date | baguette | canteen | sum | grouping |
grouping | grouping_id
-----------+--------------+----------+---------+-----+----------+----------+-------------
| | golf | | 9 | 0 |
1 | 1
| | buster | | 20 | 0 |
1 | 1
30 | 2007-10-30 | | | 17 | 1 |
0 | 2
31 | 2007-10-31 | | | 12 | 1 |
0 | 2
| | | Prague | 14 | 1 |
1 | 3
| | | Berlin | 15 | 1 |
1 | 3
| | | | 29 | 1 |
1 | 3
(7 rows)

date_part column is problematic.

regards
Pavel Stehule

2009/5/13 David Fetter <david(at)fetter(dot)org>:
> On Wed, May 13, 2009 at 03:12:51PM +0200, Pavel Stehule wrote:
>> 2009/5/13 Joshua Tolley <eggyknap(at)gmail(dot)com>:
>> > On Wed, May 13, 2009 at 06:29:41AM +0200, Pavel Stehule wrote:
>> >> 2009/5/13 Joshua Tolley <eggyknap(at)gmail(dot)com>:
>> >> > On Tue, May 12, 2009 at 11:20:14PM +0200, Pavel Stehule wrote:
>> >> >> this patch has some bugs but it is good prototype (it's more stable
>> >> >> than old patch):
>> >> >
>> >> > I'm not sure if you're at the point that you're interested in bug reports, but
>> >> > here's something that didn't behave as expected:
>> >> >
>> >> > 5432 josh(at)josh*# create table gsettest (prod_id integer, cust_id integer,
>> >> > quantity integer);
>> >> > CREATE TABLE
>> >> > 5432 josh(at)josh*# insert into gsettest select floor(random() * 10)::int,
>> >> > floor(random() * 20)::int, floor(random() * 10)::int from generate_series(1,
>> >> > 100);
>> >> > INSERT 0 100
>> >> > 5432 josh(at)josh*# select prod_id, cust_id, sum(quantity) from gsettest group by
>> >> > cube (prod_id, cust_id) order by 1, 2;
>> >> >  prod_id | cust_id | sum
>> >> > ---------+---------+-----
>> >> >       5 |       7 |   4
>> >> >       8 |      16 |   3
>> >> >       9 |      19 |   8
>> >> >       4 |      13 |   3
>> >> >       8 |       8 |  15
>> >> >       5 |       2 |   4
>> >> >       7 |       6 |   7
>> >> >       6 |       6 |   3
>> >> > </snip>
>> >> >
>> >> > Note that the results aren't sorted. The following, though, works around it:
>> >>
>> >> I thing, so result should not be sorted - it's same like normal group by.
>> >
>> > Normal GROUP BY wouldn't have ignored the ORDER BY clause I included.
>>
>> sorry, now I understand - simply it is a bug. I fixed it
>
> Where's the new patch?
>
> Cheers,
> David.
> --
> David Fetter <david(at)fetter(dot)org> http://fetter.org/
> Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
> Skype: davidfetter      XMPP: david(dot)fetter(at)gmail(dot)com
>
> Remember to vote!
> Consider donating to Postgres: http://www.postgresql.org/about/donate
>

Hello,

here is last my grouping sets patch - next work will be done by Oleg.

This patch has full functionality of grouping sets - with support for
grouping via expr.

postgres=# select  extract(year from a),a,b, sum(c) from foo group by
grouping sets(extract(year from a), a, b,()); date_part |     a      |
b  | sum
-----------+------------+----+-----
     2001 |            |    |  40
          | 2001-10-12 |    |  20
          | 2001-10-11 |    |  20
          |            | 10 |  40
          |            |    |  40
(5 rows)

postgres=# select * from foo;
a | b | c
------------+----+----
2001-10-11 | 10 | 20
2001-10-12 | 10 | 20
(2 rows)

This patch is WIP - has full functionality, but is based on CTE - what
is mas/menos ugly hack. But for testing and first view about grouping
sets, it should do good work.

regards
Pavel Stehule

2009/5/10 Олег Царев <zabivator(at)gmail(dot)com>:
> I will write separated mail about rollup.
> Can you send me some links or information about "CTE"? What is it?
> Also, I need some deep knownledge about postgresql aggregation
> calculation (executor part) - can you help me (links, books, name of
> source files, etc)?.
> After get additional information i will can continue discussion.
> Thanls
> 2009/5/10 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>> 2009/5/10 Олег Царев <zabivator(at)gmail(dot)com>:
>>> Hello, Pavel.
>>>
>>> I read you letter, and found some points:
>>>
>>> a) Simple transformation any GROUPING SETS in GROUP BY + UNION ALL
>>> require clone source for every group.
>>> It's so slow.
>>> My point: we can make for start simple implementation.
>>> b) Your sollution it's so good, IMHO
>>> WITH q AS (SELECT * FROM some)
>>>  SELECT * FROM q GROUP BY a
>>>  UNION ALL
>>>  SELECT * FROM q GROUP BY b
>>> But can you describe me how it's used on planner (calculate cost) and exector.
>>
>> look on CTE source code or ask to CTE author.
>>
>>> Sharing source - it's mind - we don't process tree, we process
>>> DIRECTED GRAPH. Hard, many bugs, so on, not?
>>
>> for example - code for DISTINCT is shared with code for aggregation.
>> My idea is based on similarity
>>
>> GROUPING SETS is subset of CTE, nonrecursive CTE is subset of UNION ALL
>>
>>> PostgreSQL support sharing nodes of executor? How? Where i can read?
>>
>> I don't know, what do you thing exactly sharing nodes? Before CTE I
>> had to write special holder node, but it isn't necessary now.
>>
>>> Next. Ok, we spool source. After we use simple hash group/sort group +
>>> union all? Or more advanced techniques?  If different group by - it's
>>> not different from my idea, it's logic additional (spool source for
>>> fast rewind)
>>
>> I thing so trivial implementation via UNION ALL should work, but any
>> optimisations means lot of work - and when you add rewind
>> functionality, you will got trivial nonrecursive CTE implementation.
>> When I wrote patch, there wasn't possible an use CTE, so I played wit
>> own nodes. Now, the more clean solution is probably based on current
>> CTE base.
>>
>>>
>>> c) Don't forget also about node requiments - if we use hash table as
>>> container for source -  we reduce some sorting properties - and query
>>> select A,B from TABLE group by ((A,B),(B)) order by a,b require
>>> sorting on output of grouping sets.
>>> It's mind - we need insert sort.
>>
>> Yes, probably there should be possible some techniques - that should
>> to eliminate final sort op. I am not sure if it is really important.
>> Now I thinking what is the bigger devil a) patch complexity b) some
>> suboptimality  (mainly redundant call of agg nodes). For me a.
>> Aggregates are not gratis, but significantly more expensive is
>> repeated seq data scan. So it is first goal. Later we should to thing
>> about next optimalizations.
>>
>>>
>>> d) We can make SORT ROLLUP and SORT REDUCE ROLLUP.
>>> first - sort group by use sorting for grouping data and calculate aggregations.
>>> So, 'order by A,B,C' also 'order by A,B' also order by 'A' also order by ''
>>> What it's mind? It's mind we can use sort features for implement SORT
>>> ROLLUP. For every sub group we calculate self aggregates... oh, i bad
>>> say. Look on "select a,sum(b) from table group by a".  Sort group
>>> grouping by a, and for every a-group calculate aggregations (sum), for
>>> ROLLUP A,B we can make three aggregations ; for A,B than A than () -
>>> and use one path on source calculate aggregations on every step, and
>>> for split source on different subsets.
>>> It's more perfomance, than different group by and union all.
>>
>> Maybe, I don't know. What I know:
>>
>> * UNION ALL will have better result on indexed sets and MIN, MAX
>> aggregates. CTE isn't optimized now for these cases.
>> * UNION ALL will be slow for large sets (over cache),
>> * CTE needs some more optimalizations,
>> * pg core hackers dislike big and complex patches,
>> * pg core hackers like any improved current code base.
>>
>> 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.
>>
>> Regards
>> Pavel Stehule
>>>
>>> Look for MS SQL:
>>> http://www.kodyaz.com/articles/sql-server-2005-cube-rollup-cannot-compute-distinct-aggregates.aspx
>>> Why MS SQL don't support distinct aggregations? I think - because
>>> every distinct aggregations in MS SQL require hash, and many
>>> aggregations - it's so slow.
>>>
>>> Thank you!
>>> 2009/5/10 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>>>> Hello
>>>>
>>>> some other info is on  http://wiki.postgresql.org/wiki/Grouping_Sets
>>>>
>>>> Maybe in e few weak I'll have some a prototype based on CTE. My older
>>>> technique based on hashed tables should be well, but it carry lot of
>>>> unshared code. Using CTE means so we can optimize CTE and GROUPING
>>>> SETS together. I plan to transform query
>>>>
>>>> SELECT * FROM some GROUP BY GROUPING SETS(a, b)
>>>>
>>>> to
>>>>
>>>> WITH q AS (SELECT * FROM some)
>>>>  SELECT * FROM q GROUP BY a
>>>>  UNION ALL
>>>>  SELECT * FROM q GROUP BY b
>>>>
>>>>
>>>> 2009/5/10 Олег Царев <zabivator(at)gmail(dot)com>:
>>>>> Hello all.
>>>>> Please, approve my ideas for implementation.
>>>>>
>>>>> Standart has feature T431: Extended grouping capabilities.
>>>>> This feature i found in TODO-list:
>>>>> http://wiki.postgresql.org/wiki/Todo -> SQL Commands -> TO DO
>>>>>
>>>>> MS SQL 2005 partial support this feature:
>>>>> http://www.kodyaz.com/articles/sql-server-2005-cube-rollup-cannot-compute-distinct-aggregates.aspx
>>>>> http://blogs.msdn.com/craigfr/archive/2007/09/21/aggregation-with-rollup.aspx
>>>>>
>>>>> MS SQL 2008 support this feature:
>>>>> http://blogs.msdn.com/craigfr/archive/2007/10/11/grouping-sets-in-sql-server-2008.aspx
>>>>>
>>>>> Oracle support this feature:
>>>>> http://www.compshack.com/sql/oracle-group-rollup
>>>>>
>>>>> So, it's short notes about GROUPING SETS, but more complete
>>>>> information have in a official documentation of MS SQL and Oracle
>>>>> (copyright limited for send as attach).
>>>>>
>>>>> First. GROUPG SETS.
>>>>>
>>>>> select A,B,C,SUM(D) from table group by GROUPING SETS( (A,B,C), (A),
>>>>> () ) - it's example of use grouping sets.
>>>>> Semantic of this construction - make group by over source more, than
>>>>> one group of column.
>>>>> It's very wide key - A,B C. In result set of this example we can find
>>>>> result set of select   select A,B,C,SUM(D) from table group by A,B,C -
>>>>> as subset. It's mind: "GROUP BY A,B,C" - subset of "GROUP BY GROUPING
>>>>> SETS( (A,B,C), (A), () )
>>>>> Two subset - is GROUP BY A B, and instead C column we look NULL.
>>>>> Third subset - GROUP BY (), instead A,B,C - NULL, one row - it's name
>>>>> "GRAND TOTAL". - calculate over all subset without grouping
>>>>>
>>>>> Also have function "GROUPING"  it's function say about null - "real
>>>>> null" (from table) or generated by "GROUP BY GROUPING SETS"
>>>>>
>>>>> My point: this feature can implement over GROUP BY and UNION ALL
>>>>> We can make decomposition of "GROUP BY GROUPING SETS( (A,B,C),(A),()
>>>>> )" to  select A,B,C fron table GROUP BY A,B,C .UNION  ALL select
>>>>> A,B,NULL from table group BY A,B UNION ALL NUll,NUll,NULL from table
>>>>> group by();
>>>>>
>>>>> So, it's very simple, don't require modification of executor and
>>>>> callibrate cost - only parser and semantic anylysis,
>>>>> '
>>>>> So, ROLLUP(A1,...An) is alias to "GROUP BY GROUPING SETS(
>>>>> (A1,...,An),(A1,...,An-1),... (An-1,An),(An),() ),
>>>>> CUBE - analogue.
>>>>>
>>>>> If this idea it's good -  i can write code base on old patch
>>>>> http://archives.postgresql.org/pgsql-hackers/2008-10/msg00838.php or
>>>>> from clean list (as you wish).
>>>>
>>>> This is suboptimal. When SELECT * FROM X GROUP BY GROUPING SETS ...,
>>>> where X is some joined data, then you repeat JOIN on every grouping
>>>> set. So your solution is simple for implementation, but it should be
>>>> really slow.
>>>>
>>>> Regards
>>>> Pavel Stehule
>>>>
>>>>>
>>>>> In future i know how to implement ROLLUP more optimal (executor
>>>>> iterator) and use this ROLLUP for optimisation another GROUP BY,
>>>>> GROUPING SETS.
>>>>>
>>>>> Thanks.
>>>>>
>>>>> --
>>>>> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
>>>>> To make changes to your subscription:
>>>>> http://www.postgresql.org/mailpref/pgsql-hackers
>>>>>
>>>>
>>>
>>
>

Олег Царев escribió:
> Hello all!
> If no one objecte (all agree, in other say) i continue work on patch -
> particulary, i want support second strategy (tuple store instead of
> hash-table) for save order of source (more cheap solution in case with
> grouping sets + order by), investigate and brainstorm another
> optimisation, writing regression tests and technical documentation.
> But I need some time for complete my investigation internals of
> PostgreSQL, particulary CTE.

Where are we on this patch? Is it moving forward?

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

2009/8/8 Alvaro Herrera <alvherre(at)commandprompt(dot)com>:
> Олег Царев escribió:
>> Hello all!
>> If no one objecte (all agree, in other say) i continue work on patch -
>> particulary, i want support second strategy (tuple store instead of
>> hash-table) for save order of source (more cheap solution in case with
>> grouping sets + order by), investigate and brainstorm another
>> optimisation, writing regression tests and technical documentation.
>> But I need some time for complete my investigation internals of
>> PostgreSQL, particulary CTE.
>
> Where are we on this patch?  Is it moving forward?
>

It seems to me that the patch goes backward.

I looked trough the gsets-0.6.diff for about an hour, and found it is
now only a syntax sugar that builds multiple GROUP BY queries based on
CTE functionality. There's no executor modification.

If I remember correctly, the original patch touched executor parts.
I'd buy if the GROUPING SETS touches executor but I don't if this is
only syntax sugar, because you can write it as the same by yourself
without GROUPING SETS syntax. The motivation we push this forward is
performance that cannot be made by rewriting query, I guess.

Because GROUP BY we have today is a subset of GROUPING SETS by
definition, I suppose we'll refactor nodeAgg.c so that it is allowed
to take multiple group definitions. And we must support both of
HashAgg and GroupAgg. For HashAgg, it is easier in any case as the
earlier patch does. For GroupAgg, it is a bit complicated since we
sort by different key sets.

When we want GROUPING SET(a, b), at first we sort by a and aggregate
then sort by b and aggregate. This is the same as:

select a, null, count(*) from x group by a
union all
select null, b, count(*) from x group by b

so nothing better than query rewriting unless we invent something new.

But in case of sub total and grand total like ROLLUP query, GroupAgg
can do it by one-time scan by having multiple life cycle PerGroup
state.

Anyway, before going ahead we need to find rough sketch of how to
implement this feature. Only syntax sugar is acceptable? Or internal
executor support is necessary?

Regards,

--
Hitoshi Harada

2009/8/13 Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>:
> 2009/8/8 Alvaro Herrera <alvherre(at)commandprompt(dot)com>:
>> Олег Царев escribió:
>>> Hello all!
>>> If no one objecte (all agree, in other say) i continue work on patch -
>>> particulary, i want support second strategy (tuple store instead of
>>> hash-table) for save order of source (more cheap solution in case with
>>> grouping sets + order by), investigate and brainstorm another
>>> optimisation, writing regression tests and technical documentation.
>>> But I need some time for complete my investigation internals of
>>> PostgreSQL, particulary CTE.
>>
>> Where are we on this patch?  Is it moving forward?
>>
>
> It seems to me that the patch goes backward.
>
> I looked trough the gsets-0.6.diff for about an hour, and found it is
> now only a syntax sugar that builds multiple GROUP BY queries based on
> CTE functionality. There's no executor modification.
>
> If I remember correctly, the original patch touched executor parts.
> I'd buy if the GROUPING SETS touches executor but I don't if this is
> only syntax sugar, because you can write it as the same by yourself
> without GROUPING SETS syntax. The motivation we push this forward is
> performance that cannot be made by rewriting query, I guess.
>
> Because GROUP BY we have today is a subset of GROUPING SETS by
> definition, I suppose we'll refactor nodeAgg.c so that it is allowed
> to take multiple group definitions. And we must support both of
> HashAgg and GroupAgg. For HashAgg, it is easier in any case as the
> earlier patch does. For GroupAgg, it is a bit complicated since we
> sort by different key sets.
>
> When we want GROUPING SET(a, b), at first we sort by a and aggregate
> then sort by b and aggregate. This is the same as:
>
> select a, null, count(*) from x group by a
> union all
> select null, b, count(*) from x group by b
>
> so nothing better than query rewriting unless we invent something new.
>
> But in case of sub total and grand total like ROLLUP query, GroupAgg
> can do it by one-time scan by having multiple life cycle PerGroup
> state.
>
> Anyway, before going ahead we need to find rough sketch of how to
> implement this feature. Only syntax sugar is acceptable? Or internal
> executor support is necessary?
>
>
> Regards,
>
>
> --
> Hitoshi Harada
>

All rights, exclude
> Because GROUP BY we have today is a subset of GROUPING SETS by
> definition, I suppose we'll refactor nodeAgg.c so that it is allowed
> to take multiple group definitions. And we must support both of
> HashAgg and GroupAgg. For HashAgg, it is easier in any case as the
> earlier patch does. For GroupAgg, it is a bit complicated since we
> sort by different key sets.
because group by it's optimized version of grouping sets.
Of course, we can extend the current definition of group by, but we
regress perfomance of it.
Some questions for you:

How calcualte aggregation on ROLLUP on single pass?
Stupid way - store different buffer of aggregations for every group,
and accumulate every record on group for every calculator. When a
group has changed, return key of this group to output set with NULL
for fields not contains in this group, and restart current buffer of
aggregation.
Better way - add operation "merge aggregations", and calculate one
buffer on every group, when group has cnahged - merge this "main
buffer" to other, and return some intermediate result.

I think, support this of grouping operation isn't simple, and
different implementation of ROLLUP it's better.

Regards, Tsarev Oleg

2009/8/13 Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>:
> 2009/8/8 Alvaro Herrera <alvherre(at)commandprompt(dot)com>:
>> Олег Царев escribió:
>>> Hello all!
>>> If no one objecte (all agree, in other say) i continue work on patch -
>>> particulary, i want support second strategy (tuple store instead of
>>> hash-table) for save order of source (more cheap solution in case with
>>> grouping sets + order by), investigate and brainstorm another
>>> optimisation, writing regression tests and technical documentation.
>>> But I need some time for complete my investigation internals of
>>> PostgreSQL, particulary CTE.
>>
>> Where are we on this patch?  Is it moving forward?
>>
>
> It seems to me that the patch goes backward.

little bit.
>
> I looked trough the gsets-0.6.diff for about an hour, and found it is
> now only a syntax sugar that builds multiple GROUP BY queries based on
> CTE functionality. There's no executor modification.
>

I wrote older version in time when CTE wasn't implemented. The old
patch had own executor node, and was based on creating hash table per
group. This patch was maybe little bit faster than 0.6, but had lot of
bugs. Who knows planner code for grouping, then have to agree with me.
This code isn't readable and I wouldn't to it more complicated and
less readable. So I had idea, to join grouping sets with CTE. Grouping
sets is subset of CTE, so it is possible. Grouping sets is non
recursive CTE generally, and (I believe) this should be optimized
together.

I prefered using CTE, because this way was the most short to small
bugs less prototype - with full functionality.

> If I remember correctly, the original patch touched executor parts.
> I'd buy if the GROUPING SETS touches executor but I don't if this is
> only syntax sugar, because you can write it as the same by yourself
> without GROUPING SETS syntax. The motivation we push this forward is
> performance that cannot be made by rewriting query, I guess.
>

I don't thing, so you can do simply transformation from grouping sets
syntax to CTE. And what's more. Why you have optimized grouping sets
and not optimized non recursive CTE?

> Because GROUP BY we have today is a subset of GROUPING SETS by
> definition, I suppose we'll refactor nodeAgg.c so that it is allowed
> to take multiple group definitions. And we must support both of
> HashAgg and GroupAgg. For HashAgg, it is easier in any case as the
> earlier patch does. For GroupAgg, it is a bit complicated since we
> sort by different key sets.
>

This way is possible too. But needs absolutely grouping planner and
executor reworking. Maybe is time do it. It is work for somebody other
to me. My place is stored procedures.

> When we want GROUPING SET(a, b), at first we sort by a and aggregate
> then sort by b and aggregate. This is the same as:
>
> select a, null, count(*) from x group by a
> union all
> select null, b, count(*) from x group by b
>
> so nothing better than query rewriting unless we invent something new.
>

the problem is when x is subquery. Then is better using CTE, because
we don't need repeat x evaluation twice. The most typical use case is,
so x isn't table.

> But in case of sub total and grand total like ROLLUP query, GroupAgg
> can do it by one-time scan by having multiple life cycle PerGroup
> state.

>
> Anyway, before going ahead we need to find rough sketch of how to
> implement this feature. Only syntax sugar is acceptable? Or internal
> executor support is necessary?

I thing, so both ways are possible. Probably the most clean way is
total refactoring of grouping executor and grouping planner. I am not
sure if we need new nodes. There are all. But these nodes cannot work
paralel now.

>
>
> Regards,
>
>
> --
> Hitoshi Harada
>

2009/8/14 Олег Царев <zabivator(at)gmail(dot)com>:
> All rights, exclude
>> Because GROUP BY we have today is a subset of GROUPING SETS by
>> definition, I suppose we'll refactor nodeAgg.c so that it is allowed
>> to take multiple group definitions. And we must support both of
>> HashAgg and GroupAgg. For HashAgg, it is easier in any case as the
>> earlier patch does. For GroupAgg, it is a bit complicated since we
>> sort by different key sets.
> because group by it's optimized version of grouping sets.
> Of course, we can extend the current definition of group by, but we
> regress perfomance of it.
> Some questions for you:
>
> How calcualte aggregation on ROLLUP on single pass?

I'd imagine such like:

select a, b, count(*) from x group by rollup(a, b);

PerGroup all = init_agg(), a = init_agg(), ab = init_agg();
while(row = fetch()){
if(group_is_changed(ab, row)){
result_ab = finalize_agg(ab);
ab = init_agg();
}
if(group_is_changed(a, row)){
result_a = finalize_agg(a);
a = init_agg();
}
advance_agg(all, row);
advance_agg(a, row);
advance_agg(ab, row);
}
result_all = finalize_agg(all);

of course you should care best way to return result row and continue
aggregates and the number of grouping key varies from 1 to many, it is
quite possible. And normal GROUP BY is a case of key = a only, there
won't be performance regression.

> Better way - add operation "merge aggregations", and calculate one
> buffer on every group, when group has cnahged - merge this "main
> buffer" to other, and return some intermediate result.

"Merge aggregates" sounds fascinating to me in not only this feature
but also partitioned table aggregates. But adding another function
("merge function?") to the current aggregate system is quite far way.

>
> I think, support this of grouping operation isn't simple, and
> different implementation of ROLLUP it's better.

Surely not simple. Adding another node is one of the choices, but from
code maintenance point of view I feel it is better to integrate it
into nodeAgg. nodeWindowAgg and nodeAgg have similar aggregate
processes but don't share it so a bug fix in nodeAgg isn't completed
in itself but we must re-check nodeWindowAgg also. To add another
agg-like node *may* be kind of nightmare.

Regards,

--
Hitoshi Harada

2009/8/13 Олег Царев <zabivator(at)gmail(dot)com>:
> 2009/8/13 Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>:
>> 2009/8/8 Alvaro Herrera <alvherre(at)commandprompt(dot)com>:
>>> Олег Царев escribió:
>>>> Hello all!
>>>> If no one objecte (all agree, in other say) i continue work on patch -
>>>> particulary, i want support second strategy (tuple store instead of
>>>> hash-table) for save order of source (more cheap solution in case with
>>>> grouping sets + order by), investigate and brainstorm another
>>>> optimisation, writing regression tests and technical documentation.
>>>> But I need some time for complete my investigation internals of
>>>> PostgreSQL, particulary CTE.
>>>
>>> Where are we on this patch?  Is it moving forward?
>>>
>>
>> It seems to me that the patch goes backward.
>>
>> I looked trough the gsets-0.6.diff for about an hour, and found it is
>> now only a syntax sugar that builds multiple GROUP BY queries based on
>> CTE functionality. There's no executor modification.
>>
>> If I remember correctly, the original patch touched executor parts.
>> I'd buy if the GROUPING SETS touches executor but I don't if this is
>> only syntax sugar, because you can write it as the same by yourself
>> without GROUPING SETS syntax. The motivation we push this forward is
>> performance that cannot be made by rewriting query, I guess.
>>
>> Because GROUP BY we have today is a subset of GROUPING SETS by
>> definition, I suppose we'll refactor nodeAgg.c so that it is allowed
>> to take multiple group definitions. And we must support both of
>> HashAgg and GroupAgg. For HashAgg, it is easier in any case as the
>> earlier patch does. For GroupAgg, it is a bit complicated since we
>> sort by different key sets.
>>
>> When we want GROUPING SET(a, b), at first we sort by a and aggregate
>> then sort by b and aggregate. This is the same as:
>>
>> select a, null, count(*) from x group by a
>> union all
>> select null, b, count(*) from x group by b
>>
>> so nothing better than query rewriting unless we invent something new.
>>
>> But in case of sub total and grand total like ROLLUP query, GroupAgg
>> can do it by one-time scan by having multiple life cycle PerGroup
>> state.
>>
>> Anyway, before going ahead we need to find rough sketch of how to
>> implement this feature. Only syntax sugar is acceptable? Or internal
>> executor support is necessary?
>>
>>
>> Regards,
>>
>>
>> --
>> Hitoshi Harada
>>
>
> All rights, exclude
>> Because GROUP BY we have today is a subset of GROUPING SETS by
>> definition, I suppose we'll refactor nodeAgg.c so that it is allowed
>> to take multiple group definitions. And we must support both of
>> HashAgg and GroupAgg. For HashAgg, it is easier in any case as the
>> earlier patch does. For GroupAgg, it is a bit complicated since we
>> sort by different key sets.
> because group by it's optimized version of grouping sets.
> Of course, we can extend the current definition of group by, but we
> regress perfomance of it.
> Some questions for you:
>
> How calcualte aggregation on ROLLUP on single pass?
> Stupid way - store different buffer of aggregations for every group,
> and accumulate every record on group for every calculator. When a
> group has changed, return key of this group to output set with  NULL
> for fields not contains in this group, and restart current buffer of
> aggregation.
> Better way - add operation "merge aggregations", and calculate one
> buffer on every group, when group has cnahged - merge this "main
> buffer" to other, and return some intermediate result.
>

I don't thing, so this is possible for all operations. Don't forgot.
People can to implement own aggregates. example: weighted average

regards
Pavel Stehule

> I think, support this of grouping operation isn't simple, and
> different implementation of ROLLUP it's better.
>
> Regards, Tsarev Oleg
>

2009/8/14 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> I prefered using CTE, because this way was the most short to small
> bugs less prototype - with full functionality.

You could make it by query rewriting, but as you say the best cleanest
way is total refactoring of existing nodeAgg. How easy to implement is
not convincing.

>> When we want GROUPING SET(a, b), at first we sort by a and aggregate
>> then sort by b and aggregate. This is the same as:
>>
>> select a, null, count(*) from x group by a
>> union all
>> select null, b, count(*) from x group by b
>>
>> so nothing better than query rewriting unless we invent something new.
>>
> the problem is when x is subquery. Then is better using CTE, because
> we don't need repeat x evaluation twice. The most typical use case is,
> so x isn't table.

So we need single scan aggregate as far as possible. Buffering
subquery's result is possible without CTE node. Tuplestore has that
functionality but I found the buffered result will be sorted multiple
times, one way might be to allow tuplesort to perform sort multiple
times with different keys.

Regards,

--
Hitoshi Harada

2009/8/13 Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>:
> 2009/8/14 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>> I prefered using CTE, because this way was the most short to small
>> bugs less prototype - with full functionality.
>
> You could make it by query rewriting, but as you say the best cleanest
> way is total refactoring of existing nodeAgg. How easy to implement is
> not convincing.
>

I agree. Simply I am not have time and force do it. I would to
concentrate on finishing some plpgsql issues, and then I have to do
some other things than PostgreSQL. There are fully functional
prototype and everybody is welcome to continue in this work.

>>> When we want GROUPING SET(a, b), at first we sort by a and aggregate
>>> then sort by b and aggregate. This is the same as:
>>>
>>> select a, null, count(*) from x group by a
>>> union all
>>> select null, b, count(*) from x group by b
>>>
>>> so nothing better than query rewriting unless we invent something new.
>>>
>> the problem is when x is subquery. Then is better using CTE, because
>> we don't need repeat x evaluation twice. The most typical use case is,
>> so x isn't table.
>
> So we need single scan aggregate as far as possible. Buffering
> subquery's result is possible without CTE node. Tuplestore has that
> functionality but I found the buffered result will be sorted multiple
> times, one way might be to allow tuplesort to perform sort multiple
> times with different keys.
>

yes, I don't afraid multiple evaluation of aggregates. It's cheap.
Problem is multiple table scan. I though about some new version of
aggregates. Current aggregates process row by row with final
operation. Some new kind of aggregates should to work over tuple
store. Internally it get pointer to tupplestore and number of rows.
This should be very fast for functions like median, or array_agg. I
thing so it's similar to window functions - only it not window
function.

If you like to optimalize to speed, then the most faster solution will
be using hash tables - then you don't need tuplestore. For rollup is
possible maybe one single scan - but I am not sure - there are
important fakt - final function cannot modify intermediate data.

Pavel

>
>
> Regards,
>
>
> --
> Hitoshi Harada
>

2009/8/14 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> 2009/8/13 Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>:
>> 2009/8/14 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>>> I prefered using CTE, because this way was the most short to small
>>> bugs less prototype - with full functionality.
>>
>> You could make it by query rewriting, but as you say the best cleanest
>> way is total refactoring of existing nodeAgg. How easy to implement is
>> not convincing.
>>
>
> I agree. Simply I am not have time and force do it. I would to
> concentrate on finishing some plpgsql issues, and then I have to do
> some other things than PostgreSQL. There are fully functional
> prototype and everybody is welcome to continue in this work.
>

I see your situation. Actually your prototype is good shape to be
discussed in both ways. But since you've been focusing on this feature
it'd be better if you keep your eyes on this.

So, Oleg, do you continue on this?

Regards,

--
Hitoshi Harada

2009/8/14 Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>:
> 2009/8/14 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>> 2009/8/13 Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>:
>>> 2009/8/14 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>>>> I prefered using CTE, because this way was the most short to small
>>>> bugs less prototype - with full functionality.
>>>
>>> You could make it by query rewriting, but as you say the best cleanest
>>> way is total refactoring of existing nodeAgg. How easy to implement is
>>> not convincing.
>>>
>>
>> I agree. Simply I am not have time and force do it. I would to
>> concentrate on finishing some plpgsql issues, and then I have to do
>> some other things than PostgreSQL. There are fully functional
>> prototype and everybody is welcome to continue in this work.
>>
>
> I see your situation. Actually your prototype is good shape to be
> discussed in both ways. But since you've been focusing on this feature
> it'd be better if you keep your eyes on this.
>
> So, Oleg, do you continue on this?
>
>
> Regards,
>
>
> --
> Hitoshi Harada
>

> I'd imagine such like:
>
> select a, b, count(*) from x group by rollup(a, b);
>
> PerGroup all = init_agg(), a = init_agg(), ab = init_agg();
> while(row = fetch()){
> if(group_is_changed(ab, row)){
> result_ab = finalize_agg(ab);
> ab = init_agg();
> }
> if(group_is_changed(a, row)){
> result_a = finalize_agg(a);
> a = init_agg();
> }
> advance_agg(all, row);
> advance_agg(a, row);
> advance_agg(ab, row);
> }
> result_all = finalize_agg(all);
Fun =) My implementation of rollup in DBMS qd work as your imagine there! =)
Also, multiply sort of source we take for CUBE implementation, but
this hard for support (sort in group by - it's bloat).
As result we have merge implementation of group by, rollup, and window
functions with some common code - it's way for grouping of source,
Hash implementation group xxx on different hash-tables (with different
keys) it's very expensive (require many memory for keys).
I hope continue my work, after end of time trouble on work =( (bad
TPC-H perfomance)

2009/8/14 Олег Царев <zabivator(at)gmail(dot)com>:
> 2009/8/14 Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>:
>> 2009/8/14 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>>> 2009/8/13 Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>:
>>>> 2009/8/14 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>>>>> I prefered using CTE, because this way was the most short to small
>>>>> bugs less prototype - with full functionality.
>>>>
>>>> You could make it by query rewriting, but as you say the best cleanest
>>>> way is total refactoring of existing nodeAgg. How easy to implement is
>>>> not convincing.
>>>>
>>>
>>> I agree. Simply I am not have time and force do it. I would to
>>> concentrate on finishing some plpgsql issues, and then I have to do
>>> some other things than PostgreSQL. There are fully functional
>>> prototype and everybody is welcome to continue in this work.
>>>
>>
>> I see your situation. Actually your prototype is good shape to be
>> discussed in both ways. But since you've been focusing on this feature
>> it'd be better if you keep your eyes on this.
>>
>> So, Oleg, do you continue on this?
>>
>>
>> Regards,
>>
>>
>> --
>> Hitoshi Harada
>>
>
>> I'd imagine such like:
>>
>> select a, b, count(*) from x group by rollup(a, b);
>>
>> PerGroup all = init_agg(), a = init_agg(), ab = init_agg();
>> while(row = fetch()){
>>  if(group_is_changed(ab, row)){
>>    result_ab = finalize_agg(ab);
>>    ab = init_agg();
>>  }
>>  if(group_is_changed(a, row)){
>>    result_a = finalize_agg(a);
>>    a = init_agg();
>>  }
>>  advance_agg(all, row);
>>  advance_agg(a, row);
>>  advance_agg(ab, row);
>> }
>> result_all = finalize_agg(all);
> Fun =) My implementation of rollup in DBMS qd work as your imagine there! =)
> Also, multiply sort of source we take for CUBE implementation, but
> this hard for support (sort in group by - it's bloat).
> As result we have merge implementation of group by, rollup, and window
> functions with some common code - it's way for grouping of source,
> Hash implementation group xxx on different hash-tables (with different
> keys) it's very expensive (require many memory for keys).
> I hope continue my work, after end of time trouble on work =( (bad
> TPC-H perfomance)
>

I thing, so you are afraid too much about memory. Look on current
postgres. Any hash grouping is faster than sort grouping. Try and see.
PostgreSQL isn't embeded database. So there are not main goal an using
less memory. The goal is has features with clean, readable and
maintainable source code.

After week-lengthed investigation, now i 'm sure - my level of
qualification not enough for implementation task "GROUPING SETS".
I require documentation about the executor and the planner, i can't
understand scheme of work by source code.
Many code, many cases, but very little information "what is it" and
"how thos work". May be i stupid.
Sorry.

2009/8/14 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> 2009/8/14 Олег Царев <zabivator(at)gmail(dot)com>:
>> 2009/8/14 Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>:
>>> 2009/8/14 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>>>> 2009/8/13 Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>:
>>>>> 2009/8/14 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>>>>>> I prefered using CTE, because this way was the most short to small
>>>>>> bugs less prototype - with full functionality.
>>>>>
>>>>> You could make it by query rewriting, but as you say the best cleanest
>>>>> way is total refactoring of existing nodeAgg. How easy to implement is
>>>>> not convincing.
>>>>>
>>>>
>>>> I agree. Simply I am not have time and force do it. I would to
>>>> concentrate on finishing some plpgsql issues, and then I have to do
>>>> some other things than PostgreSQL. There are fully functional
>>>> prototype and everybody is welcome to continue in this work.
>>>>
>>>
>>> I see your situation. Actually your prototype is good shape to be
>>> discussed in both ways. But since you've been focusing on this feature
>>> it'd be better if you keep your eyes on this.
>>>
>>> So, Oleg, do you continue on this?
>>>
>>>
>>> Regards,
>>>
>>>
>>> --
>>> Hitoshi Harada
>>>
>>
>>> I'd imagine such like:
>>>
>>> select a, b, count(*) from x group by rollup(a, b);
>>>
>>> PerGroup all = init_agg(), a = init_agg(), ab = init_agg();
>>> while(row = fetch()){
>>>  if(group_is_changed(ab, row)){
>>>    result_ab = finalize_agg(ab);
>>>    ab = init_agg();
>>>  }
>>>  if(group_is_changed(a, row)){
>>>    result_a = finalize_agg(a);
>>>    a = init_agg();
>>>  }
>>>  advance_agg(all, row);
>>>  advance_agg(a, row);
>>>  advance_agg(ab, row);
>>> }
>>> result_all = finalize_agg(all);
>> Fun =) My implementation of rollup in DBMS qd work as your imagine there! =)
>> Also, multiply sort of source we take for CUBE implementation, but
>> this hard for support (sort in group by - it's bloat).
>> As result we have merge implementation of group by, rollup, and window
>> functions with some common code - it's way for grouping of source,
>> Hash implementation group xxx on different hash-tables (with different
>> keys) it's very expensive (require many memory for keys).
>> I hope continue my work, after end of time trouble on work =( (bad
>> TPC-H perfomance)
>>
>
> I thing, so you are afraid too much about memory. Look on current
> postgres. Any hash grouping is faster than sort grouping. Try and see.
> PostgreSQL isn't embeded database. So there are not main goal an using
> less memory. The goal is has features with clean, readable and
> maintainable source code.
>

On Thu, Sep 03, 2009 at 01:19:25AM +0400, Олег Царев wrote:
> After week-lengthed investigation, now i 'm sure - my level of
> qualification not enough for implementation task "GROUPING SETS".
> I require documentation about the executor and the planner, i can't
> understand scheme of work by source code.
> Many code, many cases, but very little information "what is it" and
> "how thos work". May be i stupid.

I doubt you're stupid; a stupid person wouldn't know what GROUPING SETS meant,
wouldn't bother finding out, and certainly wouldn't bother trying to implement
it. It's very helpful that you've let us know you're not working on it. That
way Pavel, if he finds he has time and interest, or someone else, can work on
it without fear of conflicting with what you're doing. Thanks for your work;
please don't get discouraged!

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

2009/9/2 Олег Царев <zabivator(at)gmail(dot)com>:
> After week-lengthed investigation, now i 'm sure - my level of
> qualification not enough for implementation task "GROUPING SETS".
> I require documentation about the executor and the planner, i can't
> understand scheme of work by source code.
> Many code, many cases, but very little information "what is it" and
> "how thos work". May be i stupid.

GROUPING SETS is too hard begin. Grouping planner isn't really
readable code. It is reason, whay I did GROUPING SETS over CTE and why
I would to share code with CTE.

regards
Pavel

> Sorry.
>
> 2009/8/14 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>> 2009/8/14 Олег Царев <zabivator(at)gmail(dot)com>:
>>> 2009/8/14 Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>:
>>>> 2009/8/14 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>>>>> 2009/8/13 Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com>:
>>>>>> 2009/8/14 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>>>>>>> I prefered using CTE, because this way was the most short to small
>>>>>>> bugs less prototype - with full functionality.
>>>>>>
>>>>>> You could make it by query rewriting, but as you say the best cleanest
>>>>>> way is total refactoring of existing nodeAgg. How easy to implement is
>>>>>> not convincing.
>>>>>>
>>>>>
>>>>> I agree. Simply I am not have time and force do it. I would to
>>>>> concentrate on finishing some plpgsql issues, and then I have to do
>>>>> some other things than PostgreSQL. There are fully functional
>>>>> prototype and everybody is welcome to continue in this work.
>>>>>
>>>>
>>>> I see your situation. Actually your prototype is good shape to be
>>>> discussed in both ways. But since you've been focusing on this feature
>>>> it'd be better if you keep your eyes on this.
>>>>
>>>> So, Oleg, do you continue on this?
>>>>
>>>>
>>>> Regards,
>>>>
>>>>
>>>> --
>>>> Hitoshi Harada
>>>>
>>>
>>>> I'd imagine such like:
>>>>
>>>> select a, b, count(*) from x group by rollup(a, b);
>>>>
>>>> PerGroup all = init_agg(), a = init_agg(), ab = init_agg();
>>>> while(row = fetch()){
>>>>  if(group_is_changed(ab, row)){
>>>>    result_ab = finalize_agg(ab);
>>>>    ab = init_agg();
>>>>  }
>>>>  if(group_is_changed(a, row)){
>>>>    result_a = finalize_agg(a);
>>>>    a = init_agg();
>>>>  }
>>>>  advance_agg(all, row);
>>>>  advance_agg(a, row);
>>>>  advance_agg(ab, row);
>>>> }
>>>> result_all = finalize_agg(all);
>>> Fun =) My implementation of rollup in DBMS qd work as your imagine there! =)
>>> Also, multiply sort of source we take for CUBE implementation, but
>>> this hard for support (sort in group by - it's bloat).
>>> As result we have merge implementation of group by, rollup, and window
>>> functions with some common code - it's way for grouping of source,
>>> Hash implementation group xxx on different hash-tables (with different
>>> keys) it's very expensive (require many memory for keys).
>>> I hope continue my work, after end of time trouble on work =( (bad
>>> TPC-H perfomance)
>>>
>>
>> I thing, so you are afraid too much about memory. Look on current
>> postgres. Any hash grouping is faster than sort grouping. Try and see.
>> PostgreSQL isn't embeded database. So there are not main goal an using
>> less memory. The goal is has features with clean, readable and
>> maintainable source code.
>>
>

2009/9/3 Joshua Tolley <eggyknap(at)gmail(dot)com>:
> On Thu, Sep 03, 2009 at 01:19:25AM +0400, Олег Царев wrote:
>> After week-lengthed investigation, now i 'm sure - my level of
>> qualification not enough for implementation task "GROUPING SETS".
>> I require documentation about the executor and the planner, i can't
>> understand scheme of work by source code.
>> Many code, many cases, but very little information "what is it" and
>> "how thos work". May be i stupid.
>
> I doubt you're stupid; a stupid person wouldn't know what GROUPING SETS meant,
> wouldn't bother finding out, and certainly wouldn't bother trying to implement
> it. It's very helpful that you've let us know you're not working on it. That
> way Pavel, if he finds he has time and interest, or someone else, can work on
> it without fear of conflicting with what you're doing. Thanks for your work;
> please don't get discouraged!

There some ways, how implement GROUPING SETS. Currently I don't would
to continue on this topic without some sponsoring. It got too my time
- and I am able to implement it only as some special variant of CTE.
Second way is total grouping planner refactoring - what is out of me.

regard
Pavel

>
> --
> Joshua Tolley / eggyknap
> End Point Corporation
> http://www.endpoint.com
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkqfM2QACgkQRiRfCGf1UMOHvgCgpzV9cvjhCWhzcmvRDbXjdBQ1
> 4RYAn2E+ZLRLdho+c+ZFleslPrbyxsZN
> =66vh
> -----END PGP SIGNATURE-----
>
>

2009/9/3 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
> 2009/9/3 Joshua Tolley <eggyknap(at)gmail(dot)com>:
>> On Thu, Sep 03, 2009 at 01:19:25AM +0400, Олег Царев wrote:
>>> After week-lengthed investigation, now i 'm sure - my level of
>>> qualification not enough for implementation task "GROUPING SETS".
>>> I require documentation about the executor and the planner, i can't
>>> understand scheme of work by source code.
>>> Many code, many cases, but very little information "what is it" and
>>> "how thos work". May be i stupid.
>>
>> I doubt you're stupid; a stupid person wouldn't know what GROUPING SETS meant,
>> wouldn't bother finding out, and certainly wouldn't bother trying to implement
>> it. It's very helpful that you've let us know you're not working on it. That
>> way Pavel, if he finds he has time and interest, or someone else, can work on
>> it without fear of conflicting with what you're doing. Thanks for your work;
>> please don't get discouraged!
>
> There some ways, how implement GROUPING SETS. Currently I don't would
> to continue on this topic without some sponsoring. It got too my time
> - and I am able to implement it only as some special variant of CTE.
> Second way is total grouping planner refactoring - what is out of me.

There supposed to be another way to implement, that is a "hybrid" way
of CTE approach and hash tables approach. As you noticed, in group
mode the planner may be untouchable which CTE helps to avoid and in
hash mode you've almost done it. I wouldn't like to agree to CTE based
approach totally, but the hybrid might be reasonable.

Regards,

>
> regard
> Pavel
>
>>
>> --
>> Joshua Tolley / eggyknap
>> End Point Corporation
>> http://www.endpoint.com
>>
>> -----BEGIN PGP SIGNATURE-----
>> Version: GnuPG v1.4.9 (GNU/Linux)
>>
>> iEYEARECAAYFAkqfM2QACgkQRiRfCGf1UMOHvgCgpzV9cvjhCWhzcmvRDbXjdBQ1
>> 4RYAn2E+ZLRLdho+c+ZFleslPrbyxsZN
>> =66vh
>> -----END PGP SIGNATURE-----
>>
>>
>

--
Hitoshi Harada