On 10/8/24 08:19, Michael Paquier wrote:
On Tue, Oct 01, 2024 at 04:25:59PM +0700, Andrei Lepikhov wrote:
I have written a sketch patch to implement the idea with aggregate
prosupport in code - see the attachment.
My intention was to provide an example, not a ready-to-commit patch.
As I see, the only problem there lies in the tests - CREATE AGGREGATE
doesn't allow us to set a prosupport routine, and we need to update the
pg_proc table manually.
Please note that the CF bot is red.
Thanks, I suppose CATALOG_VERSION_NO was the only reason for this fail.
Also, I see that the union test fails:
left join lateral
(select t1.tenthous from tenk2 t2 union all (values(1)))
on true limit 1;
- QUERY PLAN
--------------------------------------------------------------------
- Limit
- -> Nested Loop Left Join
- -> Seq Scan on tenk1 t1
- -> Append
- -> Index Only Scan using tenk2_hundred on tenk2 t2
- -> Result
-(6 rows)
-
+ERROR: deadlock detected
+DETAIL: Process 414506 waits for AccessShareLock on relation 24696 of
database 16387; blocked by process 414511.
+Process 414511 waits for AccessExclusiveLock on relation 16421 of
database 16387; blocked by process 414506.
+HINT: See server log for query details.
But I wonder if new prosupport routine caused that. Please, let me know
if it is not a well-known issue.
--
regards, Andrei Lepikhov
From c3678a52d8cac9293a50ff5a4bab951155d45c5c Mon Sep 17 00:00:00 2001
From: "Andrei V. Lepikhov" <lepi...@gmail.com>
Date: Tue, 1 Oct 2024 16:08:11 +0700
Subject: [PATCH v1] Introduce prosupport helpers for aggregates.
For example, add minmax_support routine to simplify min/max aggregates.
---
src/backend/optimizer/plan/planagg.c | 76 ++++++++++++++
src/backend/optimizer/prep/prepagg.c | 26 +++++
src/include/catalog/catversion.h | 2 +-
src/include/catalog/pg_proc.dat | 93 ++++++++--------
src/include/nodes/supportnodes.h | 7 ++
src/test/regress/expected/aggregates.out | 128 ++++++++++++++++++++++-
src/test/regress/sql/aggregates.sql | 55 ++++++++++
src/tools/pgindent/typedefs.list | 1 +
8 files changed, 340 insertions(+), 48 deletions(-)
diff --git a/src/backend/optimizer/plan/planagg.c b/src/backend/optimizer/plan/planagg.c
index afb5445b77..598da61af8 100644
--- a/src/backend/optimizer/plan/planagg.c
+++ b/src/backend/optimizer/plan/planagg.c
@@ -33,6 +33,7 @@
#include "catalog/pg_type.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "nodes/supportnodes.h"
#include "optimizer/cost.h"
#include "optimizer/optimizer.h"
#include "optimizer/pathnode.h"
@@ -43,6 +44,7 @@
#include "parser/parse_clause.h"
#include "parser/parsetree.h"
#include "rewrite/rewriteManip.h"
+#include "utils/fmgrprotos.h"
#include "utils/lsyscache.h"
#include "utils/syscache.h"
@@ -510,3 +512,77 @@ fetch_agg_sort_op(Oid aggfnoid)
return aggsortop;
}
+
+Datum
+minmax_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+ Oid aggsortop;
+
+ if (IsA(rawreq, SupportRequestMinMax))
+ {
+ SupportRequestMinMax *req = (SupportRequestMinMax *) rawreq;
+ Aggref *aggref = req->aggref;
+
+ if (list_length(aggref->args) != 1 || aggref->aggfilter != NULL)
+ PG_RETURN_POINTER(NULL);
+
+ aggsortop = fetch_agg_sort_op(aggref->aggfnoid);
+ if (!OidIsValid(aggsortop))
+ PG_RETURN_POINTER(NULL); /* not a MIN/MAX aggregate */
+
+ if (aggref->aggorder != NIL)
+ {
+ SortGroupClause *orderClause;
+ TargetEntry *curTarget;
+
+ curTarget = (TargetEntry *) linitial(aggref->args);
+
+ /*
+ * If the order clause is the same column as the one we're
+ * aggregating, we can still use the index: It is undefined which
+ * value is MIN() or MAX(), as well as which value is first or
+ * last when sorted. So, we can still use the index IFF the
+ * aggregated expression equals the expression used in the
+ * ordering operation.
+ */
+
+ /*
+ * We only accept a single argument to min/max aggregates,
+ * orderings that have more clauses won't provide correct results.
+ */
+ Assert(list_length(aggref->aggorder) == 1);
+
+ orderClause = castNode(SortGroupClause, linitial(aggref->aggorder));
+
+ if (orderClause->tleSortGroupRef != curTarget->ressortgroupref)
+ elog(ERROR, "Aggregate order clause isn't found in target list");
+
+ if (orderClause->sortop != aggsortop)
+ {
+ List *btclasses;
+ ListCell *lc;
+
+ btclasses = get_op_btree_interpretation(orderClause->sortop);
+
+ foreach(lc, btclasses)
+ {
+ OpBtreeInterpretation *interpretation;
+
+ interpretation = (OpBtreeInterpretation *) lfirst(lc);
+ if (op_in_opfamily(aggsortop, interpretation->opfamily_id))
+ {
+ aggref->aggorder = NIL;
+ break;
+ }
+ }
+
+ list_free_deep(btclasses);
+ }
+ else
+ aggref->aggorder = NIL;
+ }
+ }
+
+ PG_RETURN_POINTER(NULL);
+}
diff --git a/src/backend/optimizer/prep/prepagg.c b/src/backend/optimizer/prep/prepagg.c
index 4606df379a..925d621e9d 100644
--- a/src/backend/optimizer/prep/prepagg.c
+++ b/src/backend/optimizer/prep/prepagg.c
@@ -39,6 +39,7 @@
#include "catalog/pg_type.h"
#include "nodes/nodeFuncs.h"
#include "nodes/pathnodes.h"
+#include "nodes/supportnodes.h"
#include "optimizer/cost.h"
#include "optimizer/optimizer.h"
#include "optimizer/plancat.h"
@@ -64,6 +65,25 @@ static int find_compatible_trans(PlannerInfo *root, Aggref *newagg,
List *transnos);
static Datum GetAggInitVal(Datum textInitVal, Oid transtype);
+static void
+optimize_aggregates(Aggref *aggref)
+{
+ SupportRequestMinMax req;
+ Oid prosupport;
+
+ prosupport = get_func_support(aggref->aggfnoid);
+
+ /* Check if there's a support function for the aggregate */
+ if (!OidIsValid(prosupport))
+ return;
+
+
+ req.type = T_SupportRequestMinMax;
+ req.aggref = aggref;
+ /* call the support function */
+ (void) OidFunctionCall1(prosupport, PointerGetDatum(&req));
+}
+
/* -----------------
* Resolve the transition type of all Aggrefs, and determine which Aggrefs
* can share aggregate or transition state.
@@ -215,6 +235,12 @@ preprocess_aggref(Aggref *aggref, PlannerInfo *root)
ReleaseSysCache(aggTuple);
+ /*
+ * See if any modifications can be made to each aggregate to allow
+ * planner to process it in more effective way.
+ */
+ optimize_aggregates(aggref);
+
/*
* 1. See if this is identical to another aggregate function call that
* we've seen already.
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 504bbe5327..4d4a2dd7af 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -57,6 +57,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 202410021
+#define CATALOG_VERSION_NO 202410081
#endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 77f54a79e6..df0387d4e2 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -6807,150 +6807,155 @@
proname => 'sum', prokind => 'a', proisstrict => 'f', prorettype => 'numeric',
proargtypes => 'numeric', prosrc => 'aggregate_dummy' },
+{ oid => '2775', descr => 'planner support for min and max aggregates',
+ proname => 'minmax_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'minmax_support' },
{ oid => '2115', descr => 'maximum value of all bigint input values',
- proname => 'max', prokind => 'a', proisstrict => 'f', prorettype => 'int8',
- proargtypes => 'int8', prosrc => 'aggregate_dummy' },
+ proname => 'max', prosupport => 'minmax_support', prokind => 'a',
+ proisstrict => 'f', prorettype => 'int8', proargtypes => 'int8',
+ prosrc => 'aggregate_dummy' },
{ oid => '2116', descr => 'maximum value of all integer input values',
- proname => 'max', prokind => 'a', proisstrict => 'f', prorettype => 'int4',
+ proname => 'max', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'int4',
proargtypes => 'int4', prosrc => 'aggregate_dummy' },
{ oid => '2117', descr => 'maximum value of all smallint input values',
- proname => 'max', prokind => 'a', proisstrict => 'f', prorettype => 'int2',
+ proname => 'max', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'int2',
proargtypes => 'int2', prosrc => 'aggregate_dummy' },
{ oid => '2118', descr => 'maximum value of all oid input values',
- proname => 'max', prokind => 'a', proisstrict => 'f', prorettype => 'oid',
+ proname => 'max', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'oid',
proargtypes => 'oid', prosrc => 'aggregate_dummy' },
{ oid => '2119', descr => 'maximum value of all float4 input values',
- proname => 'max', prokind => 'a', proisstrict => 'f', prorettype => 'float4',
+ proname => 'max', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'float4',
proargtypes => 'float4', prosrc => 'aggregate_dummy' },
{ oid => '2120', descr => 'maximum value of all float8 input values',
- proname => 'max', prokind => 'a', proisstrict => 'f', prorettype => 'float8',
+ proname => 'max', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'float8',
proargtypes => 'float8', prosrc => 'aggregate_dummy' },
{ oid => '2122', descr => 'maximum value of all date input values',
- proname => 'max', prokind => 'a', proisstrict => 'f', prorettype => 'date',
+ proname => 'max', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'date',
proargtypes => 'date', prosrc => 'aggregate_dummy' },
{ oid => '2123', descr => 'maximum value of all time input values',
- proname => 'max', prokind => 'a', proisstrict => 'f', prorettype => 'time',
+ proname => 'max', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'time',
proargtypes => 'time', prosrc => 'aggregate_dummy' },
{ oid => '2124',
descr => 'maximum value of all time with time zone input values',
- proname => 'max', prokind => 'a', proisstrict => 'f', prorettype => 'timetz',
+ proname => 'max', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'timetz',
proargtypes => 'timetz', prosrc => 'aggregate_dummy' },
{ oid => '2125', descr => 'maximum value of all money input values',
- proname => 'max', prokind => 'a', proisstrict => 'f', prorettype => 'money',
+ proname => 'max', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'money',
proargtypes => 'money', prosrc => 'aggregate_dummy' },
{ oid => '2126', descr => 'maximum value of all timestamp input values',
- proname => 'max', prokind => 'a', proisstrict => 'f',
+ proname => 'max', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f',
prorettype => 'timestamp', proargtypes => 'timestamp',
prosrc => 'aggregate_dummy' },
{ oid => '2127',
descr => 'maximum value of all timestamp with time zone input values',
- proname => 'max', prokind => 'a', proisstrict => 'f',
+ proname => 'max', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f',
prorettype => 'timestamptz', proargtypes => 'timestamptz',
prosrc => 'aggregate_dummy' },
{ oid => '2128', descr => 'maximum value of all interval input values',
- proname => 'max', prokind => 'a', proisstrict => 'f',
+ proname => 'max', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f',
prorettype => 'interval', proargtypes => 'interval',
prosrc => 'aggregate_dummy' },
{ oid => '2129', descr => 'maximum value of all text input values',
- proname => 'max', prokind => 'a', proisstrict => 'f', prorettype => 'text',
+ proname => 'max', prosupport => 'minmax_support', prokind => 'a',
+ proisstrict => 'f', prorettype => 'text',
proargtypes => 'text', prosrc => 'aggregate_dummy' },
{ oid => '2130', descr => 'maximum value of all numeric input values',
proname => 'max', prokind => 'a', proisstrict => 'f', prorettype => 'numeric',
proargtypes => 'numeric', prosrc => 'aggregate_dummy' },
{ oid => '2050', descr => 'maximum value of all anyarray input values',
- proname => 'max', prokind => 'a', proisstrict => 'f',
- prorettype => 'anyarray', proargtypes => 'anyarray',
+ proname => 'max', prosupport => 'minmax_support', prokind => 'a',
+ proisstrict => 'f', prorettype => 'anyarray', proargtypes => 'anyarray',
prosrc => 'aggregate_dummy' },
{ oid => '8595', descr => 'maximum value of all record input values',
- proname => 'max', prokind => 'a', proisstrict => 'f', prorettype => 'record',
+ proname => 'max', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'record',
proargtypes => 'record', prosrc => 'aggregate_dummy' },
{ oid => '2244', descr => 'maximum value of all bpchar input values',
- proname => 'max', prokind => 'a', proisstrict => 'f', prorettype => 'bpchar',
+ proname => 'max', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'bpchar',
proargtypes => 'bpchar', prosrc => 'aggregate_dummy' },
{ oid => '2797', descr => 'maximum value of all tid input values',
- proname => 'max', prokind => 'a', proisstrict => 'f', prorettype => 'tid',
+ proname => 'max', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'tid',
proargtypes => 'tid', prosrc => 'aggregate_dummy' },
{ oid => '3564', descr => 'maximum value of all inet input values',
- proname => 'max', prokind => 'a', proisstrict => 'f', prorettype => 'inet',
+ proname => 'max', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'inet',
proargtypes => 'inet', prosrc => 'aggregate_dummy' },
{ oid => '4189', descr => 'maximum value of all pg_lsn input values',
- proname => 'max', prokind => 'a', proisstrict => 'f', prorettype => 'pg_lsn',
+ proname => 'max', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'pg_lsn',
proargtypes => 'pg_lsn', prosrc => 'aggregate_dummy' },
{ oid => '5099', descr => 'maximum value of all xid8 input values',
- proname => 'max', prokind => 'a', proisstrict => 'f', prorettype => 'xid8',
+ proname => 'max', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'xid8',
proargtypes => 'xid8', prosrc => 'aggregate_dummy' },
{ oid => '2131', descr => 'minimum value of all bigint input values',
- proname => 'min', prokind => 'a', proisstrict => 'f', prorettype => 'int8',
+ proname => 'min', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'int8',
proargtypes => 'int8', prosrc => 'aggregate_dummy' },
{ oid => '2132', descr => 'minimum value of all integer input values',
- proname => 'min', prokind => 'a', proisstrict => 'f', prorettype => 'int4',
+ proname => 'min', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'int4',
proargtypes => 'int4', prosrc => 'aggregate_dummy' },
{ oid => '2133', descr => 'minimum value of all smallint input values',
- proname => 'min', prokind => 'a', proisstrict => 'f', prorettype => 'int2',
+ proname => 'min', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'int2',
proargtypes => 'int2', prosrc => 'aggregate_dummy' },
{ oid => '2134', descr => 'minimum value of all oid input values',
- proname => 'min', prokind => 'a', proisstrict => 'f', prorettype => 'oid',
+ proname => 'min', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'oid',
proargtypes => 'oid', prosrc => 'aggregate_dummy' },
{ oid => '2135', descr => 'minimum value of all float4 input values',
- proname => 'min', prokind => 'a', proisstrict => 'f', prorettype => 'float4',
+ proname => 'min', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'float4',
proargtypes => 'float4', prosrc => 'aggregate_dummy' },
{ oid => '2136', descr => 'minimum value of all float8 input values',
- proname => 'min', prokind => 'a', proisstrict => 'f', prorettype => 'float8',
+ proname => 'min', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'float8',
proargtypes => 'float8', prosrc => 'aggregate_dummy' },
{ oid => '2138', descr => 'minimum value of all date input values',
- proname => 'min', prokind => 'a', proisstrict => 'f', prorettype => 'date',
+ proname => 'min', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'date',
proargtypes => 'date', prosrc => 'aggregate_dummy' },
{ oid => '2139', descr => 'minimum value of all time input values',
- proname => 'min', prokind => 'a', proisstrict => 'f', prorettype => 'time',
+ proname => 'min', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'time',
proargtypes => 'time', prosrc => 'aggregate_dummy' },
{ oid => '2140',
descr => 'minimum value of all time with time zone input values',
- proname => 'min', prokind => 'a', proisstrict => 'f', prorettype => 'timetz',
+ proname => 'min', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'timetz',
proargtypes => 'timetz', prosrc => 'aggregate_dummy' },
{ oid => '2141', descr => 'minimum value of all money input values',
- proname => 'min', prokind => 'a', proisstrict => 'f', prorettype => 'money',
+ proname => 'min', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'money',
proargtypes => 'money', prosrc => 'aggregate_dummy' },
{ oid => '2142', descr => 'minimum value of all timestamp input values',
- proname => 'min', prokind => 'a', proisstrict => 'f',
+ proname => 'min', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f',
prorettype => 'timestamp', proargtypes => 'timestamp',
prosrc => 'aggregate_dummy' },
{ oid => '2143',
descr => 'minimum value of all timestamp with time zone input values',
- proname => 'min', prokind => 'a', proisstrict => 'f',
+ proname => 'min', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f',
prorettype => 'timestamptz', proargtypes => 'timestamptz',
prosrc => 'aggregate_dummy' },
{ oid => '2144', descr => 'minimum value of all interval input values',
- proname => 'min', prokind => 'a', proisstrict => 'f',
+ proname => 'min', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f',
prorettype => 'interval', proargtypes => 'interval',
prosrc => 'aggregate_dummy' },
{ oid => '2145', descr => 'minimum value of all text values',
- proname => 'min', prokind => 'a', proisstrict => 'f', prorettype => 'text',
+ proname => 'min', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'text',
proargtypes => 'text', prosrc => 'aggregate_dummy' },
{ oid => '2146', descr => 'minimum value of all numeric input values',
proname => 'min', prokind => 'a', proisstrict => 'f', prorettype => 'numeric',
proargtypes => 'numeric', prosrc => 'aggregate_dummy' },
{ oid => '2051', descr => 'minimum value of all anyarray input values',
- proname => 'min', prokind => 'a', proisstrict => 'f',
+ proname => 'min', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f',
prorettype => 'anyarray', proargtypes => 'anyarray',
prosrc => 'aggregate_dummy' },
{ oid => '8596', descr => 'minimum value of all record input values',
- proname => 'min', prokind => 'a', proisstrict => 'f', prorettype => 'record',
+ proname => 'min', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'record',
proargtypes => 'record', prosrc => 'aggregate_dummy' },
{ oid => '2245', descr => 'minimum value of all bpchar input values',
- proname => 'min', prokind => 'a', proisstrict => 'f', prorettype => 'bpchar',
+ proname => 'min', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'bpchar',
proargtypes => 'bpchar', prosrc => 'aggregate_dummy' },
{ oid => '2798', descr => 'minimum value of all tid input values',
- proname => 'min', prokind => 'a', proisstrict => 'f', prorettype => 'tid',
+ proname => 'min', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'tid',
proargtypes => 'tid', prosrc => 'aggregate_dummy' },
{ oid => '3565', descr => 'minimum value of all inet input values',
- proname => 'min', prokind => 'a', proisstrict => 'f', prorettype => 'inet',
+ proname => 'min', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'inet',
proargtypes => 'inet', prosrc => 'aggregate_dummy' },
{ oid => '4190', descr => 'minimum value of all pg_lsn input values',
- proname => 'min', prokind => 'a', proisstrict => 'f', prorettype => 'pg_lsn',
+ proname => 'min', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'pg_lsn',
proargtypes => 'pg_lsn', prosrc => 'aggregate_dummy' },
{ oid => '5100', descr => 'minimum value of all xid8 input values',
- proname => 'min', prokind => 'a', proisstrict => 'f', prorettype => 'xid8',
+ proname => 'min', prosupport => 'minmax_support', prokind => 'a', proisstrict => 'f', prorettype => 'xid8',
proargtypes => 'xid8', prosrc => 'aggregate_dummy' },
# count has two forms: count(any) and count(*)
diff --git a/src/include/nodes/supportnodes.h b/src/include/nodes/supportnodes.h
index 5f7bcde891..57c1ed116a 100644
--- a/src/include/nodes/supportnodes.h
+++ b/src/include/nodes/supportnodes.h
@@ -343,4 +343,11 @@ typedef struct SupportRequestOptimizeWindowClause
* optimizations are possible. */
} SupportRequestOptimizeWindowClause;
+typedef struct SupportRequestMinMax
+{
+ NodeTag type;
+
+ Aggref *aggref;
+} SupportRequestMinMax;
+
#endif /* SUPPORTNODES_H */
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index 8ac13b562c..1053e03f25 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -1003,6 +1003,128 @@ select max(unique1) from tenk1 where unique1 > 42;
9999
(1 row)
+-- When sorting on the column that's being aggregated, indexes can also be
+-- used, but only when the aggregate's operator has the same ordering behavior
+-- as the ORDER BY-clause, i.e. if it is in the same btree opclass as the one
+-- chosen for the ORDER BY clause.
+explain (costs off)
+ select min(unique1 ORDER BY unique1 ASC NULLS LAST) from tenk1;
+ QUERY PLAN
+------------------------------------------------------------
+ Result
+ InitPlan 1
+ -> Limit
+ -> Index Only Scan using tenk1_unique1 on tenk1
+ Index Cond: (unique1 IS NOT NULL)
+(5 rows)
+
+select min(unique1 ORDER BY unique1 ASC NULLS LAST) from tenk1;
+ min
+-----
+ 0
+(1 row)
+
+explain (costs off)
+ select max(unique1 ORDER BY unique1 USING <) from tenk1;
+ QUERY PLAN
+---------------------------------------------------------------------
+ Result
+ InitPlan 1
+ -> Limit
+ -> Index Only Scan Backward using tenk1_unique1 on tenk1
+ Index Cond: (unique1 IS NOT NULL)
+(5 rows)
+
+select max(unique1 ORDER BY unique1 USING <) from tenk1;
+ max
+------
+ 9999
+(1 row)
+
+explain (costs off)
+ select max(unique1 ORDER BY tenthous) from tenk1;
+ QUERY PLAN
+-------------------------------
+ Aggregate
+ -> Sort
+ Sort Key: tenthous
+ -> Seq Scan on tenk1
+(4 rows)
+
+select max(unique1 ORDER BY tenthous) from tenk1;
+ max
+------
+ 9999
+(1 row)
+
+CREATE OPERATOR @>@ (function=int4gt, leftarg=int4, rightarg=int4);
+CREATE OPERATOR @<@ (function=int4lt, leftarg=int4, rightarg=int4);
+CREATE OPERATOR @=@ (function=int4eq, leftarg=int4, rightarg=int4);
+CREATE OPERATOR FAMILY my_family USING btree;
+CREATE OPERATOR CLASS my_int_ops
+ FOR TYPE int
+ USING btree FAMILY my_family AS
+ OPERATOR 1 @<@ FOR SEARCH,
+ OPERATOR 5 @>@ FOR SEARCH,
+ OPERATOR 3 @=@,
+ FUNCTION 1 btint4cmp;
+CREATE AGGREGATE my_int_max (
+ BASETYPE = int4,
+ SFUNC = int4larger,
+ STYPE = int4,
+ SORTOP = @>@
+);
+-- Dirty hack in absence of prosupport declaration in the 'CREATE AGGREGATE'
+-- statement.
+UPDATE pg_proc SET prosupport = 'minmax_support'::regproc
+ WHERE proname = 'my_int_max';
+CREATE INDEX idx_int4 ON tenk1 (unique1 my_int_ops);
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT my_int_max(unique1::int4 ORDER BY unique1::int4 USING @<@ ) from tenk1;
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Result
+ Output: (InitPlan 1).col1
+ InitPlan 1
+ -> Limit
+ Output: tenk1.unique1
+ -> Index Only Scan Backward using idx_int4 on public.tenk1
+ Output: tenk1.unique1
+ Index Cond: (tenk1.unique1 IS NOT NULL)
+(8 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT my_int_max(unique1::int4 ORDER BY unique1::int4 USING @>@ ) from tenk1;
+ QUERY PLAN
+-----------------------------------------------------------------------
+ Result
+ Output: (InitPlan 1).col1
+ InitPlan 1
+ -> Limit
+ Output: tenk1.unique1
+ -> Index Only Scan Backward using idx_int4 on public.tenk1
+ Output: tenk1.unique1
+ Index Cond: (tenk1.unique1 IS NOT NULL)
+(8 rows)
+
+DROP AGGREGATE my_int_max(int4);
+DROP OPERATOR CLASS my_int_ops USING btree CASCADE;
+NOTICE: drop cascades to index idx_int4
+DROP OPERATOR FAMILY my_family USING btree;
+DROP OPERATOR @>@ (int4, int4);
+DROP OPERATOR @<@ (int4, int4);
+DROP OPERATOR @=@ (int4, int4);
+-- But even then, the index can't be used if we order by multiple columns.
+explain (costs off)
+ select max(unique1 ORDER BY unique1, tenthous) from tenk1;
+ QUERY PLAN
+-------------------------------------
+ Aggregate
+ -> Sort
+ Sort Key: unique1, tenthous
+ -> Seq Scan on tenk1
+(4 rows)
+
-- the planner may choose a generic aggregate here if parallel query is
-- enabled, since that plan will be parallel safe and the "optimized"
-- plan, which has almost identical cost, will not be. we want to test
@@ -1506,7 +1628,7 @@ from tenk1;
-------------------------------
Aggregate
-> Sort
- Sort Key: four
+ Sort Key: two
-> Seq Scan on tenk1
(4 rows)
@@ -1535,7 +1657,7 @@ from tenk1;
-------------------------------
Aggregate
-> Sort
- Sort Key: four
+ Sort Key: two
-> Seq Scan on tenk1
(4 rows)
@@ -1551,7 +1673,7 @@ from tenk1;
-------------------------------
Aggregate
-> Sort
- Sort Key: ten
+ Sort Key: two
-> Seq Scan on tenk1
(4 rows)
diff --git a/src/test/regress/sql/aggregates.sql b/src/test/regress/sql/aggregates.sql
index ca6d1bcfb7..b6507170eb 100644
--- a/src/test/regress/sql/aggregates.sql
+++ b/src/test/regress/sql/aggregates.sql
@@ -368,6 +368,61 @@ explain (costs off)
select max(unique1) from tenk1 where unique1 > 42;
select max(unique1) from tenk1 where unique1 > 42;
+-- When sorting on the column that's being aggregated, indexes can also be
+-- used, but only when the aggregate's operator has the same ordering behavior
+-- as the ORDER BY-clause, i.e. if it is in the same btree opclass as the one
+-- chosen for the ORDER BY clause.
+explain (costs off)
+ select min(unique1 ORDER BY unique1 ASC NULLS LAST) from tenk1;
+select min(unique1 ORDER BY unique1 ASC NULLS LAST) from tenk1;
+explain (costs off)
+ select max(unique1 ORDER BY unique1 USING <) from tenk1;
+select max(unique1 ORDER BY unique1 USING <) from tenk1;
+explain (costs off)
+ select max(unique1 ORDER BY tenthous) from tenk1;
+select max(unique1 ORDER BY tenthous) from tenk1;
+
+CREATE OPERATOR @>@ (function=int4gt, leftarg=int4, rightarg=int4);
+CREATE OPERATOR @<@ (function=int4lt, leftarg=int4, rightarg=int4);
+CREATE OPERATOR @=@ (function=int4eq, leftarg=int4, rightarg=int4);
+
+CREATE OPERATOR FAMILY my_family USING btree;
+CREATE OPERATOR CLASS my_int_ops
+ FOR TYPE int
+ USING btree FAMILY my_family AS
+ OPERATOR 1 @<@ FOR SEARCH,
+ OPERATOR 5 @>@ FOR SEARCH,
+ OPERATOR 3 @=@,
+ FUNCTION 1 btint4cmp;
+
+CREATE AGGREGATE my_int_max (
+ BASETYPE = int4,
+ SFUNC = int4larger,
+ STYPE = int4,
+ SORTOP = @>@
+);
+
+-- Dirty hack in absence of prosupport declaration in the 'CREATE AGGREGATE'
+-- statement.
+UPDATE pg_proc SET prosupport = 'minmax_support'::regproc
+ WHERE proname = 'my_int_max';
+CREATE INDEX idx_int4 ON tenk1 (unique1 my_int_ops);
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT my_int_max(unique1::int4 ORDER BY unique1::int4 USING @<@ ) from tenk1;
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT my_int_max(unique1::int4 ORDER BY unique1::int4 USING @>@ ) from tenk1;
+
+DROP AGGREGATE my_int_max(int4);
+DROP OPERATOR CLASS my_int_ops USING btree CASCADE;
+DROP OPERATOR FAMILY my_family USING btree;
+DROP OPERATOR @>@ (int4, int4);
+DROP OPERATOR @<@ (int4, int4);
+DROP OPERATOR @=@ (int4, int4);
+
+-- But even then, the index can't be used if we order by multiple columns.
+explain (costs off)
+ select max(unique1 ORDER BY unique1, tenthous) from tenk1;
+
-- the planner may choose a generic aggregate here if parallel query is
-- enabled, since that plan will be parallel safe and the "optimized"
-- plan, which has almost identical cost, will not be. we want to test
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index a65e1c07c5..823702e819 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2781,6 +2781,7 @@ SubscriptionRelState
SummarizerReadLocalXLogPrivate
SupportRequestCost
SupportRequestIndexCondition
+SupportRequestMinMax
SupportRequestOptimizeWindowClause
SupportRequestRows
SupportRequestSelectivity
--
2.39.5
