Search Postgresql Archives

Re: Are multiple array_aggs going to be in the same order?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 04/10/2017 11:35 AM, Tom Lane wrote:
I'm not very keen on recommending that the OP insert an ORDER BY into
each aggregate call, because that would cause a separate sort for each
aggregate (unless someone's improved that recently while I wasn't
looking).

I mentioned this in my other email, upon a second look I was misreading the EXPLAIN output. (The sort was for the GROUP BY, not the individual ORDER BYs.) Do aggregate function sorts even show up in EXPLAIN? I can't seem to find any. For example in this try the sorts are just for grouping and joining:

db=> explain select c.id, array_agg(e.item order by e.id), array_agg(e.payee order by e.id) from expense_categories c join expenses e on e.expense_category_id = c.id group by c.id; QUERY PLAN
---------------------------------------------------------------------------------------
 GroupAggregate  (cost=223.44..285.14 rows=16 width=30)
   ->  Merge Join  (cost=223.44..264.44 rows=2728 width=30)
         Merge Cond: (c.id = e.expense_category_id)
         ->  Sort  (cost=1.48..1.52 rows=16 width=4)
               Sort Key: c.id
-> Seq Scan on expense_categories c (cost=0.00..1.16 rows=16 width=4)
         ->  Sort  (cost=221.96..228.78 rows=2728 width=30)
               Sort Key: e.expense_category_id
-> Seq Scan on expenses e (cost=0.00..66.28 rows=2728 width=30)
(9 rows)

Paul



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux