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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
