Re: Order by in a sub query when aggregating the main query

Understood, thanks for the explanation.
I'll work on updating the queries used by sqlalchemy to do array_agg(x
order by x) instead of the order by in the subquery.

> I think that right now that'd
> incur additional sorting overhead, which is annoying. But work is
> ongoing to recognize when the input is already correctly sorted
> for an aggregate, so it should get better in PG 16 or so.

Nice to know, hopefully it's too bad for this use case

Thanks, Federico Caselli

On Sun, 25 Sept 2022 at 00:20, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Federico <cfederico87(at)gmail(dot)com> writes:
> > A basic example of the type of query in question is the following (see
> > below for the actual query):
>
> > select w, array_agg(x)
> > from (
> > select v, v / 10 as w
> > from pg_catalog.generate_series(25, 0, -1) as t(v)
> > order by v
> > ) as t(x)
> > group by w
>
> > This query will return an ordered array as specified by the order by
> > clause.in the subquery.
> > Can this behaviour be relied upon?
>
> No, not really. It might always work given a particular set of
> circumstances. As long as the planner chooses to do the outer
> query's grouped aggregation as a HashAgg, there'd be no reason
> for it to reshuffle the subquery output before feeding that to
> array_agg. However, if it decided that sort-group-and-aggregate
> was better, it'd insert a sort by w above the subquery, and then
> you'd lose any certainty of the ordering by v continuing to hold.
> (Maybe the sort by w would be stable for equal keys, but that's
> not guaranteed.)
>
> What you really ought to do is write
>
> select w, array_agg(x order by x)
> from ...
>
> to be in the clear per SQL standard. I think that right now that'd
> incur additional sorting overhead, which is annoying. But work is
> ongoing to recognize when the input is already correctly sorted
> for an aggregate, so it should get better in PG 16 or so.
>
> regards, tom lane