Horton

Re: Add proper planner support for ORDER BY / DISTINCT aggregates

David Rowley <dgrowleyml@gmail.com>

View thread View on postgr.es
From: David Rowley <dgrowleyml@gmail.com>
To: Richard Guo <guofenglinux@gmail.com>
Cc: Ronan Dunklau <ronan.dunklau@aiven.io>, PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>, Ranier Vilela <ranier.vf@gmail.com>
Date: 2022-07-26T22:45:44Z
Lists: pgsql-hackers 
On Tue, 26 Jul 2022 at 19:39, Richard Guo <guofenglinux@gmail.com> wrote:
> Also I'm wondering if it's possible to take into consideration the
> ordering indicated by existing indexes when determining the pathkeys. So
> that for the query below we can avoid the Incremental Sort node if we
> consider that there is an index on t(a, c):
>
> # explain (costs off) select max(b order by b), max(c order by c) from t group by a;
>                  QUERY PLAN
> ---------------------------------------------
>  GroupAggregate
>    Group Key: a
>    ->  Incremental Sort
>          Sort Key: a, b
>          Presorted Key: a
>          ->  Index Scan using t_a_c_idx on t
> (6 rows)

That would be nice but I'm not going to add anything to this patch
which does anything like that. I think the patch, as it is, is a good
meaningful step forward to improve the performance of ordered
aggregates.

There are other things in the planner that could gain from what you
talk about. For example, choosing the evaluation order of WindowFuncs.
Perhaps it would be better to try to tackle those two problems
together rather than try to sneak something half-baked along with this
patch.

David

Commits

  1. Don't presort ORDER BY/DISTINCT Aggrefs with volatile functions

  2. Add enable_presorted_aggregate GUC

  3. Remove pessimistic cost penalization from Incremental Sort

  4. Fix hypothetical problem passing the wrong GROUP BY pathkeys

  5. Remove unused fields from ExprEvalStep

  6. Improve performance of ORDER BY / DISTINCT aggregates

  7. Refactor function parse_subscription_options.