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

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