Re: group by of multi columns

Thank you, this works like a charm.

On 2015年1月4日周日 20:01 Andreas Kretschmer <akretschmer(at)spamfence(dot)net> wrote:

> Flyingfox Lee <flyingfoxlee(at)gmail(dot)com> wrote:
>
> > I am doing a `group by` on a table with ~ 3 million rows, the code is
> simply
> > `select A, B, C, D,E count(1) from t group by A, B, C, D, E order by
> 6`, it
> > takes ~ 3 minutes for this operation and there are ~ 500 rows returned.
> So, to
> > speed this up, should I add a composite index on A, B, C, D, E or there
> are
> > some parameters in postgresql.conf I can tweak, I am new to postgres,
> all the
> > parameters in postgresql.conf are the default.
>
> The only thing you can do is run the query with explain analyse and see
> how it work. You can tweak work_mem, a simple example:
>
> test=# create table b (a int, b int, c int, d int);
> CREATE TABLE
> Time: 0,735 ms
> test=*# insert into b select (random() * 1000)::int, (random()*1000)::int,
> (random() * 1000)::int, (random() * 1000)::int from
> generate_series(1,100000) s;
> INSERT 0 100000
> Time: 332,212 ms
> test=*# explain analyse select a,b,c,d, count(1) from b group by a,b,c,d;
> QUERY PLAN
> ------------------------------------------------------------
> ---------------------------------------------------
> HashAggregate (cost=2695.53..2791.29 rows=9576 width=16) (actual
> time=126.904..191.598 rows=100000 loops=1)
> Group Key: a, b, c, d
> -> Seq Scan on b (cost=0.00..1498.57 rows=95757 width=16) (actual
> time=0.012..33.520 rows=100000 loops=1)
> Planning time: 0.095 ms
> Execution time: 214.584 ms
> (5 rows)
>
> Time: 215,121 ms
> test=*# set work_mem to '64kB';
> SET
> Time: 0,109 ms
> test=*# explain analyse select a,b,c,d, count(1) from b group by a,b,c,d;
> QUERY PLAN
> ------------------------------------------------------------
> ---------------------------------------------------------
> GroupAggregate (cost=12697.07..14229.18 rows=9576 width=16) (actual
> time=206.603..388.892 rows=100000 loops=1)
> Group Key: a, b, c, d
> -> Sort (cost=12697.07..12936.46 rows=95757 width=16) (actual
> time=206.577..276.864 rows=100000 loops=1)
> Sort Key: a, b, c, d
> Sort Method: external merge Disk: 2552kB
> -> Seq Scan on b (cost=0.00..1498.57 rows=95757 width=16)
> (actual time=0.014..33.412 rows=100000 loops=1)
> Planning time: 0.071 ms
> Execution time: 413.876 ms
> (8 rows)
>
> Time: 414,246 ms
> test=*# set work_mem to '4MB';
> SET
> Time: 0,059 ms
> test=*# explain analyse select a,b,c,d, count(1) from b group by a,b,c,d;
> QUERY PLAN
> ------------------------------------------------------------
> ---------------------------------------------------
> HashAggregate (cost=2695.53..2791.29 rows=9576 width=16) (actual
> time=129.093..194.711 rows=100000 loops=1)
> Group Key: a, b, c, d
> -> Seq Scan on b (cost=0.00..1498.57 rows=95757 width=16) (actual
> time=0.014..33.762 rows=100000 loops=1)
> Planning time: 0.067 ms
> Execution time: 219.694 ms
> (5 rows)
>
>
> so, if you can see a 'Sort Method: external merge Disk', you should
> increase work_mem.
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect. (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly." (unknown)
> Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>