agora inbox for [email protected]
help / color / mirror / Atom feed[PATCH v42 1/2] Subscripting for jsonb
17+ messages / 7 participants
[nested] [flat]
* [PATCH v42 1/2] Subscripting for jsonb
@ 2020-12-18 16:19 Dmitrii Dolgov <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: Dmitrii Dolgov @ 2020-12-18 16:19 UTC (permalink / raw)
Subscripting implementation for jsonb. It does not support slices, does
not have a limit for number of subscripts and for assignment expects a
replace value to be of jsonb type. There is also one functional
difference in assignment via subscripting from jsonb_set, when an
original jsonb container is NULL, subscripting replaces it with an empty
jsonb and proceed with assignment.
For the sake of code reuse, some parts of jsonb functionality were
rearranged to allow use the same functions for jsonb_set and assign
subscripting operation.
The original idea belongs to Oleg Bartunov.
Reviewed-by: Tom Lane, Arthur Zakirov, Pavel Stehule
---
doc/src/sgml/json.sgml | 48 ++++
src/backend/utils/adt/Makefile | 1 +
src/backend/utils/adt/jsonb_util.c | 76 ++++-
src/backend/utils/adt/jsonbsubs.c | 413 ++++++++++++++++++++++++++++
src/backend/utils/adt/jsonfuncs.c | 180 ++++++------
src/include/catalog/pg_proc.dat | 4 +
src/include/catalog/pg_type.dat | 3 +-
src/include/utils/jsonb.h | 6 +-
src/test/regress/expected/jsonb.out | 272 +++++++++++++++++-
src/test/regress/sql/jsonb.sql | 84 +++++-
10 files changed, 982 insertions(+), 105 deletions(-)
create mode 100644 src/backend/utils/adt/jsonbsubs.c
diff --git a/doc/src/sgml/json.sgml b/doc/src/sgml/json.sgml
index 5b9a5557a4..100d1a60f4 100644
--- a/doc/src/sgml/json.sgml
+++ b/doc/src/sgml/json.sgml
@@ -602,6 +602,54 @@ SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qu
</para>
</sect2>
+ <sect2 id="jsonb-subscripting">
+ <title><type>jsonb</type> Subscripting</title>
+ <para>
+ <type>jsonb</type> data type supports array-style subscripting expressions
+ to extract or update particular elements. It's possible to use multiple
+ subscripting expressions to extract nested values. In this case, a chain of
+ subscripting expressions follows the same rules as the
+ <literal>path</literal> argument in <literal>jsonb_set</literal> function,
+ e.g. in case of arrays it is a 0-based operation or that negative integers
+ that appear in <literal>path</literal> count from the end of JSON arrays.
+ The result of subscripting expressions is always jsonb data type. An
+ example of subscripting syntax:
+<programlisting>
+-- Extract value by key
+SELECT ('{"a": 1}'::jsonb)['a'];
+
+-- Extract nested value by key path
+SELECT ('{"a": {"b": {"c": 1}}}'::jsonb)['a']['b']['c'];
+
+-- Extract element by index
+SELECT ('[1, "2", null]'::jsonb)[1];
+
+-- Update value by key, note the single quotes - the assigned value
+-- needs to be of jsonb type as well
+UPDATE table_name SET jsonb_field['key'] = '1';
+
+-- Select records using where clause with subscripting. Since the result of
+-- subscripting is jsonb and we basically want to compare two jsonb objects, we
+-- need to put the value in double quotes to be able to convert it to jsonb.
+SELECT * FROM table_name WHERE jsonb_field['key'] = '"value"';
+</programlisting>
+
+ Subscripting for <type>jsonb</type> does not support slice expressions,
+ even if it contains an array.
+
+ In case if source <type>jsonb</type> is <literal>NULL</literal>, assignment
+ via subscripting will proceed as if it was an empty JSON object:
+<programlisting>
+-- If jsonb_field here is NULL, the result is {"a": 1}
+UPDATE table_name SET jsonb_field['a'] = '1';
+
+-- If jsonb_field here is NULL, the result is [1]
+UPDATE table_name SET jsonb_field[0] = '1';
+</programlisting>
+
+ </para>
+ </sect2>
+
<sect2>
<title>Transforms</title>
diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile
index 82732146d3..279ff15ade 100644
--- a/src/backend/utils/adt/Makefile
+++ b/src/backend/utils/adt/Makefile
@@ -50,6 +50,7 @@ OBJS = \
jsonb_op.o \
jsonb_util.o \
jsonfuncs.o \
+ jsonbsubs.o \
jsonpath.o \
jsonpath_exec.o \
jsonpath_gram.o \
diff --git a/src/backend/utils/adt/jsonb_util.c b/src/backend/utils/adt/jsonb_util.c
index 4eeffa1424..41a1c1f9bb 100644
--- a/src/backend/utils/adt/jsonb_util.c
+++ b/src/backend/utils/adt/jsonb_util.c
@@ -68,18 +68,29 @@ static JsonbValue *pushJsonbValueScalar(JsonbParseState **pstate,
JsonbIteratorToken seq,
JsonbValue *scalarVal);
+JsonbValue *
+JsonbToJsonbValue(Jsonb *jsonb)
+{
+ JsonbValue *val = (JsonbValue *) palloc(sizeof(JsonbValue));
+
+ val->type = jbvBinary;
+ val->val.binary.data = &jsonb->root;
+ val->val.binary.len = VARSIZE(jsonb) - VARHDRSZ;
+
+ return val;
+}
+
/*
* Turn an in-memory JsonbValue into a Jsonb for on-disk storage.
*
- * There isn't a JsonbToJsonbValue(), because generally we find it more
- * convenient to directly iterate through the Jsonb representation and only
- * really convert nested scalar values. JsonbIteratorNext() does this, so that
- * clients of the iteration code don't have to directly deal with the binary
- * representation (JsonbDeepContains() is a notable exception, although all
- * exceptions are internal to this module). In general, functions that accept
- * a JsonbValue argument are concerned with the manipulation of scalar values,
- * or simple containers of scalar values, where it would be inconvenient to
- * deal with a great amount of other state.
+ * Generally we find it more convenient to directly iterate through the Jsonb
+ * representation and only really convert nested scalar values.
+ * JsonbIteratorNext() does this, so that clients of the iteration code don't
+ * have to directly deal with the binary representation (JsonbDeepContains() is
+ * a notable exception, although all exceptions are internal to this module).
+ * In general, functions that accept a JsonbValue argument are concerned with
+ * the manipulation of scalar values, or simple containers of scalar values,
+ * where it would be inconvenient to deal with a great amount of other state.
*/
Jsonb *
JsonbValueToJsonb(JsonbValue *val)
@@ -563,6 +574,30 @@ pushJsonbValue(JsonbParseState **pstate, JsonbIteratorToken seq,
JsonbValue *res = NULL;
JsonbValue v;
JsonbIteratorToken tok;
+ int i;
+
+ if (jbval && (seq == WJB_ELEM || seq == WJB_VALUE) && jbval->type == jbvObject)
+ {
+ pushJsonbValue(pstate, WJB_BEGIN_OBJECT, NULL);
+ for (i = 0; i < jbval->val.object.nPairs; i++)
+ {
+ pushJsonbValue(pstate, WJB_KEY, &jbval->val.object.pairs[i].key);
+ pushJsonbValue(pstate, WJB_VALUE, &jbval->val.object.pairs[i].value);
+ }
+
+ return pushJsonbValue(pstate, WJB_END_OBJECT, NULL);
+ }
+
+ if (jbval && (seq == WJB_ELEM || seq == WJB_VALUE) && jbval->type == jbvArray)
+ {
+ pushJsonbValue(pstate, WJB_BEGIN_ARRAY, NULL);
+ for (i = 0; i < jbval->val.array.nElems; i++)
+ {
+ pushJsonbValue(pstate, WJB_ELEM, &jbval->val.array.elems[i]);
+ }
+
+ return pushJsonbValue(pstate, WJB_END_ARRAY, NULL);
+ }
if (!jbval || (seq != WJB_ELEM && seq != WJB_VALUE) ||
jbval->type != jbvBinary)
@@ -573,9 +608,30 @@ pushJsonbValue(JsonbParseState **pstate, JsonbIteratorToken seq,
/* unpack the binary and add each piece to the pstate */
it = JsonbIteratorInit(jbval->val.binary.data);
+
+ if ((jbval->val.binary.data->header & JB_FSCALAR) && *pstate)
+ {
+ tok = JsonbIteratorNext(&it, &v, true);
+ Assert(tok == WJB_BEGIN_ARRAY);
+ Assert(v.type == jbvArray && v.val.array.rawScalar);
+
+ tok = JsonbIteratorNext(&it, &v, true);
+ Assert(tok == WJB_ELEM);
+
+ res = pushJsonbValueScalar(pstate, seq, &v);
+
+ tok = JsonbIteratorNext(&it, &v, true);
+ Assert(tok == WJB_END_ARRAY);
+ Assert(it == NULL);
+
+ return res;
+ }
+
while ((tok = JsonbIteratorNext(&it, &v, false)) != WJB_DONE)
res = pushJsonbValueScalar(pstate, tok,
- tok < WJB_BEGIN_ARRAY ? &v : NULL);
+ tok < WJB_BEGIN_ARRAY ||
+ (tok == WJB_BEGIN_ARRAY &&
+ v.val.array.rawScalar) ? &v : NULL);
return res;
}
diff --git a/src/backend/utils/adt/jsonbsubs.c b/src/backend/utils/adt/jsonbsubs.c
new file mode 100644
index 0000000000..306c37b5a6
--- /dev/null
+++ b/src/backend/utils/adt/jsonbsubs.c
@@ -0,0 +1,413 @@
+/*-------------------------------------------------------------------------
+ *
+ * jsonbsubs.c
+ * Subscripting support functions for jsonb.
+ *
+ * Portions Copyright (c) 1996-2020, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ *
+ * IDENTIFICATION
+ * src/backend/utils/adt/jsonbsubs.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "executor/execExpr.h"
+#include "nodes/makefuncs.h"
+#include "nodes/nodeFuncs.h"
+#include "nodes/subscripting.h"
+#include "parser/parse_coerce.h"
+#include "parser/parse_expr.h"
+#include "utils/jsonb.h"
+#include "utils/jsonfuncs.h"
+#include "utils/builtins.h"
+#include "utils/lsyscache.h"
+
+
+/* SubscriptingRefState.workspace for jsonb subscripting execution */
+typedef struct JsonbSubWorkspace
+{
+ bool expectArray; /* jsonb root is expected to be an array */
+ Oid *indexOid; /* OID of coerced subscript expression,
+ could be only integer or text */
+ Datum *index; /* Subscript values in Datum format */
+} JsonbSubWorkspace;
+
+
+/*
+ * Finish parse analysis of a SubscriptingRef expression for a jsonb.
+ *
+ * Transform the subscript expressions, coerce them to text,
+ * and determine the result type of the SubscriptingRef node.
+ */
+static void
+jsonb_subscript_transform(SubscriptingRef *sbsref,
+ List *indirection,
+ ParseState *pstate,
+ bool isSlice,
+ bool isAssignment)
+{
+ List *upperIndexpr = NIL;
+ ListCell *idx;
+
+ /*
+ * Transform and convert the subscript expressions. Jsonb subscripting does
+ * not support slices, look only and the upper index.
+ */
+ foreach(idx, indirection)
+ {
+ A_Indices *ai = lfirst_node(A_Indices, idx);
+ Node *subExpr;
+
+ if (isSlice)
+ {
+ Node *expr = ai->uidx ? ai->uidx : ai->lidx;
+
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("jsonb subscript does not support slices"),
+ parser_errposition(pstate, exprLocation(expr))));
+ }
+
+ if (ai->uidx)
+ {
+ Oid subExprType = InvalidOid,
+ targetType = UNKNOWNOID;
+
+ subExpr = transformExpr(pstate, ai->uidx, pstate->p_expr_kind);
+ subExprType = exprType(subExpr);
+
+ if (subExprType != UNKNOWNOID)
+ {
+ Oid targets[2] = {INT4OID, TEXTOID};
+
+ /*
+ * Jsonb can handle multiple subscript types, but cases when a
+ * subscript could be coerced to multiple target types must be
+ * avoided, similar to overloaded functions. It could be
+ * possibly extend with jsonpath in the future.
+ */
+ for (int i = 0; i < 2; i++)
+ {
+ if (can_coerce_type(1, &subExprType, &targets[i], COERCION_IMPLICIT))
+ {
+ /*
+ * One type has already succeeded, it means there are
+ * two coercion targets possible, failure.
+ */
+ if (targetType != UNKNOWNOID)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("subscript type is not supported"),
+ errhint("Jsonb subscript must be coerced "
+ "only to one type, integer or text."),
+ parser_errposition(pstate, exprLocation(subExpr))));
+
+ targetType = targets[i];
+ }
+ }
+
+ /*
+ * No suitable types were found, failure.
+ */
+ if (targetType == UNKNOWNOID)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("subscript type is not supported"),
+ errhint("Jsonb subscript must be coercet to either integer or text"),
+ parser_errposition(pstate, exprLocation(subExpr))));
+ }
+ else
+ targetType = TEXTOID;
+
+ /*
+ * We known from can_coerce_type that coercion will succeed, so
+ * coerce_type could be used. Note the implicit coercion context,
+ * which is required to handle subscripts of different types,
+ * similar to overloaded functions.
+ */
+ subExpr = coerce_type(pstate,
+ subExpr, subExprType,
+ targetType, -1,
+ COERCION_IMPLICIT,
+ COERCE_IMPLICIT_CAST,
+ -1);
+ if (subExpr == NULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("jsonb subscript must have text type"),
+ parser_errposition(pstate, exprLocation(subExpr))));
+ }
+ else
+ {
+ /*
+ * Slice with omitted upper bound. Should not happen as we already
+ * errored out on slice earlier, but handle this just in case.
+ */
+ Assert(isSlice && ai->is_slice);
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("jsonb subscript does not support slices"),
+ parser_errposition(pstate, exprLocation(ai->uidx))));
+ }
+
+ upperIndexpr = lappend(upperIndexpr, subExpr);
+ }
+
+ /* store the transformed lists into the SubscriptRef node */
+ sbsref->refupperindexpr = upperIndexpr;
+ sbsref->reflowerindexpr = NIL;
+
+ /* Determine the result type of the subscripting operation; always jsonb */
+ sbsref->refrestype = JSONBOID;
+ sbsref->reftypmod = -1;
+}
+
+/*
+ * During execution, process the subscripts in a SubscriptingRef expression.
+ *
+ * The subscript expressions are already evaluated in Datum form in the
+ * SubscriptingRefState's arrays. Check and convert them as necessary.
+ *
+ * If any subscript is NULL, we throw error in assignment cases, or in fetch
+ * cases set result to NULL and return false (instructing caller to skip the
+ * rest of the SubscriptingRef sequence).
+ */
+static bool
+jsonb_subscript_check_subscripts(ExprState *state,
+ ExprEvalStep *op,
+ ExprContext *econtext)
+{
+ SubscriptingRefState *sbsrefstate = op->d.sbsref_subscript.state;
+ JsonbSubWorkspace *workspace = (JsonbSubWorkspace *) sbsrefstate->workspace;
+
+ /*
+ * In case if the first subscript is an integer, the source jsonb is
+ * expected to be an array. This information is not used directly, all such
+ * cases are handled within corresponding jsonb assign functions. But if
+ * the source jsonb is NULL the expected type will be used to construct an
+ * empty source.
+ */
+ if (sbsrefstate->numupper > 0 && sbsrefstate->upperprovided[0] &&
+ !sbsrefstate->upperindexnull[0] && workspace->indexOid[0] == INT4OID)
+ workspace->expectArray = true;
+
+ /* Process upper subscripts */
+ for (int i = 0; i < sbsrefstate->numupper; i++)
+ {
+ if (sbsrefstate->upperprovided[i])
+ {
+ /* If any index expr yields NULL, result is NULL or error */
+ if (sbsrefstate->upperindexnull[i])
+ {
+ if (sbsrefstate->isassignment)
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("jsonb subscript in assignment must not be null")));
+ *op->resnull = true;
+ return false;
+ }
+
+ /*
+ * For jsonb fetch and assign functions we need to provide path in
+ * text format. Convert if it's not already text.
+ */
+ if (workspace->indexOid[i] == INT4OID)
+ {
+ Datum datum = sbsrefstate->upperindex[i];
+ char *cs = DatumGetCString(DirectFunctionCall1(int4out, datum));
+ workspace->index[i] = CStringGetTextDatum(cs);
+ }
+ else
+ workspace->index[i] = sbsrefstate->upperindex[i];
+ }
+ }
+
+ return true;
+}
+
+/*
+ * Evaluate SubscriptingRef fetch for a jsonb element.
+ *
+ * Source container is in step's result variable (it's known not NULL, since
+ * we set fetch_strict to true).
+ */
+static void
+jsonb_subscript_fetch(ExprState *state,
+ ExprEvalStep *op,
+ ExprContext *econtext)
+{
+ SubscriptingRefState *sbsrefstate = op->d.sbsref.state;
+ JsonbSubWorkspace *workspace = (JsonbSubWorkspace *) sbsrefstate->workspace;
+ Jsonb *jsonbSource;
+
+ /* Should not get here if source jsonb (or any subscript) is null */
+ Assert(!(*op->resnull));
+
+ jsonbSource = DatumGetJsonbP(*op->resvalue);
+ *op->resvalue = jsonb_get_element(jsonbSource,
+ workspace->index,
+ sbsrefstate->numupper,
+ op->resnull,
+ false);
+}
+
+/*
+ * Evaluate SubscriptingRef assignment for a jsonb element assignment.
+ *
+ * Input container (possibly null) is in result area, replacement value is in
+ * SubscriptingRefState's replacevalue/replacenull.
+ */
+static void
+jsonb_subscript_assign(ExprState *state,
+ ExprEvalStep *op,
+ ExprContext *econtext)
+{
+ SubscriptingRefState *sbsrefstate = op->d.sbsref.state;
+ JsonbSubWorkspace *workspace = (JsonbSubWorkspace *) sbsrefstate->workspace;
+ Jsonb *jsonbSource;
+ JsonbValue *replacevalue;
+
+ if (sbsrefstate->replacenull)
+ {
+ replacevalue = (JsonbValue *) palloc(sizeof(JsonbValue));
+ replacevalue->type = jbvNull;
+ }
+ else
+ replacevalue =
+ JsonbToJsonbValue(DatumGetJsonbP(sbsrefstate->replacevalue));
+
+ /*
+ * In case if the input container is null, set up an empty jsonb and
+ * proceed with the assignment.
+ */
+ if (*op->resnull)
+ {
+ JsonbValue *newSource = (JsonbValue *) palloc(sizeof(JsonbValue));
+
+ /*
+ * To avoid any surprising results, set up an empty jsonb array in case
+ * of an array is expected (i.e. the first subscript is integer),
+ * otherwise jsonb object.
+ */
+ if (workspace->expectArray)
+ {
+ newSource->type = jbvArray;
+ newSource->val.array.nElems = 0;
+ newSource->val.array.rawScalar = false;
+ }
+ else
+ {
+ newSource->type = jbvObject;
+ newSource->val.object.nPairs = 0;
+ }
+
+ jsonbSource = JsonbValueToJsonb(newSource);
+ *op->resnull = false;
+ }
+ else
+ jsonbSource = DatumGetJsonbP(*op->resvalue);
+
+ *op->resvalue = jsonb_set_element(jsonbSource,
+ workspace->index,
+ sbsrefstate->numupper,
+ replacevalue);
+ /* The result is never NULL, so no need to change *op->resnull */
+}
+
+/*
+ * Compute old jsonb element value for a SubscriptingRef assignment
+ * expression. Will only be called if the new-value subexpression
+ * contains SubscriptingRef or FieldStore. This is the same as the
+ * regular fetch case, except that we have to handle a null jsonb,
+ * and the value should be stored into the SubscriptingRefState's
+ * prevvalue/prevnull fields.
+ */
+static void
+jsonb_subscript_fetch_old(ExprState *state,
+ ExprEvalStep *op,
+ ExprContext *econtext)
+{
+ SubscriptingRefState *sbsrefstate = op->d.sbsref.state;
+
+ if (*op->resnull)
+ {
+ /* whole jsonb is null, so any element is too */
+ sbsrefstate->prevvalue = (Datum) 0;
+ sbsrefstate->prevnull = true;
+ }
+ else
+ {
+ Jsonb *jsonbSource = DatumGetJsonbP(*op->resvalue);
+ sbsrefstate->prevvalue = jsonb_get_element(jsonbSource,
+ sbsrefstate->upperindex,
+ sbsrefstate->numupper,
+ &sbsrefstate->prevnull,
+ false);
+ }
+}
+
+/*
+ * Set up execution state for a jsonb subscript operation. Opposite to the
+ * arrays subscription, there is no limit for number of subscripts as jsonb
+ * type itself doesn't have nesting limits.
+ */
+static void
+jsonb_exec_setup(const SubscriptingRef *sbsref,
+ SubscriptingRefState *sbsrefstate,
+ SubscriptExecSteps *methods)
+{
+ JsonbSubWorkspace *workspace;
+ ListCell *lc;
+ int nupper = sbsref->refupperindexpr->length;
+ char *ptr;
+
+ /* Allocate type-specific workspace with space for per-subscript data */
+ workspace = palloc0(MAXALIGN(sizeof(JsonbSubWorkspace)) +
+ nupper * (sizeof(Datum) + sizeof(Oid)));
+ workspace->expectArray = false;
+ ptr = ((char *) workspace) + MAXALIGN(sizeof(JsonbSubWorkspace));
+ workspace->indexOid = (Oid *) ptr;
+ ptr += nupper * sizeof(Oid);
+ workspace->index = (Datum *) ptr;
+
+ sbsrefstate->workspace = workspace;
+
+ /* Collect subscript data types necessary at execution time */
+ foreach(lc, sbsref->refupperindexpr)
+ {
+ Node *expr = lfirst(lc);
+ int i = foreach_current_index(lc);
+
+ workspace->indexOid[i] = exprType(expr);
+ }
+
+ /*
+ * Pass back pointers to appropriate step execution functions.
+ */
+ methods->sbs_check_subscripts = jsonb_subscript_check_subscripts;
+ methods->sbs_fetch = jsonb_subscript_fetch;
+ methods->sbs_assign = jsonb_subscript_assign;
+ methods->sbs_fetch_old = jsonb_subscript_fetch_old;
+}
+
+/*
+ * jsonb_subscript_handler
+ * Subscripting handler for jsonb.
+ *
+ */
+Datum
+jsonb_subscript_handler(PG_FUNCTION_ARGS)
+{
+ static const SubscriptRoutines sbsroutines = {
+ .transform = jsonb_subscript_transform,
+ .exec_setup = jsonb_exec_setup,
+ .fetch_strict = true, /* fetch returns NULL for NULL inputs */
+ .fetch_leakproof = true, /* fetch returns NULL for bad subscript */
+ .store_leakproof = false /* ... but assignment throws error */
+ };
+
+ PG_RETURN_POINTER(&sbsroutines);
+}
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index 69100feab7..5a0ba6b220 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -461,18 +461,18 @@ static Datum populate_domain(DomainIOData *io, Oid typid, const char *colname,
/* functions supporting jsonb_delete, jsonb_set and jsonb_concat */
static JsonbValue *IteratorConcat(JsonbIterator **it1, JsonbIterator **it2,
JsonbParseState **state);
-static JsonbValue *setPath(JsonbIterator **it, Datum *path_elems,
+extern JsonbValue *setPath(JsonbIterator **it, Datum *path_elems,
bool *path_nulls, int path_len,
- JsonbParseState **st, int level, Jsonb *newval,
+ JsonbParseState **st, int level, JsonbValue *newval,
int op_type);
static void setPathObject(JsonbIterator **it, Datum *path_elems,
bool *path_nulls, int path_len, JsonbParseState **st,
int level,
- Jsonb *newval, uint32 npairs, int op_type);
+ JsonbValue *newval, uint32 npairs, int op_type);
static void setPathArray(JsonbIterator **it, Datum *path_elems,
bool *path_nulls, int path_len, JsonbParseState **st,
- int level, Jsonb *newval, uint32 nelems, int op_type);
-static void addJsonbToParseState(JsonbParseState **jbps, Jsonb *jb);
+ int level,
+ JsonbValue *newval, uint32 nelems, int op_type);
/* function supporting iterate_json_values */
static void iterate_values_scalar(void *state, char *token, JsonTokenType tokentype);
@@ -1448,13 +1448,9 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text)
ArrayType *path = PG_GETARG_ARRAYTYPE_P(1);
Datum *pathtext;
bool *pathnulls;
+ bool isnull;
int npath;
- int i;
- bool have_object = false,
- have_array = false;
- JsonbValue *jbvp = NULL;
- JsonbValue jbvbuf;
- JsonbContainer *container;
+ Datum res;
/*
* If the array contains any null elements, return NULL, on the grounds
@@ -1469,9 +1465,26 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text)
deconstruct_array(path, TEXTOID, -1, false, TYPALIGN_INT,
&pathtext, &pathnulls, &npath);
- /* Identify whether we have object, array, or scalar at top-level */
- container = &jb->root;
+ res = jsonb_get_element(jb, pathtext, npath, &isnull, as_text);
+ if (isnull)
+ PG_RETURN_NULL();
+ else
+ PG_RETURN_DATUM(res);
+}
+
+Datum
+jsonb_get_element(Jsonb *jb, Datum *path, int npath, bool *isnull, bool as_text)
+{
+ JsonbContainer *container = &jb->root;
+ JsonbValue *jbvp = NULL;
+ int i;
+ bool have_object = false,
+ have_array = false;
+
+ *isnull = false;
+
+ /* Identify whether we have object, array, or scalar at top-level */
if (JB_ROOT_IS_OBJECT(jb))
have_object = true;
else if (JB_ROOT_IS_ARRAY(jb) && !JB_ROOT_IS_SCALAR(jb))
@@ -1496,7 +1509,7 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text)
{
if (as_text)
{
- PG_RETURN_TEXT_P(cstring_to_text(JsonbToCString(NULL,
+ return PointerGetDatum(cstring_to_text(JsonbToCString(NULL,
container,
VARSIZE(jb))));
}
@@ -1512,22 +1525,25 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text)
if (have_object)
{
jbvp = getKeyJsonValueFromContainer(container,
- VARDATA(pathtext[i]),
- VARSIZE(pathtext[i]) - VARHDRSZ,
- &jbvbuf);
+ VARDATA(path[i]),
+ VARSIZE(path[i]) - VARHDRSZ,
+ NULL);
}
else if (have_array)
{
long lindex;
uint32 index;
- char *indextext = TextDatumGetCString(pathtext[i]);
+ char *indextext = TextDatumGetCString(path[i]);
char *endptr;
errno = 0;
lindex = strtol(indextext, &endptr, 10);
if (endptr == indextext || *endptr != '\0' || errno != 0 ||
lindex > INT_MAX || lindex < INT_MIN)
- PG_RETURN_NULL();
+ {
+ *isnull = true;
+ return PointerGetDatum(NULL);
+ }
if (lindex >= 0)
{
@@ -1545,7 +1561,10 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text)
nelements = JsonContainerSize(container);
if (-lindex > nelements)
- PG_RETURN_NULL();
+ {
+ *isnull = true;
+ return PointerGetDatum(NULL);
+ }
else
index = nelements + lindex;
}
@@ -1555,11 +1574,15 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text)
else
{
/* scalar, extraction yields a null */
- PG_RETURN_NULL();
+ *isnull = true;
+ return PointerGetDatum(NULL);
}
if (jbvp == NULL)
- PG_RETURN_NULL();
+ {
+ *isnull = true;
+ return PointerGetDatum(NULL);
+ }
else if (i == npath - 1)
break;
@@ -1581,9 +1604,12 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text)
if (as_text)
{
if (jbvp->type == jbvNull)
- PG_RETURN_NULL();
+ {
+ *isnull = true;
+ return PointerGetDatum(NULL);
+ }
- PG_RETURN_TEXT_P(JsonbValueAsText(jbvp));
+ return PointerGetDatum(JsonbValueAsText(jbvp));
}
else
{
@@ -1594,6 +1620,28 @@ get_jsonb_path_all(FunctionCallInfo fcinfo, bool as_text)
}
}
+Datum
+jsonb_set_element(Jsonb* jb, Datum *path, int path_len,
+ JsonbValue *newval)
+{
+ JsonbValue *res;
+ JsonbParseState *state = NULL;
+ JsonbIterator *it;
+ bool *path_nulls = palloc0(path_len * sizeof(bool));
+
+ if (newval->type == jbvArray && newval->val.array.rawScalar)
+ *newval = newval->val.array.elems[0];
+
+ it = JsonbIteratorInit(&jb->root);
+
+ res = setPath(&it, path, path_nulls, path_len, &state, 0,
+ newval, JB_PATH_CREATE);
+
+ pfree(path_nulls);
+
+ PG_RETURN_JSONB_P(JsonbValueToJsonb(res));
+}
+
/*
* Return the text representation of the given JsonbValue.
*/
@@ -4151,58 +4199,6 @@ jsonb_strip_nulls(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(JsonbValueToJsonb(res));
}
-/*
- * Add values from the jsonb to the parse state.
- *
- * If the parse state container is an object, the jsonb is pushed as
- * a value, not a key.
- *
- * This needs to be done using an iterator because pushJsonbValue doesn't
- * like getting jbvBinary values, so we can't just push jb as a whole.
- */
-static void
-addJsonbToParseState(JsonbParseState **jbps, Jsonb *jb)
-{
- JsonbIterator *it;
- JsonbValue *o = &(*jbps)->contVal;
- JsonbValue v;
- JsonbIteratorToken type;
-
- it = JsonbIteratorInit(&jb->root);
-
- Assert(o->type == jbvArray || o->type == jbvObject);
-
- if (JB_ROOT_IS_SCALAR(jb))
- {
- (void) JsonbIteratorNext(&it, &v, false); /* skip array header */
- Assert(v.type == jbvArray);
- (void) JsonbIteratorNext(&it, &v, false); /* fetch scalar value */
-
- switch (o->type)
- {
- case jbvArray:
- (void) pushJsonbValue(jbps, WJB_ELEM, &v);
- break;
- case jbvObject:
- (void) pushJsonbValue(jbps, WJB_VALUE, &v);
- break;
- default:
- elog(ERROR, "unexpected parent of nested structure");
- }
- }
- else
- {
- while ((type = JsonbIteratorNext(&it, &v, false)) != WJB_DONE)
- {
- if (type == WJB_KEY || type == WJB_VALUE || type == WJB_ELEM)
- (void) pushJsonbValue(jbps, type, &v);
- else
- (void) pushJsonbValue(jbps, type, NULL);
- }
- }
-
-}
-
/*
* SQL function jsonb_pretty (jsonb)
*
@@ -4474,7 +4470,8 @@ jsonb_set(PG_FUNCTION_ARGS)
{
Jsonb *in = PG_GETARG_JSONB_P(0);
ArrayType *path = PG_GETARG_ARRAYTYPE_P(1);
- Jsonb *newval = PG_GETARG_JSONB_P(2);
+ Jsonb *newjsonb = PG_GETARG_JSONB_P(2);
+ JsonbValue *newval = JsonbToJsonbValue(newjsonb);
bool create = PG_GETARG_BOOL(3);
JsonbValue *res = NULL;
Datum *path_elems;
@@ -4632,7 +4629,8 @@ jsonb_insert(PG_FUNCTION_ARGS)
{
Jsonb *in = PG_GETARG_JSONB_P(0);
ArrayType *path = PG_GETARG_ARRAYTYPE_P(1);
- Jsonb *newval = PG_GETARG_JSONB_P(2);
+ Jsonb *newjsonb = PG_GETARG_JSONB_P(2);
+ JsonbValue *newval = JsonbToJsonbValue(newjsonb);
bool after = PG_GETARG_BOOL(3);
JsonbValue *res = NULL;
Datum *path_elems;
@@ -4787,10 +4785,10 @@ IteratorConcat(JsonbIterator **it1, JsonbIterator **it2,
* All path elements before the last must already exist
* whatever bits in op_type are set, or nothing is done.
*/
-static JsonbValue *
+JsonbValue *
setPath(JsonbIterator **it, Datum *path_elems,
bool *path_nulls, int path_len,
- JsonbParseState **st, int level, Jsonb *newval, int op_type)
+ JsonbParseState **st, int level, JsonbValue *newval, int op_type)
{
JsonbValue v;
JsonbIteratorToken r;
@@ -4843,11 +4841,11 @@ setPath(JsonbIterator **it, Datum *path_elems,
static void
setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
int path_len, JsonbParseState **st, int level,
- Jsonb *newval, uint32 npairs, int op_type)
+ JsonbValue *newval, uint32 npairs, int op_type)
{
- JsonbValue v;
int i;
- JsonbValue k;
+ JsonbValue k,
+ v;
bool done = false;
if (level >= path_len || path_nulls[level])
@@ -4864,7 +4862,7 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
newkey.val.string.val = VARDATA_ANY(path_elems[level]);
(void) pushJsonbValue(st, WJB_KEY, &newkey);
- addJsonbToParseState(st, newval);
+ (void) pushJsonbValue(st, WJB_VALUE, newval);
}
for (i = 0; i < npairs; i++)
@@ -4895,7 +4893,7 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
if (!(op_type & JB_PATH_DELETE))
{
(void) pushJsonbValue(st, WJB_KEY, &k);
- addJsonbToParseState(st, newval);
+ (void) pushJsonbValue(st, WJB_VALUE, newval);
}
done = true;
}
@@ -4918,7 +4916,7 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
newkey.val.string.val = VARDATA_ANY(path_elems[level]);
(void) pushJsonbValue(st, WJB_KEY, &newkey);
- addJsonbToParseState(st, newval);
+ (void) pushJsonbValue(st, WJB_VALUE, newval);
}
(void) pushJsonbValue(st, r, &k);
@@ -4950,7 +4948,7 @@ setPathObject(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
static void
setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
int path_len, JsonbParseState **st, int level,
- Jsonb *newval, uint32 nelems, int op_type)
+ JsonbValue *newval, uint32 nelems, int op_type)
{
JsonbValue v;
int idx,
@@ -4998,7 +4996,7 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
(op_type & JB_PATH_CREATE_OR_INSERT))
{
Assert(newval != NULL);
- addJsonbToParseState(st, newval);
+ (void) pushJsonbValue(st, WJB_ELEM, newval);
done = true;
}
@@ -5014,7 +5012,7 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
r = JsonbIteratorNext(it, &v, true); /* skip */
if (op_type & (JB_PATH_INSERT_BEFORE | JB_PATH_CREATE))
- addJsonbToParseState(st, newval);
+ (void) pushJsonbValue(st, WJB_ELEM, newval);
/*
* We should keep current value only in case of
@@ -5025,7 +5023,7 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
(void) pushJsonbValue(st, r, &v);
if (op_type & (JB_PATH_INSERT_AFTER | JB_PATH_REPLACE))
- addJsonbToParseState(st, newval);
+ (void) pushJsonbValue(st, WJB_ELEM, newval);
done = true;
}
@@ -5059,7 +5057,7 @@ setPathArray(JsonbIterator **it, Datum *path_elems, bool *path_nulls,
if ((op_type & JB_PATH_CREATE_OR_INSERT) && !done &&
level == path_len - 1 && i == nelems - 1)
{
- addJsonbToParseState(st, newval);
+ (void) pushJsonbValue(st, WJB_ELEM, newval);
}
}
}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 139f4a08bd..feae8cc4b0 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -11239,6 +11239,10 @@
{ oid => '9256', descr => 'raw array subscripting support',
proname => 'raw_array_subscript_handler', prorettype => 'internal',
proargtypes => 'internal', prosrc => 'raw_array_subscript_handler' },
+# type subscripting support
+{ oid => '6098', descr => 'jsonb subscripting logic',
+ proname => 'jsonb_subscript_handler', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'jsonb_subscript_handler' },
# collation management functions
{ oid => '3445', descr => 'import collations from operating system',
diff --git a/src/include/catalog/pg_type.dat b/src/include/catalog/pg_type.dat
index 62018f063a..4a530ca907 100644
--- a/src/include/catalog/pg_type.dat
+++ b/src/include/catalog/pg_type.dat
@@ -444,7 +444,8 @@
{ oid => '3802', array_type_oid => '3807', descr => 'Binary JSON',
typname => 'jsonb', typlen => '-1', typbyval => 'f', typcategory => 'U',
typinput => 'jsonb_in', typoutput => 'jsonb_out', typreceive => 'jsonb_recv',
- typsend => 'jsonb_send', typalign => 'i', typstorage => 'x' },
+ typsend => 'jsonb_send', typalign => 'i', typstorage => 'x',
+ typsubscript => 'jsonb_subscript_handler' },
{ oid => '4072', array_type_oid => '4073', descr => 'JSON path',
typname => 'jsonpath', typlen => '-1', typbyval => 'f', typcategory => 'U',
typinput => 'jsonpath_in', typoutput => 'jsonpath_out',
diff --git a/src/include/utils/jsonb.h b/src/include/utils/jsonb.h
index 5860011693..64f1ccbf77 100644
--- a/src/include/utils/jsonb.h
+++ b/src/include/utils/jsonb.h
@@ -392,6 +392,7 @@ extern JsonbValue *pushJsonbValue(JsonbParseState **pstate,
extern JsonbIterator *JsonbIteratorInit(JsonbContainer *container);
extern JsonbIteratorToken JsonbIteratorNext(JsonbIterator **it, JsonbValue *val,
bool skipNested);
+extern JsonbValue *JsonbToJsonbValue(Jsonb *jsonb);
extern Jsonb *JsonbValueToJsonb(JsonbValue *val);
extern bool JsonbDeepContains(JsonbIterator **val,
JsonbIterator **mContained);
@@ -407,5 +408,8 @@ extern char *JsonbToCStringIndent(StringInfo out, JsonbContainer *in,
extern bool JsonbExtractScalar(JsonbContainer *jbc, JsonbValue *res);
extern const char *JsonbTypeName(JsonbValue *jb);
-
+extern Datum jsonb_set_element(Jsonb *jb, Datum *path, int path_len,
+ JsonbValue *newval);
+extern Datum jsonb_get_element(Jsonb *jb, Datum *path, int npath,
+ bool *isnull, bool as_text);
#endif /* __JSONB_H__ */
diff --git a/src/test/regress/expected/jsonb.out b/src/test/regress/expected/jsonb.out
index 1e6c6ef200..bb3f25ec3f 100644
--- a/src/test/regress/expected/jsonb.out
+++ b/src/test/regress/expected/jsonb.out
@@ -4599,7 +4599,7 @@ select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, null_value_treatment => 'use_
{"a": 1, "b": null}
(1 row)
-\pset null
+\pset null ''
-- jsonb_insert
select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"');
jsonb_insert
@@ -4729,6 +4729,276 @@ HINT: Try using the function jsonb_set to replace key value.
select jsonb_insert('{"a": {"b": "value"}}', '{a, b}', '"new_value"', true);
ERROR: cannot replace existing key
HINT: Try using the function jsonb_set to replace key value.
+-- jsonb subscript
+select ('123'::jsonb)['a'];
+ jsonb
+-------
+
+(1 row)
+
+select ('123'::jsonb)[0];
+ jsonb
+-------
+
+(1 row)
+
+select ('123'::jsonb)[NULL];
+ jsonb
+-------
+
+(1 row)
+
+select ('{"a": 1}'::jsonb)['a'];
+ jsonb
+-------
+ 1
+(1 row)
+
+select ('{"a": 1}'::jsonb)[0];
+ jsonb
+-------
+
+(1 row)
+
+select ('{"a": 1}'::jsonb)['not_exist'];
+ jsonb
+-------
+
+(1 row)
+
+select ('{"a": 1}'::jsonb)[NULL];
+ jsonb
+-------
+
+(1 row)
+
+select ('[1, "2", null]'::jsonb)['a'];
+ jsonb
+-------
+
+(1 row)
+
+select ('[1, "2", null]'::jsonb)[0];
+ jsonb
+-------
+ 1
+(1 row)
+
+select ('[1, "2", null]'::jsonb)['1'];
+ jsonb
+-------
+ "2"
+(1 row)
+
+select ('[1, "2", null]'::jsonb)[1.0];
+ERROR: subscript type is not supported
+LINE 1: select ('[1, "2", null]'::jsonb)[1.0];
+ ^
+HINT: Jsonb subscript must be coercet to either integer or text
+select ('[1, "2", null]'::jsonb)[2];
+ jsonb
+-------
+ null
+(1 row)
+
+select ('[1, "2", null]'::jsonb)[3];
+ jsonb
+-------
+
+(1 row)
+
+select ('[1, "2", null]'::jsonb)[-2];
+ jsonb
+-------
+ "2"
+(1 row)
+
+select ('[1, "2", null]'::jsonb)[1]['a'];
+ jsonb
+-------
+
+(1 row)
+
+select ('[1, "2", null]'::jsonb)[1][0];
+ jsonb
+-------
+
+(1 row)
+
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['b'];
+ jsonb
+-------
+ "c"
+(1 row)
+
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d'];
+ jsonb
+-----------
+ [1, 2, 3]
+(1 row)
+
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d'][1];
+ jsonb
+-------
+ 2
+(1 row)
+
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d']['a'];
+ jsonb
+-------
+
+(1 row)
+
+select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1'];
+ jsonb
+---------------
+ {"a2": "aaa"}
+(1 row)
+
+select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2'];
+ jsonb
+-------
+ "aaa"
+(1 row)
+
+select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2']['a3'];
+ jsonb
+-------
+
+(1 row)
+
+select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1'];
+ jsonb
+-----------------------
+ ["aaa", "bbb", "ccc"]
+(1 row)
+
+select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1'][2];
+ jsonb
+-------
+ "ccc"
+(1 row)
+
+-- slices are not supported
+select ('{"a": 1}'::jsonb)['a':'b'];
+ERROR: jsonb subscript does not support slices
+LINE 1: select ('{"a": 1}'::jsonb)['a':'b'];
+ ^
+select ('[1, "2", null]'::jsonb)[1:2];
+ERROR: jsonb subscript does not support slices
+LINE 1: select ('[1, "2", null]'::jsonb)[1:2];
+ ^
+select ('[1, "2", null]'::jsonb)[:2];
+ERROR: jsonb subscript does not support slices
+LINE 1: select ('[1, "2", null]'::jsonb)[:2];
+ ^
+select ('[1, "2", null]'::jsonb)[1:];
+ERROR: jsonb subscript does not support slices
+LINE 1: select ('[1, "2", null]'::jsonb)[1:];
+ ^
+select ('[1, "2", null]'::jsonb)[:];
+ERROR: jsonb subscript does not support slices
+create TEMP TABLE test_jsonb_subscript (
+ id int,
+ test_json jsonb
+);
+insert into test_jsonb_subscript values
+(1, '{}'), -- empty jsonb
+(2, '{"key": "value"}'); -- jsonb with data
+-- update empty jsonb
+update test_jsonb_subscript set test_json['a'] = '1' where id = 1;
+select * from test_jsonb_subscript;
+ id | test_json
+----+------------------
+ 2 | {"key": "value"}
+ 1 | {"a": 1}
+(2 rows)
+
+-- update jsonb with some data
+update test_jsonb_subscript set test_json['a'] = '1' where id = 2;
+select * from test_jsonb_subscript;
+ id | test_json
+----+--------------------------
+ 1 | {"a": 1}
+ 2 | {"a": 1, "key": "value"}
+(2 rows)
+
+-- replace jsonb
+update test_jsonb_subscript set test_json['a'] = '"test"';
+select * from test_jsonb_subscript;
+ id | test_json
+----+-------------------------------
+ 1 | {"a": "test"}
+ 2 | {"a": "test", "key": "value"}
+(2 rows)
+
+-- replace by object
+update test_jsonb_subscript set test_json['a'] = '{"b": 1}'::jsonb;
+select * from test_jsonb_subscript;
+ id | test_json
+----+---------------------------------
+ 1 | {"a": {"b": 1}}
+ 2 | {"a": {"b": 1}, "key": "value"}
+(2 rows)
+
+-- replace by array
+update test_jsonb_subscript set test_json['a'] = '[1, 2, 3]'::jsonb;
+select * from test_jsonb_subscript;
+ id | test_json
+----+----------------------------------
+ 1 | {"a": [1, 2, 3]}
+ 2 | {"a": [1, 2, 3], "key": "value"}
+(2 rows)
+
+-- use jsonb subscription in where clause
+select * from test_jsonb_subscript where test_json['key'] = '"value"';
+ id | test_json
+----+----------------------------------
+ 2 | {"a": [1, 2, 3], "key": "value"}
+(1 row)
+
+select * from test_jsonb_subscript where test_json['key_doesnt_exists'] = '"value"';
+ id | test_json
+----+-----------
+(0 rows)
+
+select * from test_jsonb_subscript where test_json['key'] = '"wrong_value"';
+ id | test_json
+----+-----------
+(0 rows)
+
+-- NULL
+update test_jsonb_subscript set test_json[NULL] = '1';
+ERROR: jsonb subscript in assignment must not be null
+update test_jsonb_subscript set test_json['another_key'] = NULL;
+select * from test_jsonb_subscript;
+ id | test_json
+----+-------------------------------------------------------
+ 1 | {"a": [1, 2, 3], "another_key": null}
+ 2 | {"a": [1, 2, 3], "key": "value", "another_key": null}
+(2 rows)
+
+-- NULL as jsonb source
+insert into test_jsonb_subscript values (3, NULL);
+update test_jsonb_subscript set test_json['a'] = '1' where id = 3;
+select * from test_jsonb_subscript;
+ id | test_json
+----+-------------------------------------------------------
+ 1 | {"a": [1, 2, 3], "another_key": null}
+ 2 | {"a": [1, 2, 3], "key": "value", "another_key": null}
+ 3 | {"a": 1}
+(3 rows)
+
+update test_jsonb_subscript set test_json = NULL where id = 3;
+update test_jsonb_subscript set test_json[0] = '1';
+select * from test_jsonb_subscript;
+ id | test_json
+----+---------------------------------------------------------------
+ 1 | {"0": 1, "a": [1, 2, 3], "another_key": null}
+ 2 | {"0": 1, "a": [1, 2, 3], "key": "value", "another_key": null}
+ 3 | [1]
+(3 rows)
+
-- jsonb to tsvector
select to_tsvector('{"a": "aaa bbb ddd ccc", "b": ["eee fff ggg"], "c": {"d": "hhh iii"}}'::jsonb);
to_tsvector
diff --git a/src/test/regress/sql/jsonb.sql b/src/test/regress/sql/jsonb.sql
index b6409767f6..20aa8fe0e2 100644
--- a/src/test/regress/sql/jsonb.sql
+++ b/src/test/regress/sql/jsonb.sql
@@ -1177,7 +1177,7 @@ select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, null_value_treatment => 'retu
select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, null_value_treatment => 'delete_key') as delete_key;
select jsonb_set_lax('{"a":1,"b":2}', '{b}', null, null_value_treatment => 'use_json_null') as use_json_null;
-\pset null
+\pset null ''
-- jsonb_insert
select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"');
@@ -1208,6 +1208,88 @@ select jsonb_insert('{"a": {"b": "value"}}', '{a, c}', '"new_value"', true);
select jsonb_insert('{"a": {"b": "value"}}', '{a, b}', '"new_value"');
select jsonb_insert('{"a": {"b": "value"}}', '{a, b}', '"new_value"', true);
+-- jsonb subscript
+select ('123'::jsonb)['a'];
+select ('123'::jsonb)[0];
+select ('123'::jsonb)[NULL];
+select ('{"a": 1}'::jsonb)['a'];
+select ('{"a": 1}'::jsonb)[0];
+select ('{"a": 1}'::jsonb)['not_exist'];
+select ('{"a": 1}'::jsonb)[NULL];
+select ('[1, "2", null]'::jsonb)['a'];
+select ('[1, "2", null]'::jsonb)[0];
+select ('[1, "2", null]'::jsonb)['1'];
+select ('[1, "2", null]'::jsonb)[1.0];
+select ('[1, "2", null]'::jsonb)[2];
+select ('[1, "2", null]'::jsonb)[3];
+select ('[1, "2", null]'::jsonb)[-2];
+select ('[1, "2", null]'::jsonb)[1]['a'];
+select ('[1, "2", null]'::jsonb)[1][0];
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['b'];
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d'];
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d'][1];
+select ('{"a": 1, "b": "c", "d": [1, 2, 3]}'::jsonb)['d']['a'];
+select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1'];
+select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2'];
+select ('{"a": {"a1": {"a2": "aaa"}}, "b": "bbb", "c": "ccc"}'::jsonb)['a']['a1']['a2']['a3'];
+select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1'];
+select ('{"a": ["a1", {"b1": ["aaa", "bbb", "ccc"]}], "b": "bb"}'::jsonb)['a'][1]['b1'][2];
+
+-- slices are not supported
+select ('{"a": 1}'::jsonb)['a':'b'];
+select ('[1, "2", null]'::jsonb)[1:2];
+select ('[1, "2", null]'::jsonb)[:2];
+select ('[1, "2", null]'::jsonb)[1:];
+select ('[1, "2", null]'::jsonb)[:];
+
+create TEMP TABLE test_jsonb_subscript (
+ id int,
+ test_json jsonb
+);
+
+insert into test_jsonb_subscript values
+(1, '{}'), -- empty jsonb
+(2, '{"key": "value"}'); -- jsonb with data
+
+-- update empty jsonb
+update test_jsonb_subscript set test_json['a'] = '1' where id = 1;
+select * from test_jsonb_subscript;
+
+-- update jsonb with some data
+update test_jsonb_subscript set test_json['a'] = '1' where id = 2;
+select * from test_jsonb_subscript;
+
+-- replace jsonb
+update test_jsonb_subscript set test_json['a'] = '"test"';
+select * from test_jsonb_subscript;
+
+-- replace by object
+update test_jsonb_subscript set test_json['a'] = '{"b": 1}'::jsonb;
+select * from test_jsonb_subscript;
+
+-- replace by array
+update test_jsonb_subscript set test_json['a'] = '[1, 2, 3]'::jsonb;
+select * from test_jsonb_subscript;
+
+-- use jsonb subscription in where clause
+select * from test_jsonb_subscript where test_json['key'] = '"value"';
+select * from test_jsonb_subscript where test_json['key_doesnt_exists'] = '"value"';
+select * from test_jsonb_subscript where test_json['key'] = '"wrong_value"';
+
+-- NULL
+update test_jsonb_subscript set test_json[NULL] = '1';
+update test_jsonb_subscript set test_json['another_key'] = NULL;
+select * from test_jsonb_subscript;
+
+-- NULL as jsonb source
+insert into test_jsonb_subscript values (3, NULL);
+update test_jsonb_subscript set test_json['a'] = '1' where id = 3;
+select * from test_jsonb_subscript;
+
+update test_jsonb_subscript set test_json = NULL where id = 3;
+update test_jsonb_subscript set test_json[0] = '1';
+select * from test_jsonb_subscript;
+
-- jsonb to tsvector
select to_tsvector('{"a": "aaa bbb ddd ccc", "b": ["eee fff ggg"], "c": {"d": "hhh iii"}}'::jsonb);
--
2.21.0
--2lozexjxkmbehzdg
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v42-0002-Filling-gaps-in-jsonb.patch"
^ permalink raw reply [nested|flat] 17+ messages in thread
* Allow WindowFuncs prosupport function to use more optimal WindowClause options
@ 2022-10-12 02:40 David Rowley <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: David Rowley @ 2022-10-12 02:40 UTC (permalink / raw)
To: PostgreSQL Developers <[email protected]>; Erwin Brandstetter <[email protected]>
Over on [1], Erwin mentions that row_number() over (ORDER BY ... ROWS
UNBOUNDED PRECEDING) is substantially faster than the default RANGE
UNBOUNDED PRECEDING WindowClause options. The difference between
these options are that nodeWindowAgg.c checks for peer rows for RANGE
but not for ROWS. That would make a difference for many of our
built-in window and aggregate functions, but row_number() does not
really care.
To quantify the performance difference, take the following example:
create table ab (a int, b int) ;
insert into ab
select x,y from generate_series(1,1000)x,generate_series(1,1000)y;
create index on ab(a,b);
-- range unbounded (the default)
explain (analyze, costs off, timing off)
select a,b from (select a,b,row_number() over (partition by a order by
a range unbounded preceding) rn from ab) ab where rn <= 1;
QUERY PLAN
---------------------------------------------------------------------------------------
Subquery Scan on ab (actual rows=1000 loops=1)
-> WindowAgg (actual rows=1000 loops=1)
Run Condition: (row_number() OVER (?) <= 1)
-> Index Only Scan using ab_a_b_idx on ab ab_1 (actual
rows=1000000 loops=1)
Heap Fetches: 1000000
Planning Time: 0.091 ms
Execution Time: 336.172 ms
(7 rows)
If that were switched to use ROWS UNBOUNDED PRECEDING then the
executor would not have to check for peer rows in the window frame.
explain (analyze, costs off, timing off)
select a,b from (select a,b,row_number() over (partition by a order by
a rows unbounded preceding) rn from ab) ab where rn <= 1;
QUERY PLAN
---------------------------------------------------------------------------------------
Subquery Scan on ab (actual rows=1000 loops=1)
-> WindowAgg (actual rows=1000 loops=1)
Run Condition: (row_number() OVER (?) <= 1)
-> Index Only Scan using ab_a_b_idx on ab ab_1 (actual
rows=1000000 loops=1)
Heap Fetches: 0
Planning Time: 0.178 ms
Execution Time: 75.101 ms
(7 rows)
Time: 75.673 ms (447% faster)
You can see that this executes quite a bit more quickly. As Erwin
pointed out to me (off-list), this along with the monotonic window
function optimisation that was added in PG15 the performance of this
gets close to DISTINCT ON.
explain (analyze, costs off, timing off)
select distinct on (a) a,b from ab order by a,b;
QUERY PLAN
----------------------------------------------------------------------------
Unique (actual rows=1000 loops=1)
-> Index Only Scan using ab_a_b_idx on ab (actual rows=1000000 loops=1)
Heap Fetches: 0
Planning Time: 0.071 ms
Execution Time: 77.988 ms
(5 rows)
I've not really done any analysis into which other window functions
can use this optimisation. The attached only adds support to
row_number()'s support function and only converts exactly "RANGE
UNBOUNDED PRECEDING AND CURRENT ROW" into "ROW UNBOUNDED PRECEDING AND
CURRENT ROW". That might need to be relaxed a little, but I've done
no analysis to find that out. Erwin mentioned to me that he's not
currently in a position to produce a patch for this, so here's the
patch. I'm hoping the existence of this might coax Erwin into doing
some analysis into what other window functions we can support and what
other frame options can be optimised.
[1] https://postgr.es/m/CAGHENJ7LBBszxS+SkWWFVnBmOT2oVsBhDMB1DFrgerCeYa_DyA@mail.gmail.com
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 5d0fd6e072..e55e0e49a5 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -38,6 +38,7 @@
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "nodes/supportnodes.h"
#ifdef OPTIMIZER_DEBUG
#include "nodes/print.h"
#endif
@@ -207,6 +208,8 @@ static PathTarget *make_partial_grouping_target(PlannerInfo *root,
PathTarget *grouping_target,
Node *havingQual);
static List *postprocess_setop_tlist(List *new_tlist, List *orig_tlist);
+static void optimize_window_clause_frameoptions(PlannerInfo *root,
+ WindowFuncLists *wflists);
static List *select_active_windows(PlannerInfo *root, WindowFuncLists *wflists);
static PathTarget *make_window_input_target(PlannerInfo *root,
PathTarget *final_target,
@@ -1422,7 +1425,15 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
wflists = find_window_functions((Node *) root->processed_tlist,
list_length(parse->windowClause));
if (wflists->numWindowFuncs > 0)
+ {
+ /*
+ * See if we can find more optimal version of each
+ * WindowClause's frameOptions.
+ */
+ optimize_window_clause_frameoptions(root, wflists);
+
activeWindows = select_active_windows(root, wflists);
+ }
else
parse->hasWindowFuncs = false;
}
@@ -5391,6 +5402,83 @@ postprocess_setop_tlist(List *new_tlist, List *orig_tlist)
return new_tlist;
}
+/*
+ * optimize_window_clause_frameoptions
+ * Call each WindowFunc's prosupport function to see if we're able to
+ * make any adjustments to any of the WindowClause's frameOptions so that
+ * the executor can execute the window functions in a more optimal way.
+ */
+static void
+optimize_window_clause_frameoptions(PlannerInfo *root,
+ WindowFuncLists *wflists)
+{
+ List *windowClause = root->parse->windowClause;
+ ListCell *lc;
+
+ foreach(lc, windowClause)
+ {
+ WindowClause *wc = lfirst_node(WindowClause, lc);
+ ListCell *lc2;
+ int optimizedFrameOptions = 0;
+
+ Assert(wc->winref <= wflists->maxWinRef);
+
+ /* skip any WindowClauses that have no WindowFuncs */
+ if (wflists->windowFuncs[wc->winref] == NIL)
+ continue;
+
+ foreach(lc2, wflists->windowFuncs[wc->winref])
+ {
+ SupportRequestWFuncOptimizeFrameOpts req;
+ SupportRequestWFuncOptimizeFrameOpts *res;
+ WindowFunc *wfunc = lfirst_node(WindowFunc, lc2);
+ Oid prosupport;
+
+ prosupport = get_func_support(wfunc->winfnoid);
+
+ /* Check if there's a support function for 'wfunc' */
+ if (!OidIsValid(prosupport))
+ break; /* can't optimize this WindowClause */
+
+ req.type = T_SupportRequestWFuncOptimizeFrameOpts;
+ req.window_clause = wc;
+ req.window_func = wfunc;
+ req.frameOptions = wc->frameOptions;
+
+ /* call the support function */
+ res = (SupportRequestWFuncOptimizeFrameOpts *)
+ DatumGetPointer(OidFunctionCall1(prosupport,
+ PointerGetDatum(&req)));
+
+ /*
+ * Skip to next WindowClause if the support function does not
+ * support this request type.
+ */
+ if (res == NULL)
+ break;
+
+ /*
+ * Save these frameOptions for the first WindowFunc for this
+ * WindowClause.
+ */
+ if (foreach_current_index(lc2) == 0)
+ optimizedFrameOptions = res->frameOptions;
+
+ /*
+ * On subsequent WindowFuncs, if the frameOptions are not the same
+ * then we're unable to optimize the frameOptions for this
+ * WindowClause.
+ */
+ else if (optimizedFrameOptions != res->frameOptions)
+ break; /* skip to the next WindowClause, if any */
+ }
+
+ /* adjust the frameOptions if all WindowFunc's agree that it's ok */
+ if (lc2 == NULL)
+ wc->frameOptions = optimizedFrameOptions;
+ }
+}
+
/*
* select_active_windows
* Create a list of the "active" window clauses (ie, those referenced
diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c
index 596564fa15..8a85204080 100644
--- a/src/backend/utils/adt/windowfuncs.c
+++ b/src/backend/utils/adt/windowfuncs.c
@@ -107,6 +107,30 @@ window_row_number_support(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(req);
}
+ if (IsA(rawreq, SupportRequestWFuncOptimizeFrameOpts))
+ {
+ SupportRequestWFuncOptimizeFrameOpts *req = (SupportRequestWFuncOptimizeFrameOpts *) rawreq;
+
+ /*
+ * row_number() does not care about RANGE UNBOUNDED PRECEDING vs
+ * ROWS UNBOUNDED PRECEDING. The latter will execute more efficiently
+ * due to nodeWindowAgg.c not having to check if prior rows are peers
+ * of the current row.
+ *
+ * XXX what to do about this FRAMEOPTION_NONDEFAULT option? Are there
+ * any other variations that can be optimized?
+ */
+ if (req->frameOptions == (FRAMEOPTION_NONDEFAULT | FRAMEOPTION_RANGE |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW))
+ req->frameOptions = (FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
+
PG_RETURN_POINTER(NULL);
}
diff --git a/src/include/nodes/supportnodes.h b/src/include/nodes/supportnodes.h
index 9fcbc39949..7be75b06b8 100644
--- a/src/include/nodes/supportnodes.h
+++ b/src/include/nodes/supportnodes.h
@@ -299,4 +299,43 @@ typedef struct SupportRequestWFuncMonotonic
MonotonicFunction monotonic;
} SupportRequestWFuncMonotonic;
+/*
+ * Some WindowFunc behavior might not be affected by certain variations in
+ * the WindowClause's frameOptions. For example, row_number() behaves the
+ * same way if it's called with "OVER (RANGE UNBOUNDED PRECEDING)" or if it's
+ * called with "OVER (ROWS UNBOUNDED PRECEDING)", the latter does not need to
+ * check if prior rows are peers of the current row, so should execute much
+ * more quickly than the RANGE version.
+ *
+ * Here we allow a WindowFunc's support function to output the most optimal
+ * version of window_clause.frameOptions for this particular window function.
+ * The support function is responsible for ensuring the optimized version of
+ * the frameOptions doesn't affect the result of the window function. The
+ * planner is responsible for only changing the frame options when all
+ * WindowFuncs using this particular WindowClause agree on what the optimized
+ * version of the frame options are. If a particular WindowFunc being used
+ * does not have a support function then the planner will not make any changes
+ * to the WindowClause's frameOptions.
+ *
+ * 'window_func' and 'window_clause' are set by the planner before calling the
+ * support function so that the support function has these fields available to
+ * allow the support function to lookup details of how the WindowFunc is being
+ * used.
+ *
+ * 'frameOptions' is set by the planner to WindowClause.frameOptions. The
+ * support should only adjust this if optimizations are possible.
+ */
+typedef struct SupportRequestWFuncOptimizeFrameOpts
+{
+ NodeTag type;
+
+ /* Input fields: */
+ WindowFunc *window_func; /* Pointer to the window function data */
+ struct WindowClause *window_clause; /* Pointer to the window clause data */
+
+ /* Input/Output fields: */
+ int frameOptions; /* New frameOptions, or left untouched if no
+ * optimization is possible. */
+} SupportRequestWFuncOptimizeFrameOpts;
+
#endif /* SUPPORTNODES_H */
Attachments:
[text/plain] optimize_row_numbers_frameoptions_when_possible.patch (7.3K, ../../CAApHDvohAKEtTXxq7Pc-ic2dKT8oZfbRKeEJP64M0B6+S88z+A@mail.gmail.com/2-optimize_row_numbers_frameoptions_when_possible.patch)
download | inline diff:
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 5d0fd6e072..e55e0e49a5 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -38,6 +38,7 @@
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "nodes/supportnodes.h"
#ifdef OPTIMIZER_DEBUG
#include "nodes/print.h"
#endif
@@ -207,6 +208,8 @@ static PathTarget *make_partial_grouping_target(PlannerInfo *root,
PathTarget *grouping_target,
Node *havingQual);
static List *postprocess_setop_tlist(List *new_tlist, List *orig_tlist);
+static void optimize_window_clause_frameoptions(PlannerInfo *root,
+ WindowFuncLists *wflists);
static List *select_active_windows(PlannerInfo *root, WindowFuncLists *wflists);
static PathTarget *make_window_input_target(PlannerInfo *root,
PathTarget *final_target,
@@ -1422,7 +1425,15 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
wflists = find_window_functions((Node *) root->processed_tlist,
list_length(parse->windowClause));
if (wflists->numWindowFuncs > 0)
+ {
+ /*
+ * See if we can find more optimal version of each
+ * WindowClause's frameOptions.
+ */
+ optimize_window_clause_frameoptions(root, wflists);
+
activeWindows = select_active_windows(root, wflists);
+ }
else
parse->hasWindowFuncs = false;
}
@@ -5391,6 +5402,83 @@ postprocess_setop_tlist(List *new_tlist, List *orig_tlist)
return new_tlist;
}
+/*
+ * optimize_window_clause_frameoptions
+ * Call each WindowFunc's prosupport function to see if we're able to
+ * make any adjustments to any of the WindowClause's frameOptions so that
+ * the executor can execute the window functions in a more optimal way.
+ */
+static void
+optimize_window_clause_frameoptions(PlannerInfo *root,
+ WindowFuncLists *wflists)
+{
+ List *windowClause = root->parse->windowClause;
+ ListCell *lc;
+
+ foreach(lc, windowClause)
+ {
+ WindowClause *wc = lfirst_node(WindowClause, lc);
+ ListCell *lc2;
+ int optimizedFrameOptions = 0;
+
+ Assert(wc->winref <= wflists->maxWinRef);
+
+ /* skip any WindowClauses that have no WindowFuncs */
+ if (wflists->windowFuncs[wc->winref] == NIL)
+ continue;
+
+ foreach(lc2, wflists->windowFuncs[wc->winref])
+ {
+ SupportRequestWFuncOptimizeFrameOpts req;
+ SupportRequestWFuncOptimizeFrameOpts *res;
+ WindowFunc *wfunc = lfirst_node(WindowFunc, lc2);
+ Oid prosupport;
+
+ prosupport = get_func_support(wfunc->winfnoid);
+
+ /* Check if there's a support function for 'wfunc' */
+ if (!OidIsValid(prosupport))
+ break; /* can't optimize this WindowClause */
+
+ req.type = T_SupportRequestWFuncOptimizeFrameOpts;
+ req.window_clause = wc;
+ req.window_func = wfunc;
+ req.frameOptions = wc->frameOptions;
+
+ /* call the support function */
+ res = (SupportRequestWFuncOptimizeFrameOpts *)
+ DatumGetPointer(OidFunctionCall1(prosupport,
+ PointerGetDatum(&req)));
+
+ /*
+ * Skip to next WindowClause if the support function does not
+ * support this request type.
+ */
+ if (res == NULL)
+ break;
+
+ /*
+ * Save these frameOptions for the first WindowFunc for this
+ * WindowClause.
+ */
+ if (foreach_current_index(lc2) == 0)
+ optimizedFrameOptions = res->frameOptions;
+
+ /*
+ * On subsequent WindowFuncs, if the frameOptions are not the same
+ * then we're unable to optimize the frameOptions for this
+ * WindowClause.
+ */
+ else if (optimizedFrameOptions != res->frameOptions)
+ break; /* skip to the next WindowClause, if any */
+ }
+
+ /* adjust the frameOptions if all WindowFunc's agree that it's ok */
+ if (lc2 == NULL)
+ wc->frameOptions = optimizedFrameOptions;
+ }
+}
+
/*
* select_active_windows
* Create a list of the "active" window clauses (ie, those referenced
diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c
index 596564fa15..8a85204080 100644
--- a/src/backend/utils/adt/windowfuncs.c
+++ b/src/backend/utils/adt/windowfuncs.c
@@ -107,6 +107,30 @@ window_row_number_support(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(req);
}
+ if (IsA(rawreq, SupportRequestWFuncOptimizeFrameOpts))
+ {
+ SupportRequestWFuncOptimizeFrameOpts *req = (SupportRequestWFuncOptimizeFrameOpts *) rawreq;
+
+ /*
+ * row_number() does not care about RANGE UNBOUNDED PRECEDING vs
+ * ROWS UNBOUNDED PRECEDING. The latter will execute more efficiently
+ * due to nodeWindowAgg.c not having to check if prior rows are peers
+ * of the current row.
+ *
+ * XXX what to do about this FRAMEOPTION_NONDEFAULT option? Are there
+ * any other variations that can be optimized?
+ */
+ if (req->frameOptions == (FRAMEOPTION_NONDEFAULT | FRAMEOPTION_RANGE |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW))
+ req->frameOptions = (FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
+
PG_RETURN_POINTER(NULL);
}
diff --git a/src/include/nodes/supportnodes.h b/src/include/nodes/supportnodes.h
index 9fcbc39949..7be75b06b8 100644
--- a/src/include/nodes/supportnodes.h
+++ b/src/include/nodes/supportnodes.h
@@ -299,4 +299,43 @@ typedef struct SupportRequestWFuncMonotonic
MonotonicFunction monotonic;
} SupportRequestWFuncMonotonic;
+/*
+ * Some WindowFunc behavior might not be affected by certain variations in
+ * the WindowClause's frameOptions. For example, row_number() behaves the
+ * same way if it's called with "OVER (RANGE UNBOUNDED PRECEDING)" or if it's
+ * called with "OVER (ROWS UNBOUNDED PRECEDING)", the latter does not need to
+ * check if prior rows are peers of the current row, so should execute much
+ * more quickly than the RANGE version.
+ *
+ * Here we allow a WindowFunc's support function to output the most optimal
+ * version of window_clause.frameOptions for this particular window function.
+ * The support function is responsible for ensuring the optimized version of
+ * the frameOptions doesn't affect the result of the window function. The
+ * planner is responsible for only changing the frame options when all
+ * WindowFuncs using this particular WindowClause agree on what the optimized
+ * version of the frame options are. If a particular WindowFunc being used
+ * does not have a support function then the planner will not make any changes
+ * to the WindowClause's frameOptions.
+ *
+ * 'window_func' and 'window_clause' are set by the planner before calling the
+ * support function so that the support function has these fields available to
+ * allow the support function to lookup details of how the WindowFunc is being
+ * used.
+ *
+ * 'frameOptions' is set by the planner to WindowClause.frameOptions. The
+ * support should only adjust this if optimizations are possible.
+ */
+typedef struct SupportRequestWFuncOptimizeFrameOpts
+{
+ NodeTag type;
+
+ /* Input fields: */
+ WindowFunc *window_func; /* Pointer to the window function data */
+ struct WindowClause *window_clause; /* Pointer to the window clause data */
+
+ /* Input/Output fields: */
+ int frameOptions; /* New frameOptions, or left untouched if no
+ * optimization is possible. */
+} SupportRequestWFuncOptimizeFrameOpts;
+
#endif /* SUPPORTNODES_H */
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options
@ 2022-10-12 03:33 Vik Fearing <[email protected]>
parent: David Rowley <[email protected]>
0 siblings, 2 replies; 17+ messages in thread
From: Vik Fearing @ 2022-10-12 03:33 UTC (permalink / raw)
To: David Rowley <[email protected]>; PostgreSQL Developers <[email protected]>; Erwin Brandstetter <[email protected]>
On 10/12/22 04:40, David Rowley wrote:
> I've not really done any analysis into which other window functions
> can use this optimisation. The attached only adds support to
> row_number()'s support function and only converts exactly "RANGE
> UNBOUNDED PRECEDING AND CURRENT ROW" into "ROW UNBOUNDED PRECEDING AND
> CURRENT ROW". That might need to be relaxed a little, but I've done
> no analysis to find that out.
Per spec, the ROW_NUMBER() window function is not even allowed to have a
frame specified.
b) The window framing clause of WDX shall not be present.
Also, the specification for ROW_NUMBER() is:
f) ROW_NUMBER() OVER WNS is equivalent to the <window function>:
COUNT (*) OVER (WNS1 ROWS UNBOUNDED PRECEDING)
So I don't think we need to test for anything at all and can
indiscriminately add or replace the frame with ROWS UNBOUNDED PRECEDING.
--
Vik Fearing
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options
@ 2022-10-12 04:03 Erwin Brandstetter <[email protected]>
parent: Vik Fearing <[email protected]>
1 sibling, 0 replies; 17+ messages in thread
From: Erwin Brandstetter @ 2022-10-12 04:03 UTC (permalink / raw)
To: Vik Fearing <[email protected]>; +Cc: David Rowley <[email protected]>; PostgreSQL Developers <[email protected]>
On Wed, 12 Oct 2022 at 05:33, Vik Fearing <[email protected]> wrote:
> On 10/12/22 04:40, David Rowley wrote:
> > I've not really done any analysis into which other window functions
> > can use this optimisation. The attached only adds support to
> > row_number()'s support function and only converts exactly "RANGE
> > UNBOUNDED PRECEDING AND CURRENT ROW" into "ROW UNBOUNDED PRECEDING AND
> > CURRENT ROW". That might need to be relaxed a little, but I've done
> > no analysis to find that out.
>
> Per spec, the ROW_NUMBER() window function is not even allowed to have a
> frame specified.
>
> b) The window framing clause of WDX shall not be present.
>
> Also, the specification for ROW_NUMBER() is:
>
> f) ROW_NUMBER() OVER WNS is equivalent to the <window function>:
>
> COUNT (*) OVER (WNS1 ROWS UNBOUNDED PRECEDING)
>
>
> So I don't think we need to test for anything at all and can
> indiscriminately add or replace the frame with ROWS UNBOUNDED PRECEDING.
>
>
To back this up:
SQL Server returns an error right away if you try to add a window frame
https://dbfiddle.uk/SplT-F3E
> Msg 10752 Level 15 State 3 Line 1
> The function 'row_number' may not have a window frame.
And Oracle reports a syntax error:
https://dbfiddle.uk/l0Yk8Lw5
row_number() is defined without a "windowing clause" (in Oravle's
nomenclature)
https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions001.htm#i81407
https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions144.htm#i86310
Allowing the same in Postgres (and defaulting to RANGE mode) seems like (a)
genuine bug(s) after all.
Regards
Erwin
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options
@ 2022-10-13 00:34 David Rowley <[email protected]>
parent: Vik Fearing <[email protected]>
1 sibling, 3 replies; 17+ messages in thread
From: David Rowley @ 2022-10-13 00:34 UTC (permalink / raw)
To: Vik Fearing <[email protected]>; +Cc: PostgreSQL Developers <[email protected]>; Erwin Brandstetter <[email protected]>
On Wed, 12 Oct 2022 at 16:33, Vik Fearing <[email protected]> wrote:
> Per spec, the ROW_NUMBER() window function is not even allowed to have a
> frame specified.
>
> b) The window framing clause of WDX shall not be present.
>
> Also, the specification for ROW_NUMBER() is:
>
> f) ROW_NUMBER() OVER WNS is equivalent to the <window function>:
>
> COUNT (*) OVER (WNS1 ROWS UNBOUNDED PRECEDING)
>
>
> So I don't think we need to test for anything at all and can
> indiscriminately add or replace the frame with ROWS UNBOUNDED PRECEDING.
Thanks for digging that out.
Just above that I see:
RANK() OVER WNS is equivalent to:
( COUNT (*) OVER (WNS1 RANGE UNBOUNDED PRECEDING)
- COUNT (*) OVER (WNS1 RANGE CURRENT ROW) + 1 )
and
DENSE_RANK() OVER WNS is equivalent to the <window function>:
COUNT (DISTINCT ROW ( VE1, ..., VEN ) )
OVER (WNS1 RANGE UNBOUNDED PRECEDING)
So it looks like the same can be done for rank() and dense_rank() too.
I've added support for those in the attached.
This also got me thinking that maybe we should be a bit more generic
with the support function node tag name. After looking at the
nodeWindowAgg.c code for a while, I wondered if we might want to add
some optimisations in the future that makes WindowAgg not bother
storing tuples for row_number(), rank() and dense_rank(). That might
save a bit of overhead from the tuple store. I imagined that we'd
want to allow the expansion of this support request so that the
support function could let the planner know if any tuples will be
accessed by the window function or not. The
SupportRequestWFuncOptimizeFrameOpts name didn't seem very fitting for
that so I adjusted it to become SupportRequestOptimizeWindowClause
instead.
The updated patch is attached.
David
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 5d0fd6e072..74a8bafd8b 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -38,6 +38,7 @@
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "nodes/supportnodes.h"
#ifdef OPTIMIZER_DEBUG
#include "nodes/print.h"
#endif
@@ -207,6 +208,8 @@ static PathTarget *make_partial_grouping_target(PlannerInfo *root,
PathTarget *grouping_target,
Node *havingQual);
static List *postprocess_setop_tlist(List *new_tlist, List *orig_tlist);
+static void optimize_window_clauses(PlannerInfo *root,
+ WindowFuncLists *wflists);
static List *select_active_windows(PlannerInfo *root, WindowFuncLists *wflists);
static PathTarget *make_window_input_target(PlannerInfo *root,
PathTarget *final_target,
@@ -1422,7 +1425,16 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
wflists = find_window_functions((Node *) root->processed_tlist,
list_length(parse->windowClause));
if (wflists->numWindowFuncs > 0)
+ {
+ /*
+ * See if any modifications can be made to each WindowClause
+ * to allow the executor to execute the WindowFuncs more
+ * quickly.
+ */
+ optimize_window_clauses(root, wflists);
+
activeWindows = select_active_windows(root, wflists);
+ }
else
parse->hasWindowFuncs = false;
}
@@ -5391,6 +5403,85 @@ postprocess_setop_tlist(List *new_tlist, List *orig_tlist)
return new_tlist;
}
+/*
+ * optimize_window_clauses
+ * Call each WindowFunc's prosupport function to see if we're able to
+ * make any adjustments to any of the WindowClause's so that the executor
+ * can execute the window functions in a more optimal way.
+ *
+ * Currently we only allow adjustments to the WindowClause's frameOptions. We
+ * may allow more things to be done here in the future.
+ */
+static void
+optimize_window_clauses(PlannerInfo *root, WindowFuncLists *wflists)
+{
+ List *windowClause = root->parse->windowClause;
+ ListCell *lc;
+
+ foreach(lc, windowClause)
+ {
+ WindowClause *wc = lfirst_node(WindowClause, lc);
+ ListCell *lc2;
+ int optimizedFrameOptions = 0;
+
+ Assert(wc->winref <= wflists->maxWinRef);
+
+ /* skip any WindowClauses that have no WindowFuncs */
+ if (wflists->windowFuncs[wc->winref] == NIL)
+ continue;
+
+ foreach(lc2, wflists->windowFuncs[wc->winref])
+ {
+ SupportRequestOptimizeWindowClause req;
+ SupportRequestOptimizeWindowClause *res;
+ WindowFunc *wfunc = lfirst_node(WindowFunc, lc2);
+ Oid prosupport;
+
+ prosupport = get_func_support(wfunc->winfnoid);
+
+ /* Check if there's a support function for 'wfunc' */
+ if (!OidIsValid(prosupport))
+ break; /* can't optimize this WindowClause */
+
+ req.type = T_SupportRequestOptimizeWindowClause;
+ req.window_clause = wc;
+ req.window_func = wfunc;
+ req.frameOptions = wc->frameOptions;
+
+ /* call the support function */
+ res = (SupportRequestOptimizeWindowClause *)
+ DatumGetPointer(OidFunctionCall1(prosupport,
+ PointerGetDatum(&req)));
+
+ /*
+ * Skip to next WindowClause if the support function does not
+ * support this request type.
+ */
+ if (res == NULL)
+ break;
+
+ /*
+ * Save these frameOptions for the first WindowFunc for this
+ * WindowClause.
+ */
+ if (foreach_current_index(lc2) == 0)
+ optimizedFrameOptions = res->frameOptions;
+
+ /*
+ * On subsequent WindowFuncs, if the frameOptions are not the same
+ * then we're unable to optimize the frameOptions for this
+ * WindowClause.
+ */
+ else if (optimizedFrameOptions != res->frameOptions)
+ break; /* skip to the next WindowClause, if any */
+ }
+
+ /* adjust the frameOptions if all WindowFunc's agree that it's ok */
+ if (lc2 == NULL)
+ wc->frameOptions = optimizedFrameOptions;
+ }
+}
+
/*
* select_active_windows
* Create a list of the "active" window clauses (ie, those referenced
diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c
index 596564fa15..3bf90813cf 100644
--- a/src/backend/utils/adt/windowfuncs.c
+++ b/src/backend/utils/adt/windowfuncs.c
@@ -107,6 +107,23 @@ window_row_number_support(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(req);
}
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * The frame options can always become "ROWS BETWEEN UNBOUNDED
+ * PRECEDING AND CURRENT ROW". row_number() always just increments
+ * by 1 with each row in the partition. Using ROWS instead of RANGE
+ * saves effort during execution.
+ */
+ req->frameOptions = (FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
PG_RETURN_POINTER(NULL);
}
@@ -149,6 +166,26 @@ window_rank_support(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(req);
}
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * rank() is coded in such a way that it returns "(COUNT (*) OVER
+ *(<opt> RANGE UNBOUNDED PRECEDING) - COUNT (*) OVER (<opt> RANGE
+ * CURRENT ROW) + 1 )" regardless of the frame options. We'll set the
+ * frame options to "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW"
+ * so they agree with what window_row_number_support() optimized the
+ * frame options to be. Using ROWS instead of RANGE saves effort
+ * during execution.
+ */
+ req->frameOptions = (FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
PG_RETURN_POINTER(NULL);
}
@@ -190,6 +227,22 @@ window_dense_rank_support(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(req);
}
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * Like window_rank(), window_dense_rank() is also unaffected by the
+ * frame options. Here we just set them to match what's done for the
+ * row_number() and rank() window functions.
+ */
+ req->frameOptions = (FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
PG_RETURN_POINTER(NULL);
}
diff --git a/src/include/nodes/supportnodes.h b/src/include/nodes/supportnodes.h
index 9fcbc39949..b446125b2b 100644
--- a/src/include/nodes/supportnodes.h
+++ b/src/include/nodes/supportnodes.h
@@ -299,4 +299,48 @@ typedef struct SupportRequestWFuncMonotonic
MonotonicFunction monotonic;
} SupportRequestWFuncMonotonic;
+/*
+ * Some WindowFunc behavior might not be affected by certain variations in
+ * the WindowClause's frameOptions. For example, row_number() is coded in
+ * such a way that the frame options don't change the returned row number.
+ * nodeWindowAgg.c will have less work to do if the ROWS option is used
+ * instead of the RANGE option as no check needs to be done for peer rows.
+ * Since RANGE is included in the default frame options, window functions
+ * such as row_number() might want to change that to ROW.
+ *
+ * Here we allow a WindowFunc's support function to determine which, if
+ * anything, can be changed about the WindowClause which the WindowFunc
+ * belongs to. Currently only the frameOptions can be modified. However,
+ * we may want to allow more optimizations in the future.
+ *
+ * The support function is responsible for ensuring the optimized version of
+ * the frameOptions doesn't affect the result of the window function. The
+ * planner is responsible for only changing the frame options when all
+ * WindowFuncs using this particular WindowClause agree on what the optimized
+ * version of the frameOptions are. If a particular WindowFunc being used
+ * does not have a support function then the planner will not make any changes
+ * to the WindowClause's frameOptions.
+ *
+ * 'window_func' and 'window_clause' are set by the planner before calling the
+ * support function so that the support function has these fields available.
+ * These may be required in order to determine which optimizations are
+ * possible.
+ *
+ * 'frameOptions' is set by the planner to WindowClause.frameOptions. The
+ * support function must only adjust this if optimizations are possible for
+ * the given WindowFunc.
+ */
+typedef struct SupportRequestOptimizeWindowClause
+{
+ NodeTag type;
+
+ /* Input fields: */
+ WindowFunc *window_func; /* Pointer to the window function data */
+ struct WindowClause *window_clause; /* Pointer to the window clause data */
+
+ /* Input/Output fields: */
+ int frameOptions; /* New frameOptions, or left untouched if no
+ * optimizations are possible. */
+} SupportRequestOptimizeWindowClause;
+
#endif /* SUPPORTNODES_H */
Attachments:
[text/plain] v2_optimize_row_numbers_frameoptions_when_possible.patch (8.9K, ../../CAApHDvrnWEUrUFn_LtGqKjNy-jtq75cZ0Z6xc7w5Nq+LGKjkdg@mail.gmail.com/2-v2_optimize_row_numbers_frameoptions_when_possible.patch)
download | inline diff:
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 5d0fd6e072..74a8bafd8b 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -38,6 +38,7 @@
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "nodes/supportnodes.h"
#ifdef OPTIMIZER_DEBUG
#include "nodes/print.h"
#endif
@@ -207,6 +208,8 @@ static PathTarget *make_partial_grouping_target(PlannerInfo *root,
PathTarget *grouping_target,
Node *havingQual);
static List *postprocess_setop_tlist(List *new_tlist, List *orig_tlist);
+static void optimize_window_clauses(PlannerInfo *root,
+ WindowFuncLists *wflists);
static List *select_active_windows(PlannerInfo *root, WindowFuncLists *wflists);
static PathTarget *make_window_input_target(PlannerInfo *root,
PathTarget *final_target,
@@ -1422,7 +1425,16 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
wflists = find_window_functions((Node *) root->processed_tlist,
list_length(parse->windowClause));
if (wflists->numWindowFuncs > 0)
+ {
+ /*
+ * See if any modifications can be made to each WindowClause
+ * to allow the executor to execute the WindowFuncs more
+ * quickly.
+ */
+ optimize_window_clauses(root, wflists);
+
activeWindows = select_active_windows(root, wflists);
+ }
else
parse->hasWindowFuncs = false;
}
@@ -5391,6 +5403,85 @@ postprocess_setop_tlist(List *new_tlist, List *orig_tlist)
return new_tlist;
}
+/*
+ * optimize_window_clauses
+ * Call each WindowFunc's prosupport function to see if we're able to
+ * make any adjustments to any of the WindowClause's so that the executor
+ * can execute the window functions in a more optimal way.
+ *
+ * Currently we only allow adjustments to the WindowClause's frameOptions. We
+ * may allow more things to be done here in the future.
+ */
+static void
+optimize_window_clauses(PlannerInfo *root, WindowFuncLists *wflists)
+{
+ List *windowClause = root->parse->windowClause;
+ ListCell *lc;
+
+ foreach(lc, windowClause)
+ {
+ WindowClause *wc = lfirst_node(WindowClause, lc);
+ ListCell *lc2;
+ int optimizedFrameOptions = 0;
+
+ Assert(wc->winref <= wflists->maxWinRef);
+
+ /* skip any WindowClauses that have no WindowFuncs */
+ if (wflists->windowFuncs[wc->winref] == NIL)
+ continue;
+
+ foreach(lc2, wflists->windowFuncs[wc->winref])
+ {
+ SupportRequestOptimizeWindowClause req;
+ SupportRequestOptimizeWindowClause *res;
+ WindowFunc *wfunc = lfirst_node(WindowFunc, lc2);
+ Oid prosupport;
+
+ prosupport = get_func_support(wfunc->winfnoid);
+
+ /* Check if there's a support function for 'wfunc' */
+ if (!OidIsValid(prosupport))
+ break; /* can't optimize this WindowClause */
+
+ req.type = T_SupportRequestOptimizeWindowClause;
+ req.window_clause = wc;
+ req.window_func = wfunc;
+ req.frameOptions = wc->frameOptions;
+
+ /* call the support function */
+ res = (SupportRequestOptimizeWindowClause *)
+ DatumGetPointer(OidFunctionCall1(prosupport,
+ PointerGetDatum(&req)));
+
+ /*
+ * Skip to next WindowClause if the support function does not
+ * support this request type.
+ */
+ if (res == NULL)
+ break;
+
+ /*
+ * Save these frameOptions for the first WindowFunc for this
+ * WindowClause.
+ */
+ if (foreach_current_index(lc2) == 0)
+ optimizedFrameOptions = res->frameOptions;
+
+ /*
+ * On subsequent WindowFuncs, if the frameOptions are not the same
+ * then we're unable to optimize the frameOptions for this
+ * WindowClause.
+ */
+ else if (optimizedFrameOptions != res->frameOptions)
+ break; /* skip to the next WindowClause, if any */
+ }
+
+ /* adjust the frameOptions if all WindowFunc's agree that it's ok */
+ if (lc2 == NULL)
+ wc->frameOptions = optimizedFrameOptions;
+ }
+}
+
/*
* select_active_windows
* Create a list of the "active" window clauses (ie, those referenced
diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c
index 596564fa15..3bf90813cf 100644
--- a/src/backend/utils/adt/windowfuncs.c
+++ b/src/backend/utils/adt/windowfuncs.c
@@ -107,6 +107,23 @@ window_row_number_support(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(req);
}
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * The frame options can always become "ROWS BETWEEN UNBOUNDED
+ * PRECEDING AND CURRENT ROW". row_number() always just increments
+ * by 1 with each row in the partition. Using ROWS instead of RANGE
+ * saves effort during execution.
+ */
+ req->frameOptions = (FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
PG_RETURN_POINTER(NULL);
}
@@ -149,6 +166,26 @@ window_rank_support(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(req);
}
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * rank() is coded in such a way that it returns "(COUNT (*) OVER
+ *(<opt> RANGE UNBOUNDED PRECEDING) - COUNT (*) OVER (<opt> RANGE
+ * CURRENT ROW) + 1 )" regardless of the frame options. We'll set the
+ * frame options to "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW"
+ * so they agree with what window_row_number_support() optimized the
+ * frame options to be. Using ROWS instead of RANGE saves effort
+ * during execution.
+ */
+ req->frameOptions = (FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
PG_RETURN_POINTER(NULL);
}
@@ -190,6 +227,22 @@ window_dense_rank_support(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(req);
}
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * Like window_rank(), window_dense_rank() is also unaffected by the
+ * frame options. Here we just set them to match what's done for the
+ * row_number() and rank() window functions.
+ */
+ req->frameOptions = (FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
PG_RETURN_POINTER(NULL);
}
diff --git a/src/include/nodes/supportnodes.h b/src/include/nodes/supportnodes.h
index 9fcbc39949..b446125b2b 100644
--- a/src/include/nodes/supportnodes.h
+++ b/src/include/nodes/supportnodes.h
@@ -299,4 +299,48 @@ typedef struct SupportRequestWFuncMonotonic
MonotonicFunction monotonic;
} SupportRequestWFuncMonotonic;
+/*
+ * Some WindowFunc behavior might not be affected by certain variations in
+ * the WindowClause's frameOptions. For example, row_number() is coded in
+ * such a way that the frame options don't change the returned row number.
+ * nodeWindowAgg.c will have less work to do if the ROWS option is used
+ * instead of the RANGE option as no check needs to be done for peer rows.
+ * Since RANGE is included in the default frame options, window functions
+ * such as row_number() might want to change that to ROW.
+ *
+ * Here we allow a WindowFunc's support function to determine which, if
+ * anything, can be changed about the WindowClause which the WindowFunc
+ * belongs to. Currently only the frameOptions can be modified. However,
+ * we may want to allow more optimizations in the future.
+ *
+ * The support function is responsible for ensuring the optimized version of
+ * the frameOptions doesn't affect the result of the window function. The
+ * planner is responsible for only changing the frame options when all
+ * WindowFuncs using this particular WindowClause agree on what the optimized
+ * version of the frameOptions are. If a particular WindowFunc being used
+ * does not have a support function then the planner will not make any changes
+ * to the WindowClause's frameOptions.
+ *
+ * 'window_func' and 'window_clause' are set by the planner before calling the
+ * support function so that the support function has these fields available.
+ * These may be required in order to determine which optimizations are
+ * possible.
+ *
+ * 'frameOptions' is set by the planner to WindowClause.frameOptions. The
+ * support function must only adjust this if optimizations are possible for
+ * the given WindowFunc.
+ */
+typedef struct SupportRequestOptimizeWindowClause
+{
+ NodeTag type;
+
+ /* Input fields: */
+ WindowFunc *window_func; /* Pointer to the window function data */
+ struct WindowClause *window_clause; /* Pointer to the window clause data */
+
+ /* Input/Output fields: */
+ int frameOptions; /* New frameOptions, or left untouched if no
+ * optimizations are possible. */
+} SupportRequestOptimizeWindowClause;
+
#endif /* SUPPORTNODES_H */
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options
@ 2022-10-13 21:51 Zhihong Yu <[email protected]>
parent: David Rowley <[email protected]>
2 siblings, 1 reply; 17+ messages in thread
From: Zhihong Yu @ 2022-10-13 21:51 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Vik Fearing <[email protected]>; PostgreSQL Developers <[email protected]>; Erwin Brandstetter <[email protected]>
On Wed, Oct 12, 2022 at 5:35 PM David Rowley <[email protected]> wrote:
> On Wed, 12 Oct 2022 at 16:33, Vik Fearing <[email protected]> wrote:
> > Per spec, the ROW_NUMBER() window function is not even allowed to have a
> > frame specified.
> >
> > b) The window framing clause of WDX shall not be present.
> >
> > Also, the specification for ROW_NUMBER() is:
> >
> > f) ROW_NUMBER() OVER WNS is equivalent to the <window function>:
> >
> > COUNT (*) OVER (WNS1 ROWS UNBOUNDED PRECEDING)
> >
> >
> > So I don't think we need to test for anything at all and can
> > indiscriminately add or replace the frame with ROWS UNBOUNDED PRECEDING.
>
> Thanks for digging that out.
>
> Just above that I see:
>
> RANK() OVER WNS is equivalent to:
> ( COUNT (*) OVER (WNS1 RANGE UNBOUNDED PRECEDING)
> - COUNT (*) OVER (WNS1 RANGE CURRENT ROW) + 1 )
>
> and
>
> DENSE_RANK() OVER WNS is equivalent to the <window function>:
> COUNT (DISTINCT ROW ( VE1, ..., VEN ) )
> OVER (WNS1 RANGE UNBOUNDED PRECEDING)
>
> So it looks like the same can be done for rank() and dense_rank() too.
> I've added support for those in the attached.
>
> This also got me thinking that maybe we should be a bit more generic
> with the support function node tag name. After looking at the
> nodeWindowAgg.c code for a while, I wondered if we might want to add
> some optimisations in the future that makes WindowAgg not bother
> storing tuples for row_number(), rank() and dense_rank(). That might
> save a bit of overhead from the tuple store. I imagined that we'd
> want to allow the expansion of this support request so that the
> support function could let the planner know if any tuples will be
> accessed by the window function or not. The
> SupportRequestWFuncOptimizeFrameOpts name didn't seem very fitting for
> that so I adjusted it to become SupportRequestOptimizeWindowClause
> instead.
>
> The updated patch is attached.
>
> David
>
Hi,
+ req->frameOptions = (FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
The bit combination appears multiple times in the patch.
Maybe define the combination as a constant in supportnodes.h and reference
it in the code.
Cheers
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options
@ 2022-10-17 22:10 Erwin Brandstetter <[email protected]>
parent: David Rowley <[email protected]>
2 siblings, 1 reply; 17+ messages in thread
From: Erwin Brandstetter @ 2022-10-17 22:10 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Vik Fearing <[email protected]>; PostgreSQL Developers <[email protected]>
On Thu, 13 Oct 2022 at 02:34, David Rowley <[email protected]> wrote:
> On Wed, 12 Oct 2022 at 16:33, Vik Fearing <[email protected]> wrote:
> > Per spec, the ROW_NUMBER() window function is not even allowed to have a
> > frame specified.
> >
> > b) The window framing clause of WDX shall not be present.
> >
> > Also, the specification for ROW_NUMBER() is:
> >
> > f) ROW_NUMBER() OVER WNS is equivalent to the <window function>:
> >
> > COUNT (*) OVER (WNS1 ROWS UNBOUNDED PRECEDING)
> >
> >
> > So I don't think we need to test for anything at all and can
> > indiscriminately add or replace the frame with ROWS UNBOUNDED PRECEDING.
>
> Thanks for digging that out.
>
> Just above that I see:
>
> RANK() OVER WNS is equivalent to:
> ( COUNT (*) OVER (WNS1 RANGE UNBOUNDED PRECEDING)
> - COUNT (*) OVER (WNS1 RANGE CURRENT ROW) + 1 )
>
> and
>
> DENSE_RANK() OVER WNS is equivalent to the <window function>:
> COUNT (DISTINCT ROW ( VE1, ..., VEN ) )
> OVER (WNS1 RANGE UNBOUNDED PRECEDING)
>
> So it looks like the same can be done for rank() and dense_rank() too.
> I've added support for those in the attached.
>
> This also got me thinking that maybe we should be a bit more generic
> with the support function node tag name. After looking at the
> nodeWindowAgg.c code for a while, I wondered if we might want to add
> some optimisations in the future that makes WindowAgg not bother
> storing tuples for row_number(), rank() and dense_rank(). That might
> save a bit of overhead from the tuple store. I imagined that we'd
> want to allow the expansion of this support request so that the
> support function could let the planner know if any tuples will be
> accessed by the window function or not. The
> SupportRequestWFuncOptimizeFrameOpts name didn't seem very fitting for
> that so I adjusted it to become SupportRequestOptimizeWindowClause
> instead.
>
> The updated patch is attached.
> David
>
I am thinking of building a test case to run
- all existing window functions
- with all basic variants of frame definitions
- once with ROWS, once with RANGE
- on basic table that has duplicate and NULL values in partition and
ordering columns
- in all supported major versions
To verify for which of our window functions ROWS vs. RANGE never makes a
difference.
That should be obvious in most cases, just to be sure.
Do you think this would be helpful?
Regards
Erwin
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options
@ 2022-10-17 23:18 Tom Lane <[email protected]>
parent: Erwin Brandstetter <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Tom Lane @ 2022-10-17 23:18 UTC (permalink / raw)
To: Erwin Brandstetter <[email protected]>; +Cc: David Rowley <[email protected]>; Vik Fearing <[email protected]>; PostgreSQL Developers <[email protected]>
Erwin Brandstetter <[email protected]> writes:
> I am thinking of building a test case to run
> - all existing window functions
> - with all basic variants of frame definitions
> - once with ROWS, once with RANGE
> - on basic table that has duplicate and NULL values in partition and
> ordering columns
> - in all supported major versions
> To verify for which of our window functions ROWS vs. RANGE never makes a
> difference.
> That should be obvious in most cases, just to be sure.
> Do you think this would be helpful?
Doubt it. Per the old saying "testing can prove the presence of bugs,
but not their absence", this could prove that some functions *do*
respond to these options, but it cannot prove that a function
*doesn't*. Maybe you just didn't try the right test case.
If you want to try something like that as a heuristic to see which
cases are worth looking at closer, sure, but it's only a heuristic.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options
@ 2022-10-17 23:58 David Rowley <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: David Rowley @ 2022-10-17 23:58 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Erwin Brandstetter <[email protected]>; Vik Fearing <[email protected]>; PostgreSQL Developers <[email protected]>
On Tue, 18 Oct 2022 at 12:18, Tom Lane <[email protected]> wrote:
>
> Erwin Brandstetter <[email protected]> writes:
> > I am thinking of building a test case to run
> > - all existing window functions
> > - with all basic variants of frame definitions
> > - once with ROWS, once with RANGE
> > - on basic table that has duplicate and NULL values in partition and
> > ordering columns
> > - in all supported major versions
>
> > To verify for which of our window functions ROWS vs. RANGE never makes a
> > difference.
> > That should be obvious in most cases, just to be sure.
>
> > Do you think this would be helpful?
>
> Doubt it. Per the old saying "testing can prove the presence of bugs,
> but not their absence", this could prove that some functions *do*
> respond to these options, but it cannot prove that a function
> *doesn't*. Maybe you just didn't try the right test case.
I suppose this is kind of like fuzz testing. Going by "git log
--grep=sqlsmith", fuzzing certainly has found bugs for us in the past.
I personally wouldn't discourage Erwin from doing this.
For me, my first port of call will be to study the code of each window
function to see if the frame options can affect the result. I *do*
need to spend more time on this still. It would be good to have some
extra assurance on having read the code with some more exhaustive
testing results. If Erwin was to find result variations that I missed
then we might avoid writing some new bugs.
Also, I just did spend a little more time reading a few window
functions and I see percent_rank() is another candidate for this
optimisation. I've never needed to use that function before, but from
the following experiment, it seems to just be (rank() over (order by
...) - 1) / (count(*) over () - 1). Since rank() is already on the
list and count(*) over() contains all rows in the frame, then it seems
percent_rank() can join the club too.
create table t0 as select x*random() as a from generate_series(1,1000000)x;
select * from (select a,percent_rank() over (order by a) pr,(rank()
over (order by a) - 1) / (count(*) over () - 1)::float8 pr2 from t0)
c where pr <> pr2;
David
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options
@ 2022-10-18 00:05 David Rowley <[email protected]>
parent: Zhihong Yu <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: David Rowley @ 2022-10-18 00:05 UTC (permalink / raw)
To: Zhihong Yu <[email protected]>; +Cc: Vik Fearing <[email protected]>; PostgreSQL Developers <[email protected]>; Erwin Brandstetter <[email protected]>
Thanks for having a look at this.
On Fri, 14 Oct 2022 at 10:52, Zhihong Yu <[email protected]> wrote:
> + req->frameOptions = (FRAMEOPTION_ROWS |
> + FRAMEOPTION_START_UNBOUNDED_PRECEDING |
> + FRAMEOPTION_END_CURRENT_ROW);
>
> The bit combination appears multiple times in the patch.
> Maybe define the combination as a constant in supportnodes.h and reference it in the code.
I don't believe supportnodes.h has any business having any code that's
related to actual implementations of the support request type. If we
were to have such a definition then I think it would belong in
windowfuncs.c. I'd rather see each implementation of the support
request spell out exactly what they mean, which is what the patch does
already.
David
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options
@ 2022-10-18 02:40 Zhihong Yu <[email protected]>
parent: David Rowley <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: Zhihong Yu @ 2022-10-18 02:40 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Vik Fearing <[email protected]>; PostgreSQL Developers <[email protected]>; Erwin Brandstetter <[email protected]>
On Mon, Oct 17, 2022 at 5:05 PM David Rowley <[email protected]> wrote:
> Thanks for having a look at this.
>
> On Fri, 14 Oct 2022 at 10:52, Zhihong Yu <[email protected]> wrote:
> > + req->frameOptions = (FRAMEOPTION_ROWS |
> > + FRAMEOPTION_START_UNBOUNDED_PRECEDING |
> > + FRAMEOPTION_END_CURRENT_ROW);
> >
> > The bit combination appears multiple times in the patch.
> > Maybe define the combination as a constant in supportnodes.h and
> reference it in the code.
>
> I don't believe supportnodes.h has any business having any code that's
> related to actual implementations of the support request type. If we
> were to have such a definition then I think it would belong in
> windowfuncs.c. I'd rather see each implementation of the support
> request spell out exactly what they mean, which is what the patch does
> already.
>
> David
>
Hi,
I am fine with keeping the code where it is now.
Cheers
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options
@ 2022-10-20 20:02 David Rowley <[email protected]>
parent: David Rowley <[email protected]>
2 siblings, 1 reply; 17+ messages in thread
From: David Rowley @ 2022-10-20 20:02 UTC (permalink / raw)
To: Vik Fearing <[email protected]>; +Cc: PostgreSQL Developers <[email protected]>; Erwin Brandstetter <[email protected]>
On Thu, 13 Oct 2022 at 13:34, David Rowley <[email protected]> wrote:
> So it looks like the same can be done for rank() and dense_rank() too.
> I've added support for those in the attached.
The attached adds support for percent_rank(), cume_dist() and ntile().
David
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 5d0fd6e072..fa28fb539a 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -38,6 +38,7 @@
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "nodes/supportnodes.h"
#ifdef OPTIMIZER_DEBUG
#include "nodes/print.h"
#endif
@@ -207,6 +208,8 @@ static PathTarget *make_partial_grouping_target(PlannerInfo *root,
PathTarget *grouping_target,
Node *havingQual);
static List *postprocess_setop_tlist(List *new_tlist, List *orig_tlist);
+static void optimize_window_clauses(PlannerInfo *root,
+ WindowFuncLists *wflists);
static List *select_active_windows(PlannerInfo *root, WindowFuncLists *wflists);
static PathTarget *make_window_input_target(PlannerInfo *root,
PathTarget *final_target,
@@ -1422,7 +1425,16 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
wflists = find_window_functions((Node *) root->processed_tlist,
list_length(parse->windowClause));
if (wflists->numWindowFuncs > 0)
+ {
+ /*
+ * See if any modifications can be made to each WindowClause
+ * to allow the executor to execute the WindowFuncs more
+ * quickly.
+ */
+ optimize_window_clauses(root, wflists);
+
activeWindows = select_active_windows(root, wflists);
+ }
else
parse->hasWindowFuncs = false;
}
@@ -5391,6 +5403,85 @@ postprocess_setop_tlist(List *new_tlist, List *orig_tlist)
return new_tlist;
}
+/*
+ * optimize_window_clauses
+ * Call each WindowFunc's prosupport function to see if we're able to
+ * make any adjustments to any of the WindowClause's so that the executor
+ * can execute the window functions in a more optimal way.
+ *
+ * Currently we only allow adjustments to the WindowClause's frameOptions. We
+ * may allow more things to be done here in the future.
+ */
+static void
+optimize_window_clauses(PlannerInfo *root, WindowFuncLists *wflists)
+{
+ List *windowClause = root->parse->windowClause;
+ ListCell *lc;
+
+ foreach(lc, windowClause)
+ {
+ WindowClause *wc = lfirst_node(WindowClause, lc);
+ ListCell *lc2;
+ int optimizedFrameOptions = 0;
+
+ Assert(wc->winref <= wflists->maxWinRef);
+
+ /* skip any WindowClauses that have no WindowFuncs */
+ if (wflists->windowFuncs[wc->winref] == NIL)
+ continue;
+
+ foreach(lc2, wflists->windowFuncs[wc->winref])
+ {
+ SupportRequestOptimizeWindowClause req;
+ SupportRequestOptimizeWindowClause *res;
+ WindowFunc *wfunc = lfirst_node(WindowFunc, lc2);
+ Oid prosupport;
+
+ prosupport = get_func_support(wfunc->winfnoid);
+
+ /* Check if there's a support function for 'wfunc' */
+ if (!OidIsValid(prosupport))
+ break; /* can't optimize this WindowClause */
+
+ req.type = T_SupportRequestOptimizeWindowClause;
+ req.window_clause = wc;
+ req.window_func = wfunc;
+ req.frameOptions = wc->frameOptions;
+
+ /* call the support function */
+ res = (SupportRequestOptimizeWindowClause *)
+ DatumGetPointer(OidFunctionCall1(prosupport,
+ PointerGetDatum(&req)));
+
+ /*
+ * Skip to next WindowClause if the support function does not
+ * support this request type.
+ */
+ if (res == NULL)
+ break;
+
+ /*
+ * Save these frameOptions for the first WindowFunc for this
+ * WindowClause.
+ */
+ if (foreach_current_index(lc2) == 0)
+ optimizedFrameOptions = res->frameOptions;
+
+ /*
+ * On subsequent WindowFuncs, if the frameOptions are not the same
+ * then we're unable to optimize the frameOptions for this
+ * WindowClause.
+ */
+ else if (optimizedFrameOptions != res->frameOptions)
+ break; /* skip to the next WindowClause, if any */
+ }
+
+ /* adjust the frameOptions if all WindowFunc's agree that it's ok */
+ if (lc2 == NULL)
+ wc->frameOptions = optimizedFrameOptions;
+ }
+}
+
/*
* select_active_windows
* Create a list of the "active" window clauses (ie, those referenced
diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c
index 596564fa15..f5647ac67f 100644
--- a/src/backend/utils/adt/windowfuncs.c
+++ b/src/backend/utils/adt/windowfuncs.c
@@ -107,6 +107,23 @@ window_row_number_support(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(req);
}
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * The frame options can always become "ROWS BETWEEN UNBOUNDED
+ * PRECEDING AND CURRENT ROW". row_number() always just increments
+ * by 1 with each row in the partition. Using ROWS instead of RANGE
+ * saves effort during execution.
+ */
+ req->frameOptions = (FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
PG_RETURN_POINTER(NULL);
}
@@ -149,6 +166,26 @@ window_rank_support(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(req);
}
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * rank() is coded in such a way that it returns "(COUNT (*) OVER
+ *(<opt> RANGE UNBOUNDED PRECEDING) - COUNT (*) OVER (<opt> RANGE
+ * CURRENT ROW) + 1)" regardless of the frame options. We'll set the
+ * frame options to "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW"
+ * so they agree with what window_row_number_support() optimized the
+ * frame options to be. Using ROWS instead of RANGE saves from doing
+ * peer row checks during execution.
+ */
+ req->frameOptions = (FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
PG_RETURN_POINTER(NULL);
}
@@ -190,6 +227,23 @@ window_dense_rank_support(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(req);
}
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * dense_rank() is also unaffected by the frame options. Here we set
+ * the frame options to match what's done in row_number's support
+ * function. Using ROWS instead of RANGE (the default) saves the
+ * executor from having to check for peer rows.
+ */
+ req->frameOptions = (FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
PG_RETURN_POINTER(NULL);
}
@@ -222,6 +276,36 @@ window_percent_rank(PG_FUNCTION_ARGS)
PG_RETURN_FLOAT8((float8) (context->rank - 1) / (float8) (totalrows - 1));
}
+/*
+ * window_percent_rank_support
+ * prosupport function for window_percent_rank()
+ */
+Datum
+window_percent_rank_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * percent_rank() is also unaffected by the frame options. Here we
+ * set the frame options to match what's done in row_number's support
+ * function. Using ROWS instead of RANGE (the default) saves the
+ * executor from having to check for peer rows.
+ */
+ req->frameOptions = (FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
+ PG_RETURN_POINTER(NULL);
+}
+
+
/*
* cume_dist
* return fraction between 0 and 1 inclusive,
@@ -265,6 +349,35 @@ window_cume_dist(PG_FUNCTION_ARGS)
PG_RETURN_FLOAT8((float8) context->rank / (float8) totalrows);
}
+/*
+ * window_cume_dist_support
+ * prosupport function for window_cume_dist()
+ */
+Datum
+window_cume_dist_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * cume_dist() is also unaffected by the frame options. Here we set
+ * the frame options to match what's done in row_number's support
+ * function. Using ROWS instead of RANGE (the default) saves the
+ * executor from having to check for peer rows.
+ */
+ req->frameOptions = (FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
+ PG_RETURN_POINTER(NULL);
+}
+
/*
* ntile
* compute an exact numeric value with scale 0 (zero),
@@ -338,6 +451,35 @@ window_ntile(PG_FUNCTION_ARGS)
PG_RETURN_INT32(context->ntile);
}
+/*
+ * window_ntile_support
+ * prosupport function for window_ntile()
+ */
+Datum
+window_ntile_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * ntile() is also unaffected by the frame options. Here we set the
+ * frame options to match what's done in row_number's support
+ * function. Using ROWS instead of RANGE (the default) saves the
+ * executor from having to check for peer rows.
+ */
+ req->frameOptions = (FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
+ PG_RETURN_POINTER(NULL);
+}
+
/*
* leadlag_common
* common operation of lead() and lag()
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 62a5b8e655..ee01b2e3d0 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10137,32 +10137,41 @@
proname => 'row_number', prosupport => 'window_row_number_support',
prokind => 'w', proisstrict => 'f', prorettype => 'int8', proargtypes => '',
prosrc => 'window_row_number' },
-{ oid => '6233', descr => 'planner support for row_number run condition',
+{ oid => '6233', descr => 'planner support for row_number',
proname => 'window_row_number_support', prorettype => 'internal',
proargtypes => 'internal', prosrc => 'window_row_number_support' },
{ oid => '3101', descr => 'integer rank with gaps',
proname => 'rank', prosupport => 'window_rank_support', prokind => 'w',
proisstrict => 'f', prorettype => 'int8', proargtypes => '',
prosrc => 'window_rank' },
-{ oid => '6234', descr => 'planner support for rank run condition',
+{ oid => '6234', descr => 'planner support for rank',
proname => 'window_rank_support', prorettype => 'internal',
proargtypes => 'internal', prosrc => 'window_rank_support' },
{ oid => '3102', descr => 'integer rank without gaps',
proname => 'dense_rank', prosupport => 'window_dense_rank_support',
prokind => 'w', proisstrict => 'f', prorettype => 'int8', proargtypes => '',
prosrc => 'window_dense_rank' },
-{ oid => '6235', descr => 'planner support for dense rank run condition',
+{ oid => '6235', descr => 'planner support for dense_rank',
proname => 'window_dense_rank_support', prorettype => 'internal',
proargtypes => 'internal', prosrc => 'window_dense_rank_support' },
{ oid => '3103', descr => 'fractional rank within partition',
proname => 'percent_rank', prokind => 'w', proisstrict => 'f',
prorettype => 'float8', proargtypes => '', prosrc => 'window_percent_rank' },
+{ oid => '9773', descr => 'planner support for percent_rank',
+ proname => 'window_percent_rank_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'window_percent_rank_support' },
{ oid => '3104', descr => 'fractional row number within partition',
proname => 'cume_dist', prokind => 'w', proisstrict => 'f',
prorettype => 'float8', proargtypes => '', prosrc => 'window_cume_dist' },
+{ oid => '9774', descr => 'planner support for cume_dist',
+ proname => 'window_cume_dist_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'window_cume_dist_support' },
{ oid => '3105', descr => 'split rows into N groups',
proname => 'ntile', prokind => 'w', prorettype => 'int4',
proargtypes => 'int4', prosrc => 'window_ntile' },
+{ oid => '9775', descr => 'planner support for ntile',
+ proname => 'window_ntile_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'window_ntile_support' },
{ oid => '3106', descr => 'fetch the preceding row value',
proname => 'lag', prokind => 'w', prorettype => 'anyelement',
proargtypes => 'anyelement', prosrc => 'window_lag' },
diff --git a/src/include/nodes/supportnodes.h b/src/include/nodes/supportnodes.h
index 9fcbc39949..b446125b2b 100644
--- a/src/include/nodes/supportnodes.h
+++ b/src/include/nodes/supportnodes.h
@@ -299,4 +299,48 @@ typedef struct SupportRequestWFuncMonotonic
MonotonicFunction monotonic;
} SupportRequestWFuncMonotonic;
+/*
+ * Some WindowFunc behavior might not be affected by certain variations in
+ * the WindowClause's frameOptions. For example, row_number() is coded in
+ * such a way that the frame options don't change the returned row number.
+ * nodeWindowAgg.c will have less work to do if the ROWS option is used
+ * instead of the RANGE option as no check needs to be done for peer rows.
+ * Since RANGE is included in the default frame options, window functions
+ * such as row_number() might want to change that to ROW.
+ *
+ * Here we allow a WindowFunc's support function to determine which, if
+ * anything, can be changed about the WindowClause which the WindowFunc
+ * belongs to. Currently only the frameOptions can be modified. However,
+ * we may want to allow more optimizations in the future.
+ *
+ * The support function is responsible for ensuring the optimized version of
+ * the frameOptions doesn't affect the result of the window function. The
+ * planner is responsible for only changing the frame options when all
+ * WindowFuncs using this particular WindowClause agree on what the optimized
+ * version of the frameOptions are. If a particular WindowFunc being used
+ * does not have a support function then the planner will not make any changes
+ * to the WindowClause's frameOptions.
+ *
+ * 'window_func' and 'window_clause' are set by the planner before calling the
+ * support function so that the support function has these fields available.
+ * These may be required in order to determine which optimizations are
+ * possible.
+ *
+ * 'frameOptions' is set by the planner to WindowClause.frameOptions. The
+ * support function must only adjust this if optimizations are possible for
+ * the given WindowFunc.
+ */
+typedef struct SupportRequestOptimizeWindowClause
+{
+ NodeTag type;
+
+ /* Input fields: */
+ WindowFunc *window_func; /* Pointer to the window function data */
+ struct WindowClause *window_clause; /* Pointer to the window clause data */
+
+ /* Input/Output fields: */
+ int frameOptions; /* New frameOptions, or left untouched if no
+ * optimizations are possible. */
+} SupportRequestOptimizeWindowClause;
+
#endif /* SUPPORTNODES_H */
Attachments:
[text/plain] v3_optimize_row_numbers_frameoptions_when_possible.patch (14.9K, ../../CAApHDvrkd75-Bjcz8crze-KzsPEVzpZn6DepzOkq-VGx33X30g@mail.gmail.com/2-v3_optimize_row_numbers_frameoptions_when_possible.patch)
download | inline diff:
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 5d0fd6e072..fa28fb539a 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -38,6 +38,7 @@
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "nodes/supportnodes.h"
#ifdef OPTIMIZER_DEBUG
#include "nodes/print.h"
#endif
@@ -207,6 +208,8 @@ static PathTarget *make_partial_grouping_target(PlannerInfo *root,
PathTarget *grouping_target,
Node *havingQual);
static List *postprocess_setop_tlist(List *new_tlist, List *orig_tlist);
+static void optimize_window_clauses(PlannerInfo *root,
+ WindowFuncLists *wflists);
static List *select_active_windows(PlannerInfo *root, WindowFuncLists *wflists);
static PathTarget *make_window_input_target(PlannerInfo *root,
PathTarget *final_target,
@@ -1422,7 +1425,16 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
wflists = find_window_functions((Node *) root->processed_tlist,
list_length(parse->windowClause));
if (wflists->numWindowFuncs > 0)
+ {
+ /*
+ * See if any modifications can be made to each WindowClause
+ * to allow the executor to execute the WindowFuncs more
+ * quickly.
+ */
+ optimize_window_clauses(root, wflists);
+
activeWindows = select_active_windows(root, wflists);
+ }
else
parse->hasWindowFuncs = false;
}
@@ -5391,6 +5403,85 @@ postprocess_setop_tlist(List *new_tlist, List *orig_tlist)
return new_tlist;
}
+/*
+ * optimize_window_clauses
+ * Call each WindowFunc's prosupport function to see if we're able to
+ * make any adjustments to any of the WindowClause's so that the executor
+ * can execute the window functions in a more optimal way.
+ *
+ * Currently we only allow adjustments to the WindowClause's frameOptions. We
+ * may allow more things to be done here in the future.
+ */
+static void
+optimize_window_clauses(PlannerInfo *root, WindowFuncLists *wflists)
+{
+ List *windowClause = root->parse->windowClause;
+ ListCell *lc;
+
+ foreach(lc, windowClause)
+ {
+ WindowClause *wc = lfirst_node(WindowClause, lc);
+ ListCell *lc2;
+ int optimizedFrameOptions = 0;
+
+ Assert(wc->winref <= wflists->maxWinRef);
+
+ /* skip any WindowClauses that have no WindowFuncs */
+ if (wflists->windowFuncs[wc->winref] == NIL)
+ continue;
+
+ foreach(lc2, wflists->windowFuncs[wc->winref])
+ {
+ SupportRequestOptimizeWindowClause req;
+ SupportRequestOptimizeWindowClause *res;
+ WindowFunc *wfunc = lfirst_node(WindowFunc, lc2);
+ Oid prosupport;
+
+ prosupport = get_func_support(wfunc->winfnoid);
+
+ /* Check if there's a support function for 'wfunc' */
+ if (!OidIsValid(prosupport))
+ break; /* can't optimize this WindowClause */
+
+ req.type = T_SupportRequestOptimizeWindowClause;
+ req.window_clause = wc;
+ req.window_func = wfunc;
+ req.frameOptions = wc->frameOptions;
+
+ /* call the support function */
+ res = (SupportRequestOptimizeWindowClause *)
+ DatumGetPointer(OidFunctionCall1(prosupport,
+ PointerGetDatum(&req)));
+
+ /*
+ * Skip to next WindowClause if the support function does not
+ * support this request type.
+ */
+ if (res == NULL)
+ break;
+
+ /*
+ * Save these frameOptions for the first WindowFunc for this
+ * WindowClause.
+ */
+ if (foreach_current_index(lc2) == 0)
+ optimizedFrameOptions = res->frameOptions;
+
+ /*
+ * On subsequent WindowFuncs, if the frameOptions are not the same
+ * then we're unable to optimize the frameOptions for this
+ * WindowClause.
+ */
+ else if (optimizedFrameOptions != res->frameOptions)
+ break; /* skip to the next WindowClause, if any */
+ }
+
+ /* adjust the frameOptions if all WindowFunc's agree that it's ok */
+ if (lc2 == NULL)
+ wc->frameOptions = optimizedFrameOptions;
+ }
+}
+
/*
* select_active_windows
* Create a list of the "active" window clauses (ie, those referenced
diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c
index 596564fa15..f5647ac67f 100644
--- a/src/backend/utils/adt/windowfuncs.c
+++ b/src/backend/utils/adt/windowfuncs.c
@@ -107,6 +107,23 @@ window_row_number_support(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(req);
}
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * The frame options can always become "ROWS BETWEEN UNBOUNDED
+ * PRECEDING AND CURRENT ROW". row_number() always just increments
+ * by 1 with each row in the partition. Using ROWS instead of RANGE
+ * saves effort during execution.
+ */
+ req->frameOptions = (FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
PG_RETURN_POINTER(NULL);
}
@@ -149,6 +166,26 @@ window_rank_support(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(req);
}
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * rank() is coded in such a way that it returns "(COUNT (*) OVER
+ *(<opt> RANGE UNBOUNDED PRECEDING) - COUNT (*) OVER (<opt> RANGE
+ * CURRENT ROW) + 1)" regardless of the frame options. We'll set the
+ * frame options to "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW"
+ * so they agree with what window_row_number_support() optimized the
+ * frame options to be. Using ROWS instead of RANGE saves from doing
+ * peer row checks during execution.
+ */
+ req->frameOptions = (FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
PG_RETURN_POINTER(NULL);
}
@@ -190,6 +227,23 @@ window_dense_rank_support(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(req);
}
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * dense_rank() is also unaffected by the frame options. Here we set
+ * the frame options to match what's done in row_number's support
+ * function. Using ROWS instead of RANGE (the default) saves the
+ * executor from having to check for peer rows.
+ */
+ req->frameOptions = (FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
PG_RETURN_POINTER(NULL);
}
@@ -222,6 +276,36 @@ window_percent_rank(PG_FUNCTION_ARGS)
PG_RETURN_FLOAT8((float8) (context->rank - 1) / (float8) (totalrows - 1));
}
+/*
+ * window_percent_rank_support
+ * prosupport function for window_percent_rank()
+ */
+Datum
+window_percent_rank_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * percent_rank() is also unaffected by the frame options. Here we
+ * set the frame options to match what's done in row_number's support
+ * function. Using ROWS instead of RANGE (the default) saves the
+ * executor from having to check for peer rows.
+ */
+ req->frameOptions = (FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
+ PG_RETURN_POINTER(NULL);
+}
+
+
/*
* cume_dist
* return fraction between 0 and 1 inclusive,
@@ -265,6 +349,35 @@ window_cume_dist(PG_FUNCTION_ARGS)
PG_RETURN_FLOAT8((float8) context->rank / (float8) totalrows);
}
+/*
+ * window_cume_dist_support
+ * prosupport function for window_cume_dist()
+ */
+Datum
+window_cume_dist_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * cume_dist() is also unaffected by the frame options. Here we set
+ * the frame options to match what's done in row_number's support
+ * function. Using ROWS instead of RANGE (the default) saves the
+ * executor from having to check for peer rows.
+ */
+ req->frameOptions = (FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
+ PG_RETURN_POINTER(NULL);
+}
+
/*
* ntile
* compute an exact numeric value with scale 0 (zero),
@@ -338,6 +451,35 @@ window_ntile(PG_FUNCTION_ARGS)
PG_RETURN_INT32(context->ntile);
}
+/*
+ * window_ntile_support
+ * prosupport function for window_ntile()
+ */
+Datum
+window_ntile_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * ntile() is also unaffected by the frame options. Here we set the
+ * frame options to match what's done in row_number's support
+ * function. Using ROWS instead of RANGE (the default) saves the
+ * executor from having to check for peer rows.
+ */
+ req->frameOptions = (FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
+ PG_RETURN_POINTER(NULL);
+}
+
/*
* leadlag_common
* common operation of lead() and lag()
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 62a5b8e655..ee01b2e3d0 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10137,32 +10137,41 @@
proname => 'row_number', prosupport => 'window_row_number_support',
prokind => 'w', proisstrict => 'f', prorettype => 'int8', proargtypes => '',
prosrc => 'window_row_number' },
-{ oid => '6233', descr => 'planner support for row_number run condition',
+{ oid => '6233', descr => 'planner support for row_number',
proname => 'window_row_number_support', prorettype => 'internal',
proargtypes => 'internal', prosrc => 'window_row_number_support' },
{ oid => '3101', descr => 'integer rank with gaps',
proname => 'rank', prosupport => 'window_rank_support', prokind => 'w',
proisstrict => 'f', prorettype => 'int8', proargtypes => '',
prosrc => 'window_rank' },
-{ oid => '6234', descr => 'planner support for rank run condition',
+{ oid => '6234', descr => 'planner support for rank',
proname => 'window_rank_support', prorettype => 'internal',
proargtypes => 'internal', prosrc => 'window_rank_support' },
{ oid => '3102', descr => 'integer rank without gaps',
proname => 'dense_rank', prosupport => 'window_dense_rank_support',
prokind => 'w', proisstrict => 'f', prorettype => 'int8', proargtypes => '',
prosrc => 'window_dense_rank' },
-{ oid => '6235', descr => 'planner support for dense rank run condition',
+{ oid => '6235', descr => 'planner support for dense_rank',
proname => 'window_dense_rank_support', prorettype => 'internal',
proargtypes => 'internal', prosrc => 'window_dense_rank_support' },
{ oid => '3103', descr => 'fractional rank within partition',
proname => 'percent_rank', prokind => 'w', proisstrict => 'f',
prorettype => 'float8', proargtypes => '', prosrc => 'window_percent_rank' },
+{ oid => '9773', descr => 'planner support for percent_rank',
+ proname => 'window_percent_rank_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'window_percent_rank_support' },
{ oid => '3104', descr => 'fractional row number within partition',
proname => 'cume_dist', prokind => 'w', proisstrict => 'f',
prorettype => 'float8', proargtypes => '', prosrc => 'window_cume_dist' },
+{ oid => '9774', descr => 'planner support for cume_dist',
+ proname => 'window_cume_dist_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'window_cume_dist_support' },
{ oid => '3105', descr => 'split rows into N groups',
proname => 'ntile', prokind => 'w', prorettype => 'int4',
proargtypes => 'int4', prosrc => 'window_ntile' },
+{ oid => '9775', descr => 'planner support for ntile',
+ proname => 'window_ntile_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'window_ntile_support' },
{ oid => '3106', descr => 'fetch the preceding row value',
proname => 'lag', prokind => 'w', prorettype => 'anyelement',
proargtypes => 'anyelement', prosrc => 'window_lag' },
diff --git a/src/include/nodes/supportnodes.h b/src/include/nodes/supportnodes.h
index 9fcbc39949..b446125b2b 100644
--- a/src/include/nodes/supportnodes.h
+++ b/src/include/nodes/supportnodes.h
@@ -299,4 +299,48 @@ typedef struct SupportRequestWFuncMonotonic
MonotonicFunction monotonic;
} SupportRequestWFuncMonotonic;
+/*
+ * Some WindowFunc behavior might not be affected by certain variations in
+ * the WindowClause's frameOptions. For example, row_number() is coded in
+ * such a way that the frame options don't change the returned row number.
+ * nodeWindowAgg.c will have less work to do if the ROWS option is used
+ * instead of the RANGE option as no check needs to be done for peer rows.
+ * Since RANGE is included in the default frame options, window functions
+ * such as row_number() might want to change that to ROW.
+ *
+ * Here we allow a WindowFunc's support function to determine which, if
+ * anything, can be changed about the WindowClause which the WindowFunc
+ * belongs to. Currently only the frameOptions can be modified. However,
+ * we may want to allow more optimizations in the future.
+ *
+ * The support function is responsible for ensuring the optimized version of
+ * the frameOptions doesn't affect the result of the window function. The
+ * planner is responsible for only changing the frame options when all
+ * WindowFuncs using this particular WindowClause agree on what the optimized
+ * version of the frameOptions are. If a particular WindowFunc being used
+ * does not have a support function then the planner will not make any changes
+ * to the WindowClause's frameOptions.
+ *
+ * 'window_func' and 'window_clause' are set by the planner before calling the
+ * support function so that the support function has these fields available.
+ * These may be required in order to determine which optimizations are
+ * possible.
+ *
+ * 'frameOptions' is set by the planner to WindowClause.frameOptions. The
+ * support function must only adjust this if optimizations are possible for
+ * the given WindowFunc.
+ */
+typedef struct SupportRequestOptimizeWindowClause
+{
+ NodeTag type;
+
+ /* Input fields: */
+ WindowFunc *window_func; /* Pointer to the window function data */
+ struct WindowClause *window_clause; /* Pointer to the window clause data */
+
+ /* Input/Output fields: */
+ int frameOptions; /* New frameOptions, or left untouched if no
+ * optimizations are possible. */
+} SupportRequestOptimizeWindowClause;
+
#endif /* SUPPORTNODES_H */
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options
@ 2022-10-22 14:03 Vik Fearing <[email protected]>
parent: David Rowley <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Vik Fearing @ 2022-10-22 14:03 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: PostgreSQL Developers <[email protected]>; Erwin Brandstetter <[email protected]>
On 10/20/22 22:02, David Rowley wrote:
> On Thu, 13 Oct 2022 at 13:34, David Rowley <[email protected]> wrote:
>> So it looks like the same can be done for rank() and dense_rank() too.
>> I've added support for those in the attached.
>
> The attached adds support for percent_rank(), cume_dist() and ntile().
Shouldn't it be able to detect that these two windows are the same and
only do one WindowAgg pass?
explain (verbose, costs off)
select row_number() over w1,
lag(amname) over w2
from pg_am
window w1 as (order by amname),
w2 as (w1 rows unbounded preceding)
;
QUERY PLAN
-----------------------------------------------------------------
WindowAgg
Output: (row_number() OVER (?)), lag(amname) OVER (?), amname
-> WindowAgg
Output: amname, row_number() OVER (?)
-> Sort
Output: amname
Sort Key: pg_am.amname
-> Seq Scan on pg_catalog.pg_am
Output: amname
(9 rows)
--
Vik Fearing
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options
@ 2022-10-26 01:38 David Rowley <[email protected]>
parent: Vik Fearing <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: David Rowley @ 2022-10-26 01:38 UTC (permalink / raw)
To: Vik Fearing <[email protected]>; +Cc: PostgreSQL Developers <[email protected]>; Erwin Brandstetter <[email protected]>
On Sun, 23 Oct 2022 at 03:03, Vik Fearing <[email protected]> wrote:
> Shouldn't it be able to detect that these two windows are the same and
> only do one WindowAgg pass?
>
>
> explain (verbose, costs off)
> select row_number() over w1,
> lag(amname) over w2
> from pg_am
> window w1 as (order by amname),
> w2 as (w1 rows unbounded preceding)
> ;
Good thinking. I think the patch should also optimise that case. It
requires re-doing a similar de-duplication phase the same as what's
done in transformWindowFuncCall(). I've added code to do that in the
attached version.
This got me wondering if the support function, instead of returning
some more optimal versions of the frameOptions, I wondered if it
should just return which aspects of the WindowClause it does not care
about. For example,
SELECT row_number() over (), lag(relname) over (order by relname)
from pg_class;
could, in theory, have row_number() reuse the WindowAgg for lag. Here
because the WindowClause for row_number() has an empty ORDER BY
clause, I believe it could just reuse the lag's WindowClause. It
wouldn't be able to do that if row_number() had an ORDER BY, or if
row_number() were some other WindowFunc that cared about peer rows.
I'm currently thinking this might not be worth the trouble as it seems
a bit unlikely that someone would use row_number() and not care about
the ORDER BY. However, maybe the row_number() could reuse some other
WindowClause with a more strict ordering. My current thoughts are that
this feels a bit too unlikely to apply in enough cases for it to be
worthwhile. I just thought I'd mention it for the sake of the
archives.
David
Thanks for taking it for a spin.
David
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 78a8174534..43468081f3 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -38,6 +38,7 @@
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "nodes/supportnodes.h"
#ifdef OPTIMIZER_DEBUG
#include "nodes/print.h"
#endif
@@ -207,6 +208,8 @@ static PathTarget *make_partial_grouping_target(PlannerInfo *root,
PathTarget *grouping_target,
Node *havingQual);
static List *postprocess_setop_tlist(List *new_tlist, List *orig_tlist);
+static void optimize_window_clauses(PlannerInfo *root,
+ WindowFuncLists *wflists);
static List *select_active_windows(PlannerInfo *root, WindowFuncLists *wflists);
static PathTarget *make_window_input_target(PlannerInfo *root,
PathTarget *final_target,
@@ -1422,7 +1425,16 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
wflists = find_window_functions((Node *) root->processed_tlist,
list_length(parse->windowClause));
if (wflists->numWindowFuncs > 0)
+ {
+ /*
+ * See if any modifications can be made to each WindowClause
+ * to allow the executor to execute the WindowFuncs more
+ * quickly.
+ */
+ optimize_window_clauses(root, wflists);
+
activeWindows = select_active_windows(root, wflists);
+ }
else
parse->hasWindowFuncs = false;
}
@@ -5391,6 +5403,150 @@ postprocess_setop_tlist(List *new_tlist, List *orig_tlist)
return new_tlist;
}
+/*
+ * optimize_window_clauses
+ * Call each WindowFunc's prosupport function to see if we're able to
+ * make any adjustments to any of the WindowClause's so that the executor
+ * can execute the window functions in a more optimal way.
+ *
+ * Currently we only allow adjustments to the WindowClause's frameOptions. We
+ * may allow more things to be done here in the future.
+ */
+static void
+optimize_window_clauses(PlannerInfo *root, WindowFuncLists *wflists)
+{
+ List *windowClause = root->parse->windowClause;
+ ListCell *lc;
+
+ foreach(lc, windowClause)
+ {
+ WindowClause *wc = lfirst_node(WindowClause, lc);
+ ListCell *lc2;
+ int optimizedFrameOptions = 0;
+
+ Assert(wc->winref <= wflists->maxWinRef);
+
+ /* skip any WindowClauses that have no WindowFuncs */
+ if (wflists->windowFuncs[wc->winref] == NIL)
+ continue;
+
+ foreach(lc2, wflists->windowFuncs[wc->winref])
+ {
+ SupportRequestOptimizeWindowClause req;
+ SupportRequestOptimizeWindowClause *res;
+ WindowFunc *wfunc = lfirst_node(WindowFunc, lc2);
+ Oid prosupport;
+
+ prosupport = get_func_support(wfunc->winfnoid);
+
+ /* Check if there's a support function for 'wfunc' */
+ if (!OidIsValid(prosupport))
+ break; /* can't optimize this WindowClause */
+
+ req.type = T_SupportRequestOptimizeWindowClause;
+ req.window_clause = wc;
+ req.window_func = wfunc;
+ req.frameOptions = wc->frameOptions;
+
+ /* call the support function */
+ res = (SupportRequestOptimizeWindowClause *)
+ DatumGetPointer(OidFunctionCall1(prosupport,
+ PointerGetDatum(&req)));
+
+ /*
+ * Skip to next WindowClause if the support function does not
+ * support this request type.
+ */
+ if (res == NULL)
+ break;
+
+ /*
+ * Save these frameOptions for the first WindowFunc for this
+ * WindowClause.
+ */
+ if (foreach_current_index(lc2) == 0)
+ optimizedFrameOptions = res->frameOptions;
+
+ /*
+ * On subsequent WindowFuncs, if the frameOptions are not the same
+ * then we're unable to optimize the frameOptions for this
+ * WindowClause.
+ */
+ else if (optimizedFrameOptions != res->frameOptions)
+ break; /* skip to the next WindowClause, if any */
+ }
+
+ /* adjust the frameOptions if all WindowFunc's agree that it's ok */
+ if (lc2 == NULL && wc->frameOptions != optimizedFrameOptions)
+ {
+ ListCell *lc3;
+
+ /* apply the new frame options */
+ wc->frameOptions = optimizedFrameOptions;
+
+ /*
+ * We now check to see if changing the frameOptions has caused
+ * this WindowClause to be a duplicate of some other WindowClause.
+ * This can only happen if we have multiple WindowClauses, so
+ * don't bother if there's only 1.
+ */
+ if (list_length(windowClause) == 1)
+ continue;
+
+ /*
+ * Do the duplicate check and reuse the existing WindowClause if
+ * we find a duplicate.
+ */
+ foreach(lc3, windowClause)
+ {
+ WindowClause *existing_wc = lfirst_node(WindowClause, lc3);
+
+ /* skip over the WindowClause we're currently editing */
+ if (existing_wc == wc)
+ continue;
+
+ /*
+ * Perform the same duplicate check that is done in
+ * transformWindowFuncCall.
+ */
+ if (equal(wc->partitionClause, existing_wc->partitionClause) &&
+ equal(wc->orderClause, existing_wc->orderClause) &&
+ wc->frameOptions == existing_wc->frameOptions &&
+ equal(wc->startOffset, existing_wc->startOffset) &&
+ equal(wc->endOffset, existing_wc->endOffset))
+ {
+ ListCell *lc4;
+
+ /*
+ * Now move each WindowFunc in 'wc' into 'existing_wc'.
+ * This required adjusting each WindowFunc's winref and
+ * moving the WindowFuncs in 'wc' to the list of
+ * WindowFuncs in 'existing_wc'.
+ */
+ foreach(lc4, wflists->windowFuncs[wc->winref])
+ {
+ WindowFunc *wfunc = lfirst_node(WindowFunc, lc4);
+
+ wfunc->winref = existing_wc->winref;
+ }
+
+ /* move list items */
+ wflists->windowFuncs[existing_wc->winref] = list_concat(wflists->windowFuncs[existing_wc->winref],
+ wflists->windowFuncs[wc->winref]);
+ wflists->windowFuncs[wc->winref] = NIL;
+
+ /*
+ * transformWindowFuncCall() should have made sure there
+ * are no other duplicates, so we needn't bother looking
+ * any further.
+ */
+ break;
+ }
+ }
+ }
+ }
+}
+
/*
* select_active_windows
* Create a list of the "active" window clauses (ie, those referenced
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 3ef9e8ee5e..8eec2088aa 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -1025,6 +1025,10 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc,
/* matched, no refname */ ;
else
continue;
+
+ /*
+ * Also see similar de-duplication code in optimize_window_clauses
+ */
if (equal(refwin->partitionClause, windef->partitionClause) &&
equal(refwin->orderClause, windef->orderClause) &&
refwin->frameOptions == windef->frameOptions &&
diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c
index 596564fa15..898f3abc0d 100644
--- a/src/backend/utils/adt/windowfuncs.c
+++ b/src/backend/utils/adt/windowfuncs.c
@@ -107,6 +107,24 @@ window_row_number_support(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(req);
}
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * The frame options can always become "ROWS BETWEEN UNBOUNDED
+ * PRECEDING AND CURRENT ROW". row_number() always just increments by
+ * 1 with each row in the partition. Using ROWS instead of RANGE
+ * saves effort checking peer rows during execution.
+ */
+ req->frameOptions = (FRAMEOPTION_NONDEFAULT |
+ FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
PG_RETURN_POINTER(NULL);
}
@@ -149,6 +167,27 @@ window_rank_support(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(req);
}
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * rank() is coded in such a way that it returns "(COUNT (*) OVER
+ * (<opt> RANGE UNBOUNDED PRECEDING) - COUNT (*) OVER (<opt> RANGE
+ * CURRENT ROW) + 1)" regardless of the frame options. We'll set the
+ * frame options to "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW"
+ * so they agree with what window_row_number_support() optimized the
+ * frame options to be. Using ROWS instead of RANGE saves from doing
+ * peer row checks during execution.
+ */
+ req->frameOptions = (FRAMEOPTION_NONDEFAULT |
+ FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
PG_RETURN_POINTER(NULL);
}
@@ -190,6 +229,24 @@ window_dense_rank_support(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(req);
}
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * dense_rank() is also unaffected by the frame options. Here we set
+ * the frame options to match what's done in row_number's support
+ * function. Using ROWS instead of RANGE (the default) saves the
+ * executor from having to check for peer rows.
+ */
+ req->frameOptions = (FRAMEOPTION_NONDEFAULT |
+ FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
PG_RETURN_POINTER(NULL);
}
@@ -222,6 +279,37 @@ window_percent_rank(PG_FUNCTION_ARGS)
PG_RETURN_FLOAT8((float8) (context->rank - 1) / (float8) (totalrows - 1));
}
+/*
+ * window_percent_rank_support
+ * prosupport function for window_percent_rank()
+ */
+Datum
+window_percent_rank_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * percent_rank() is also unaffected by the frame options. Here we
+ * set the frame options to match what's done in row_number's support
+ * function. Using ROWS instead of RANGE (the default) saves the
+ * executor from having to check for peer rows.
+ */
+ req->frameOptions = (FRAMEOPTION_NONDEFAULT |
+ FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
+ PG_RETURN_POINTER(NULL);
+}
+
+
/*
* cume_dist
* return fraction between 0 and 1 inclusive,
@@ -265,6 +353,36 @@ window_cume_dist(PG_FUNCTION_ARGS)
PG_RETURN_FLOAT8((float8) context->rank / (float8) totalrows);
}
+/*
+ * window_cume_dist_support
+ * prosupport function for window_cume_dist()
+ */
+Datum
+window_cume_dist_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * cume_dist() is also unaffected by the frame options. Here we set
+ * the frame options to match what's done in row_number's support
+ * function. Using ROWS instead of RANGE (the default) saves the
+ * executor from having to check for peer rows.
+ */
+ req->frameOptions = (FRAMEOPTION_NONDEFAULT |
+ FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
+ PG_RETURN_POINTER(NULL);
+}
+
/*
* ntile
* compute an exact numeric value with scale 0 (zero),
@@ -338,6 +456,36 @@ window_ntile(PG_FUNCTION_ARGS)
PG_RETURN_INT32(context->ntile);
}
+/*
+ * window_ntile_support
+ * prosupport function for window_ntile()
+ */
+Datum
+window_ntile_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * ntile() is also unaffected by the frame options. Here we set the
+ * frame options to match what's done in row_number's support
+ * function. Using ROWS instead of RANGE (the default) saves the
+ * executor from having to check for peer rows.
+ */
+ req->frameOptions = (FRAMEOPTION_NONDEFAULT |
+ FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
+ PG_RETURN_POINTER(NULL);
+}
+
/*
* leadlag_common
* common operation of lead() and lag()
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 62a5b8e655..ee01b2e3d0 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10137,32 +10137,41 @@
proname => 'row_number', prosupport => 'window_row_number_support',
prokind => 'w', proisstrict => 'f', prorettype => 'int8', proargtypes => '',
prosrc => 'window_row_number' },
-{ oid => '6233', descr => 'planner support for row_number run condition',
+{ oid => '6233', descr => 'planner support for row_number',
proname => 'window_row_number_support', prorettype => 'internal',
proargtypes => 'internal', prosrc => 'window_row_number_support' },
{ oid => '3101', descr => 'integer rank with gaps',
proname => 'rank', prosupport => 'window_rank_support', prokind => 'w',
proisstrict => 'f', prorettype => 'int8', proargtypes => '',
prosrc => 'window_rank' },
-{ oid => '6234', descr => 'planner support for rank run condition',
+{ oid => '6234', descr => 'planner support for rank',
proname => 'window_rank_support', prorettype => 'internal',
proargtypes => 'internal', prosrc => 'window_rank_support' },
{ oid => '3102', descr => 'integer rank without gaps',
proname => 'dense_rank', prosupport => 'window_dense_rank_support',
prokind => 'w', proisstrict => 'f', prorettype => 'int8', proargtypes => '',
prosrc => 'window_dense_rank' },
-{ oid => '6235', descr => 'planner support for dense rank run condition',
+{ oid => '6235', descr => 'planner support for dense_rank',
proname => 'window_dense_rank_support', prorettype => 'internal',
proargtypes => 'internal', prosrc => 'window_dense_rank_support' },
{ oid => '3103', descr => 'fractional rank within partition',
proname => 'percent_rank', prokind => 'w', proisstrict => 'f',
prorettype => 'float8', proargtypes => '', prosrc => 'window_percent_rank' },
+{ oid => '9773', descr => 'planner support for percent_rank',
+ proname => 'window_percent_rank_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'window_percent_rank_support' },
{ oid => '3104', descr => 'fractional row number within partition',
proname => 'cume_dist', prokind => 'w', proisstrict => 'f',
prorettype => 'float8', proargtypes => '', prosrc => 'window_cume_dist' },
+{ oid => '9774', descr => 'planner support for cume_dist',
+ proname => 'window_cume_dist_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'window_cume_dist_support' },
{ oid => '3105', descr => 'split rows into N groups',
proname => 'ntile', prokind => 'w', prorettype => 'int4',
proargtypes => 'int4', prosrc => 'window_ntile' },
+{ oid => '9775', descr => 'planner support for ntile',
+ proname => 'window_ntile_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'window_ntile_support' },
{ oid => '3106', descr => 'fetch the preceding row value',
proname => 'lag', prokind => 'w', prorettype => 'anyelement',
proargtypes => 'anyelement', prosrc => 'window_lag' },
diff --git a/src/include/nodes/supportnodes.h b/src/include/nodes/supportnodes.h
index 9fcbc39949..b446125b2b 100644
--- a/src/include/nodes/supportnodes.h
+++ b/src/include/nodes/supportnodes.h
@@ -299,4 +299,48 @@ typedef struct SupportRequestWFuncMonotonic
MonotonicFunction monotonic;
} SupportRequestWFuncMonotonic;
+/*
+ * Some WindowFunc behavior might not be affected by certain variations in
+ * the WindowClause's frameOptions. For example, row_number() is coded in
+ * such a way that the frame options don't change the returned row number.
+ * nodeWindowAgg.c will have less work to do if the ROWS option is used
+ * instead of the RANGE option as no check needs to be done for peer rows.
+ * Since RANGE is included in the default frame options, window functions
+ * such as row_number() might want to change that to ROW.
+ *
+ * Here we allow a WindowFunc's support function to determine which, if
+ * anything, can be changed about the WindowClause which the WindowFunc
+ * belongs to. Currently only the frameOptions can be modified. However,
+ * we may want to allow more optimizations in the future.
+ *
+ * The support function is responsible for ensuring the optimized version of
+ * the frameOptions doesn't affect the result of the window function. The
+ * planner is responsible for only changing the frame options when all
+ * WindowFuncs using this particular WindowClause agree on what the optimized
+ * version of the frameOptions are. If a particular WindowFunc being used
+ * does not have a support function then the planner will not make any changes
+ * to the WindowClause's frameOptions.
+ *
+ * 'window_func' and 'window_clause' are set by the planner before calling the
+ * support function so that the support function has these fields available.
+ * These may be required in order to determine which optimizations are
+ * possible.
+ *
+ * 'frameOptions' is set by the planner to WindowClause.frameOptions. The
+ * support function must only adjust this if optimizations are possible for
+ * the given WindowFunc.
+ */
+typedef struct SupportRequestOptimizeWindowClause
+{
+ NodeTag type;
+
+ /* Input fields: */
+ WindowFunc *window_func; /* Pointer to the window function data */
+ struct WindowClause *window_clause; /* Pointer to the window clause data */
+
+ /* Input/Output fields: */
+ int frameOptions; /* New frameOptions, or left untouched if no
+ * optimizations are possible. */
+} SupportRequestOptimizeWindowClause;
+
#endif /* SUPPORTNODES_H */
Attachments:
[text/plain] v4_optimize_windowfuncs_frameoptions_when_possible.patch (17.6K, ../../CAApHDvpkktr-OOact6BoT6pB-V5XdD4Fmj7a8wns68oWqzoFnw@mail.gmail.com/2-v4_optimize_windowfuncs_frameoptions_when_possible.patch)
download | inline diff:
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 78a8174534..43468081f3 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -38,6 +38,7 @@
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "nodes/supportnodes.h"
#ifdef OPTIMIZER_DEBUG
#include "nodes/print.h"
#endif
@@ -207,6 +208,8 @@ static PathTarget *make_partial_grouping_target(PlannerInfo *root,
PathTarget *grouping_target,
Node *havingQual);
static List *postprocess_setop_tlist(List *new_tlist, List *orig_tlist);
+static void optimize_window_clauses(PlannerInfo *root,
+ WindowFuncLists *wflists);
static List *select_active_windows(PlannerInfo *root, WindowFuncLists *wflists);
static PathTarget *make_window_input_target(PlannerInfo *root,
PathTarget *final_target,
@@ -1422,7 +1425,16 @@ grouping_planner(PlannerInfo *root, double tuple_fraction)
wflists = find_window_functions((Node *) root->processed_tlist,
list_length(parse->windowClause));
if (wflists->numWindowFuncs > 0)
+ {
+ /*
+ * See if any modifications can be made to each WindowClause
+ * to allow the executor to execute the WindowFuncs more
+ * quickly.
+ */
+ optimize_window_clauses(root, wflists);
+
activeWindows = select_active_windows(root, wflists);
+ }
else
parse->hasWindowFuncs = false;
}
@@ -5391,6 +5403,150 @@ postprocess_setop_tlist(List *new_tlist, List *orig_tlist)
return new_tlist;
}
+/*
+ * optimize_window_clauses
+ * Call each WindowFunc's prosupport function to see if we're able to
+ * make any adjustments to any of the WindowClause's so that the executor
+ * can execute the window functions in a more optimal way.
+ *
+ * Currently we only allow adjustments to the WindowClause's frameOptions. We
+ * may allow more things to be done here in the future.
+ */
+static void
+optimize_window_clauses(PlannerInfo *root, WindowFuncLists *wflists)
+{
+ List *windowClause = root->parse->windowClause;
+ ListCell *lc;
+
+ foreach(lc, windowClause)
+ {
+ WindowClause *wc = lfirst_node(WindowClause, lc);
+ ListCell *lc2;
+ int optimizedFrameOptions = 0;
+
+ Assert(wc->winref <= wflists->maxWinRef);
+
+ /* skip any WindowClauses that have no WindowFuncs */
+ if (wflists->windowFuncs[wc->winref] == NIL)
+ continue;
+
+ foreach(lc2, wflists->windowFuncs[wc->winref])
+ {
+ SupportRequestOptimizeWindowClause req;
+ SupportRequestOptimizeWindowClause *res;
+ WindowFunc *wfunc = lfirst_node(WindowFunc, lc2);
+ Oid prosupport;
+
+ prosupport = get_func_support(wfunc->winfnoid);
+
+ /* Check if there's a support function for 'wfunc' */
+ if (!OidIsValid(prosupport))
+ break; /* can't optimize this WindowClause */
+
+ req.type = T_SupportRequestOptimizeWindowClause;
+ req.window_clause = wc;
+ req.window_func = wfunc;
+ req.frameOptions = wc->frameOptions;
+
+ /* call the support function */
+ res = (SupportRequestOptimizeWindowClause *)
+ DatumGetPointer(OidFunctionCall1(prosupport,
+ PointerGetDatum(&req)));
+
+ /*
+ * Skip to next WindowClause if the support function does not
+ * support this request type.
+ */
+ if (res == NULL)
+ break;
+
+ /*
+ * Save these frameOptions for the first WindowFunc for this
+ * WindowClause.
+ */
+ if (foreach_current_index(lc2) == 0)
+ optimizedFrameOptions = res->frameOptions;
+
+ /*
+ * On subsequent WindowFuncs, if the frameOptions are not the same
+ * then we're unable to optimize the frameOptions for this
+ * WindowClause.
+ */
+ else if (optimizedFrameOptions != res->frameOptions)
+ break; /* skip to the next WindowClause, if any */
+ }
+
+ /* adjust the frameOptions if all WindowFunc's agree that it's ok */
+ if (lc2 == NULL && wc->frameOptions != optimizedFrameOptions)
+ {
+ ListCell *lc3;
+
+ /* apply the new frame options */
+ wc->frameOptions = optimizedFrameOptions;
+
+ /*
+ * We now check to see if changing the frameOptions has caused
+ * this WindowClause to be a duplicate of some other WindowClause.
+ * This can only happen if we have multiple WindowClauses, so
+ * don't bother if there's only 1.
+ */
+ if (list_length(windowClause) == 1)
+ continue;
+
+ /*
+ * Do the duplicate check and reuse the existing WindowClause if
+ * we find a duplicate.
+ */
+ foreach(lc3, windowClause)
+ {
+ WindowClause *existing_wc = lfirst_node(WindowClause, lc3);
+
+ /* skip over the WindowClause we're currently editing */
+ if (existing_wc == wc)
+ continue;
+
+ /*
+ * Perform the same duplicate check that is done in
+ * transformWindowFuncCall.
+ */
+ if (equal(wc->partitionClause, existing_wc->partitionClause) &&
+ equal(wc->orderClause, existing_wc->orderClause) &&
+ wc->frameOptions == existing_wc->frameOptions &&
+ equal(wc->startOffset, existing_wc->startOffset) &&
+ equal(wc->endOffset, existing_wc->endOffset))
+ {
+ ListCell *lc4;
+
+ /*
+ * Now move each WindowFunc in 'wc' into 'existing_wc'.
+ * This required adjusting each WindowFunc's winref and
+ * moving the WindowFuncs in 'wc' to the list of
+ * WindowFuncs in 'existing_wc'.
+ */
+ foreach(lc4, wflists->windowFuncs[wc->winref])
+ {
+ WindowFunc *wfunc = lfirst_node(WindowFunc, lc4);
+
+ wfunc->winref = existing_wc->winref;
+ }
+
+ /* move list items */
+ wflists->windowFuncs[existing_wc->winref] = list_concat(wflists->windowFuncs[existing_wc->winref],
+ wflists->windowFuncs[wc->winref]);
+ wflists->windowFuncs[wc->winref] = NIL;
+
+ /*
+ * transformWindowFuncCall() should have made sure there
+ * are no other duplicates, so we needn't bother looking
+ * any further.
+ */
+ break;
+ }
+ }
+ }
+ }
+}
+
/*
* select_active_windows
* Create a list of the "active" window clauses (ie, those referenced
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 3ef9e8ee5e..8eec2088aa 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -1025,6 +1025,10 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc,
/* matched, no refname */ ;
else
continue;
+
+ /*
+ * Also see similar de-duplication code in optimize_window_clauses
+ */
if (equal(refwin->partitionClause, windef->partitionClause) &&
equal(refwin->orderClause, windef->orderClause) &&
refwin->frameOptions == windef->frameOptions &&
diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c
index 596564fa15..898f3abc0d 100644
--- a/src/backend/utils/adt/windowfuncs.c
+++ b/src/backend/utils/adt/windowfuncs.c
@@ -107,6 +107,24 @@ window_row_number_support(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(req);
}
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * The frame options can always become "ROWS BETWEEN UNBOUNDED
+ * PRECEDING AND CURRENT ROW". row_number() always just increments by
+ * 1 with each row in the partition. Using ROWS instead of RANGE
+ * saves effort checking peer rows during execution.
+ */
+ req->frameOptions = (FRAMEOPTION_NONDEFAULT |
+ FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
PG_RETURN_POINTER(NULL);
}
@@ -149,6 +167,27 @@ window_rank_support(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(req);
}
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * rank() is coded in such a way that it returns "(COUNT (*) OVER
+ * (<opt> RANGE UNBOUNDED PRECEDING) - COUNT (*) OVER (<opt> RANGE
+ * CURRENT ROW) + 1)" regardless of the frame options. We'll set the
+ * frame options to "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW"
+ * so they agree with what window_row_number_support() optimized the
+ * frame options to be. Using ROWS instead of RANGE saves from doing
+ * peer row checks during execution.
+ */
+ req->frameOptions = (FRAMEOPTION_NONDEFAULT |
+ FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
PG_RETURN_POINTER(NULL);
}
@@ -190,6 +229,24 @@ window_dense_rank_support(PG_FUNCTION_ARGS)
PG_RETURN_POINTER(req);
}
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * dense_rank() is also unaffected by the frame options. Here we set
+ * the frame options to match what's done in row_number's support
+ * function. Using ROWS instead of RANGE (the default) saves the
+ * executor from having to check for peer rows.
+ */
+ req->frameOptions = (FRAMEOPTION_NONDEFAULT |
+ FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
PG_RETURN_POINTER(NULL);
}
@@ -222,6 +279,37 @@ window_percent_rank(PG_FUNCTION_ARGS)
PG_RETURN_FLOAT8((float8) (context->rank - 1) / (float8) (totalrows - 1));
}
+/*
+ * window_percent_rank_support
+ * prosupport function for window_percent_rank()
+ */
+Datum
+window_percent_rank_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * percent_rank() is also unaffected by the frame options. Here we
+ * set the frame options to match what's done in row_number's support
+ * function. Using ROWS instead of RANGE (the default) saves the
+ * executor from having to check for peer rows.
+ */
+ req->frameOptions = (FRAMEOPTION_NONDEFAULT |
+ FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
+ PG_RETURN_POINTER(NULL);
+}
+
+
/*
* cume_dist
* return fraction between 0 and 1 inclusive,
@@ -265,6 +353,36 @@ window_cume_dist(PG_FUNCTION_ARGS)
PG_RETURN_FLOAT8((float8) context->rank / (float8) totalrows);
}
+/*
+ * window_cume_dist_support
+ * prosupport function for window_cume_dist()
+ */
+Datum
+window_cume_dist_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * cume_dist() is also unaffected by the frame options. Here we set
+ * the frame options to match what's done in row_number's support
+ * function. Using ROWS instead of RANGE (the default) saves the
+ * executor from having to check for peer rows.
+ */
+ req->frameOptions = (FRAMEOPTION_NONDEFAULT |
+ FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
+ PG_RETURN_POINTER(NULL);
+}
+
/*
* ntile
* compute an exact numeric value with scale 0 (zero),
@@ -338,6 +456,36 @@ window_ntile(PG_FUNCTION_ARGS)
PG_RETURN_INT32(context->ntile);
}
+/*
+ * window_ntile_support
+ * prosupport function for window_ntile()
+ */
+Datum
+window_ntile_support(PG_FUNCTION_ARGS)
+{
+ Node *rawreq = (Node *) PG_GETARG_POINTER(0);
+
+ if (IsA(rawreq, SupportRequestOptimizeWindowClause))
+ {
+ SupportRequestOptimizeWindowClause *req = (SupportRequestOptimizeWindowClause *) rawreq;
+
+ /*
+ * ntile() is also unaffected by the frame options. Here we set the
+ * frame options to match what's done in row_number's support
+ * function. Using ROWS instead of RANGE (the default) saves the
+ * executor from having to check for peer rows.
+ */
+ req->frameOptions = (FRAMEOPTION_NONDEFAULT |
+ FRAMEOPTION_ROWS |
+ FRAMEOPTION_START_UNBOUNDED_PRECEDING |
+ FRAMEOPTION_END_CURRENT_ROW);
+
+ PG_RETURN_POINTER(req);
+ }
+
+ PG_RETURN_POINTER(NULL);
+}
+
/*
* leadlag_common
* common operation of lead() and lag()
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 62a5b8e655..ee01b2e3d0 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10137,32 +10137,41 @@
proname => 'row_number', prosupport => 'window_row_number_support',
prokind => 'w', proisstrict => 'f', prorettype => 'int8', proargtypes => '',
prosrc => 'window_row_number' },
-{ oid => '6233', descr => 'planner support for row_number run condition',
+{ oid => '6233', descr => 'planner support for row_number',
proname => 'window_row_number_support', prorettype => 'internal',
proargtypes => 'internal', prosrc => 'window_row_number_support' },
{ oid => '3101', descr => 'integer rank with gaps',
proname => 'rank', prosupport => 'window_rank_support', prokind => 'w',
proisstrict => 'f', prorettype => 'int8', proargtypes => '',
prosrc => 'window_rank' },
-{ oid => '6234', descr => 'planner support for rank run condition',
+{ oid => '6234', descr => 'planner support for rank',
proname => 'window_rank_support', prorettype => 'internal',
proargtypes => 'internal', prosrc => 'window_rank_support' },
{ oid => '3102', descr => 'integer rank without gaps',
proname => 'dense_rank', prosupport => 'window_dense_rank_support',
prokind => 'w', proisstrict => 'f', prorettype => 'int8', proargtypes => '',
prosrc => 'window_dense_rank' },
-{ oid => '6235', descr => 'planner support for dense rank run condition',
+{ oid => '6235', descr => 'planner support for dense_rank',
proname => 'window_dense_rank_support', prorettype => 'internal',
proargtypes => 'internal', prosrc => 'window_dense_rank_support' },
{ oid => '3103', descr => 'fractional rank within partition',
proname => 'percent_rank', prokind => 'w', proisstrict => 'f',
prorettype => 'float8', proargtypes => '', prosrc => 'window_percent_rank' },
+{ oid => '9773', descr => 'planner support for percent_rank',
+ proname => 'window_percent_rank_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'window_percent_rank_support' },
{ oid => '3104', descr => 'fractional row number within partition',
proname => 'cume_dist', prokind => 'w', proisstrict => 'f',
prorettype => 'float8', proargtypes => '', prosrc => 'window_cume_dist' },
+{ oid => '9774', descr => 'planner support for cume_dist',
+ proname => 'window_cume_dist_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'window_cume_dist_support' },
{ oid => '3105', descr => 'split rows into N groups',
proname => 'ntile', prokind => 'w', prorettype => 'int4',
proargtypes => 'int4', prosrc => 'window_ntile' },
+{ oid => '9775', descr => 'planner support for ntile',
+ proname => 'window_ntile_support', prorettype => 'internal',
+ proargtypes => 'internal', prosrc => 'window_ntile_support' },
{ oid => '3106', descr => 'fetch the preceding row value',
proname => 'lag', prokind => 'w', prorettype => 'anyelement',
proargtypes => 'anyelement', prosrc => 'window_lag' },
diff --git a/src/include/nodes/supportnodes.h b/src/include/nodes/supportnodes.h
index 9fcbc39949..b446125b2b 100644
--- a/src/include/nodes/supportnodes.h
+++ b/src/include/nodes/supportnodes.h
@@ -299,4 +299,48 @@ typedef struct SupportRequestWFuncMonotonic
MonotonicFunction monotonic;
} SupportRequestWFuncMonotonic;
+/*
+ * Some WindowFunc behavior might not be affected by certain variations in
+ * the WindowClause's frameOptions. For example, row_number() is coded in
+ * such a way that the frame options don't change the returned row number.
+ * nodeWindowAgg.c will have less work to do if the ROWS option is used
+ * instead of the RANGE option as no check needs to be done for peer rows.
+ * Since RANGE is included in the default frame options, window functions
+ * such as row_number() might want to change that to ROW.
+ *
+ * Here we allow a WindowFunc's support function to determine which, if
+ * anything, can be changed about the WindowClause which the WindowFunc
+ * belongs to. Currently only the frameOptions can be modified. However,
+ * we may want to allow more optimizations in the future.
+ *
+ * The support function is responsible for ensuring the optimized version of
+ * the frameOptions doesn't affect the result of the window function. The
+ * planner is responsible for only changing the frame options when all
+ * WindowFuncs using this particular WindowClause agree on what the optimized
+ * version of the frameOptions are. If a particular WindowFunc being used
+ * does not have a support function then the planner will not make any changes
+ * to the WindowClause's frameOptions.
+ *
+ * 'window_func' and 'window_clause' are set by the planner before calling the
+ * support function so that the support function has these fields available.
+ * These may be required in order to determine which optimizations are
+ * possible.
+ *
+ * 'frameOptions' is set by the planner to WindowClause.frameOptions. The
+ * support function must only adjust this if optimizations are possible for
+ * the given WindowFunc.
+ */
+typedef struct SupportRequestOptimizeWindowClause
+{
+ NodeTag type;
+
+ /* Input fields: */
+ WindowFunc *window_func; /* Pointer to the window function data */
+ struct WindowClause *window_clause; /* Pointer to the window clause data */
+
+ /* Input/Output fields: */
+ int frameOptions; /* New frameOptions, or left untouched if no
+ * optimizations are possible. */
+} SupportRequestOptimizeWindowClause;
+
#endif /* SUPPORTNODES_H */
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options
@ 2022-12-22 23:47 David Rowley <[email protected]>
parent: David Rowley <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: David Rowley @ 2022-12-22 23:47 UTC (permalink / raw)
To: Vik Fearing <[email protected]>; +Cc: PostgreSQL Developers <[email protected]>; Erwin Brandstetter <[email protected]>
On Wed, 26 Oct 2022 at 14:38, David Rowley <[email protected]> wrote:
>
> On Sun, 23 Oct 2022 at 03:03, Vik Fearing <[email protected]> wrote:
> > Shouldn't it be able to detect that these two windows are the same and
> > only do one WindowAgg pass?
> >
> >
> > explain (verbose, costs off)
> > select row_number() over w1,
> > lag(amname) over w2
> > from pg_am
> > window w1 as (order by amname),
> > w2 as (w1 rows unbounded preceding)
> > ;
>
> Good thinking. I think the patch should also optimise that case. It
> requires re-doing a similar de-duplication phase the same as what's
> done in transformWindowFuncCall(). I've added code to do that in the
> attached version.
I've spent a bit more time on this now and added a few extra
regression tests. The previous version had nothing to test to ensure
that an aggregate function being used as a window function does not
have its frame options changed when it's sharing the same WindowClause
as a WindowFunc which can have the frame options changed.
I've now pushed the final result. Thank you to everyone who provided
input on this.
David
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options
@ 2022-12-23 02:46 Vik Fearing <[email protected]>
parent: David Rowley <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: Vik Fearing @ 2022-12-23 02:46 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: PostgreSQL Developers <[email protected]>; Erwin Brandstetter <[email protected]>
On 12/23/22 00:47, David Rowley wrote:
> On Wed, 26 Oct 2022 at 14:38, David Rowley <[email protected]> wrote:
>
> I've now pushed the final result. Thank you to everyone who provided
> input on this.
This is a very good improvement. Thank you for working on it.
--
Vik Fearing
^ permalink raw reply [nested|flat] 17+ messages in thread
* [PATCH v20240830 1/1] Avoiding some memcpy, strlen, palloc in printtup.
@ 2024-08-30 04:50 Andy Fan <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: Andy Fan @ 2024-08-30 04:50 UTC (permalink / raw)
https://www.postgresql.org/message-id/87wmjzfz0h.fsf%40163.com
---
src/backend/access/common/printtup.c | 40 ++++++++++++++++++++++------
src/backend/utils/adt/oid.c | 20 ++++++++++++++
src/backend/utils/adt/varlena.c | 17 ++++++++++++
src/include/catalog/pg_proc.dat | 9 ++++++-
4 files changed, 77 insertions(+), 9 deletions(-)
diff --git a/src/backend/access/common/printtup.c b/src/backend/access/common/printtup.c
index c78cc39308..ecba4a7113 100644
--- a/src/backend/access/common/printtup.c
+++ b/src/backend/access/common/printtup.c
@@ -19,6 +19,7 @@
#include "libpq/pqformat.h"
#include "libpq/protocol.h"
#include "tcop/pquery.h"
+#include "utils/fmgroids.h"
#include "utils/lsyscache.h"
#include "utils/memdebug.h"
#include "utils/memutils.h"
@@ -49,6 +50,7 @@ typedef struct
bool typisvarlena; /* is it varlena (ie possibly toastable)? */
int16 format; /* format code for this column */
FmgrInfo finfo; /* Precomputed call info for output fn */
+ FmgrInfo p_finfo; /* Precomputed call info for print fn if any */
} PrinttupAttrInfo;
typedef struct
@@ -274,10 +276,25 @@ printtup_prepare_info(DR_printtup *myState, TupleDesc typeinfo, int numAttrs)
thisState->format = format;
if (format == 0)
{
- getTypeOutputInfo(attr->atttypid,
- &thisState->typoutput,
- &thisState->typisvarlena);
- fmgr_info(thisState->typoutput, &thisState->finfo);
+ /*
+ * If the type defines a print function, then use it
+ * rather than outfunction.
+ *
+ * XXX: need a generic function to improve the if-elseif.
+ */
+ if (attr->atttypid == OIDOID)
+ fmgr_info(F_OIDPRINT, &thisState->p_finfo);
+ else if (attr->atttypid == TEXTOID)
+ fmgr_info(F_TEXTPRINT, &thisState->p_finfo);
+ else
+ {
+ getTypeOutputInfo(attr->atttypid,
+ &thisState->typoutput,
+ &thisState->typisvarlena);
+ fmgr_info(thisState->typoutput, &thisState->finfo);
+ /* mark print function is invalid */
+ thisState->p_finfo.fn_oid = InvalidOid;
+ }
}
else if (format == 1)
{
@@ -355,10 +372,17 @@ printtup(TupleTableSlot *slot, DestReceiver *self)
if (thisState->format == 0)
{
/* Text output */
- char *outputstr;
-
- outputstr = OutputFunctionCall(&thisState->finfo, attr);
- pq_sendcountedtext(buf, outputstr, strlen(outputstr));
+ if (thisState->p_finfo.fn_oid)
+ {
+ FunctionCall2(&thisState->p_finfo, attr, PointerGetDatum(buf));
+ }
+ else
+ {
+ char *outputstr;
+
+ outputstr = OutputFunctionCall(&thisState->finfo, attr);
+ pq_sendcountedtext(buf, outputstr, strlen(outputstr));
+ }
}
else
{
diff --git a/src/backend/utils/adt/oid.c b/src/backend/utils/adt/oid.c
index 56fb1fd77c..cc85d920c8 100644
--- a/src/backend/utils/adt/oid.c
+++ b/src/backend/utils/adt/oid.c
@@ -53,6 +53,26 @@ oidout(PG_FUNCTION_ARGS)
PG_RETURN_CSTRING(result);
}
+Datum
+oidprint(PG_FUNCTION_ARGS)
+{
+ Oid o = PG_GETARG_OID(0);
+ StringInfo buf = (StringInfo) PG_GETARG_POINTER(1);
+ uint32 *lenp;
+ uint32 data_len;
+
+ /* 12 is the max length for an oid's text presentation. */
+ enlargeStringInfo(buf, sizeof(int32) + 12);
+
+ /* note the position for len */
+ lenp = (uint32 *) (buf->data + buf->len);
+ data_len = pg_snprintf(buf->data + buf->len + sizeof(int), 12, "%u", o);
+ *lenp = pg_hton32(data_len);
+ buf->len += sizeof(uint32) + data_len;
+
+ PG_RETURN_VOID();
+}
+
/*
* oidrecv - converts external binary format to oid
*/
diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c
index 7c6391a276..3b7006d54a 100644
--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -594,6 +594,23 @@ textout(PG_FUNCTION_ARGS)
PG_RETURN_CSTRING(TextDatumGetCString(txt));
}
+
+Datum
+textprint(PG_FUNCTION_ARGS)
+{
+ text *txt = (text *) pg_detoast_datum((struct varlena *)PG_GETARG_POINTER(0));
+ StringInfo buf = (StringInfo) PG_GETARG_POINTER(1);
+ uint32 text_len = VARSIZE(txt) - VARHDRSZ;
+ uint32 ni = pg_hton32(text_len);
+
+ enlargeStringInfo(buf, sizeof(int32) + text_len);
+ memcpy((char *pg_restrict) buf->data + buf->len, &ni, sizeof(uint32));
+ memcpy(buf->data + buf->len + sizeof(int), VARDATA(txt), text_len);
+ buf->len += sizeof(uint32) + text_len;
+
+ PG_RETURN_VOID();
+}
+
/*
* textrecv - converts external binary format to text
*/
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 85f42be1b3..74eeead4de 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -100,6 +100,10 @@
{ oid => '47', descr => 'I/O',
proname => 'textout', prorettype => 'cstring', proargtypes => 'text',
prosrc => 'textout' },
+{
+ oid => '8907', descr => 'I/O',
+ proname => 'textprint', prorettype => 'void', proargtypes => 'text internal',
+ prosrc => 'textprint' },
{ oid => '48', descr => 'I/O',
proname => 'tidin', prorettype => 'tid', proargtypes => 'cstring',
prosrc => 'tidin' },
@@ -4718,7 +4722,10 @@
{ oid => '1799', descr => 'I/O',
proname => 'oidout', prorettype => 'cstring', proargtypes => 'oid',
prosrc => 'oidout' },
-
+{
+ oid => '9771', descr => 'I/O',
+ proname => 'oidprint', prorettype => 'void', proargtypes => 'oid internal',
+ prosrc => 'oidprint'},
{ oid => '3058', descr => 'concatenate values',
proname => 'concat', provariadic => 'any', proisstrict => 'f',
provolatile => 's', prorettype => 'text', proargtypes => 'any',
--
2.45.1
--=-=-=--
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2024-08-30 04:50 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-12-18 16:19 [PATCH v42 1/2] Subscripting for jsonb Dmitrii Dolgov <[email protected]>
2022-10-12 02:40 Allow WindowFuncs prosupport function to use more optimal WindowClause options David Rowley <[email protected]>
2022-10-12 03:33 ` Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options Vik Fearing <[email protected]>
2022-10-12 04:03 ` Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options Erwin Brandstetter <[email protected]>
2022-10-13 00:34 ` Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options David Rowley <[email protected]>
2022-10-13 21:51 ` Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options Zhihong Yu <[email protected]>
2022-10-18 00:05 ` Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options David Rowley <[email protected]>
2022-10-18 02:40 ` Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options Zhihong Yu <[email protected]>
2022-10-17 22:10 ` Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options Erwin Brandstetter <[email protected]>
2022-10-17 23:18 ` Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options Tom Lane <[email protected]>
2022-10-17 23:58 ` Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options David Rowley <[email protected]>
2022-10-20 20:02 ` Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options David Rowley <[email protected]>
2022-10-22 14:03 ` Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options Vik Fearing <[email protected]>
2022-10-26 01:38 ` Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options David Rowley <[email protected]>
2022-12-22 23:47 ` Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options David Rowley <[email protected]>
2022-12-23 02:46 ` Re: Allow WindowFuncs prosupport function to use more optimal WindowClause options Vik Fearing <[email protected]>
2024-08-30 04:50 [PATCH v20240830 1/1] Avoiding some memcpy, strlen, palloc in printtup. Andy Fan <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox