2009/5/13 Joshua Tolley <eggyk...@gmail.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 j...@josh*# create table gsettest (prod_id integer, cust_id integer, > quantity integer); > CREATE TABLE > 5432 j...@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 j...@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 j...@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 j...@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 j...@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----- > > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
