Horton

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

Richard Guo <guofenglinux@gmail.com>

View thread View on postgr.es
From: Richard Guo <guofenglinux@gmail.com>
To: David Rowley <dgrowleyml@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-22T09:33:24Z
Lists: pgsql-hackers 
On Wed, Jul 20, 2022 at 1:27 PM David Rowley <dgrowleyml@gmail.com> wrote:

> I've been working on this patch again. There was a bit of work to do
> to rebase it atop db0d67db2.  The problem there was that since this
> patch appends pathkeys to suit ORDER BY / DISTINCT aggregates to the
> query's group_pathkeys, db0d67db2 goes and tries to rearrange those,
> but fails to find the SortGroupClause corresponding to the PathKey in
> group_pathkeys. I wish the code I came up with to make that work was a
> bit nicer, but what's there at least seems to work. There are a few
> more making copies of Lists than I'd like.


We may need to do more checks when adding members to 'aggindexes' to
record we've found pathkeys for an aggregate, because 'currpathkeys' may
include pathkeys for some latter aggregates. I can see this problem with
the query below:

    select max(b order by b), max(a order by a) from t group by a;

When processing the first aggregate, we compose the 'currpathkeys' as
{a, b} and mark this aggregate in 'aggindexes'. When it comes to the
second aggregate, we compose its pathkeys as {a} and decide that it is
not stronger than 'currpathkeys'. So the second aggregate is not
recorded in 'aggindexes'. As a result, we fail to mark aggpresorted for
the second aggregate.

Thanks
Richard

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.