Re: Combining Aggregates

>
> # explain analyze select a%1000000,length(string_agg(b,',')) from ab group
> by 1;
>                                                         QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------
>  GroupAggregate  (cost=119510.84..144510.84 rows=1000000 width=32) (actual
> time=538.938..1015.278 rows=1000000 loops=1)
>    Group Key: ((a % 1000000))
>    ->  Sort  (cost=119510.84..122010.84 rows=1000000 width=32) (actual
> time=538.917..594.194 rows=1000000 loops=1)
>          Sort Key: ((a % 1000000))
>          Sort Method: quicksort  Memory: 102702kB
>          ->  Seq Scan on ab  (cost=0.00..19853.00 rows=1000000 width=32)
> (actual time=0.016..138.964 rows=1000000 loops=1)
>  Planning time: 0.146 ms
>  Execution time: 1047.511 ms
>
>
> Patched
> # explain analyze select a%1000000,length(string_agg(b,',')) from ab group
> by 1;
>                                                        QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------------
>  HashAggregate  (cost=24853.00..39853.00 rows=1000000 width=32) (actual
> time=8072.346..144424.872 rows=1000000 loops=1)
>    Group Key: (a % 1000000)
>    ->  Seq Scan on ab  (cost=0.00..19853.00 rows=1000000 width=32) (actual
> time=0.025..481.332 rows=1000000 loops=1)
>  Planning time: 0.164 ms
>  Execution time: 263288.332 ms

Well, that's pretty odd.  I guess the plan change must be a result of
switching the transition type from internal to text, although I'm not
immediately certain why that would make a difference.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers