WIP Patch: Precalculate stable functions
Hello everyone!
Now in Postgresql only immutable functions are precalculated; stable
functions are calculated for every row so in fact they don't differ from
volatile functions.
There's a proposal to precalculate stable and immutable functions (=
calculate once for all output rows, but as many times as function is
mentioned in query), if they don't return a set and their arguments are
constants or recursively precalculated functions. The same for
operators' functions, strict functions, tracking functions. It can be
very effective, for example, there's a comparison for full text search
in messages (Intel® Core™ i5-6500 CPU @ 3.20GHz × 4, RAM 8Gb):
Without precalculation:
EXPLAIN (ANALYZE TRUE, BUFFERS TRUE) SELECT COUNT(*) FROM messages WHERE
body_tsvector @@ to_tsquery('postgres');
QUERY
PLAN
------------------------------------------------------------------------------------------------------
------------------------------------
Aggregate (cost=18714.82..18714.83 rows=1 width=8) (actual
time=2275.334..2275.334 rows=1 loops=1)
Buffers: shared hit=309234 read=184261
-> Bitmap Heap Scan on messages (cost=66.93..18702.34 rows=4991
width=0) (actual time=70.661..224
7.462 rows=151967 loops=1)
Recheck Cond: (body_tsvector @@ to_tsquery('postgres'::text))
Rows Removed by Index Recheck: 118531
Heap Blocks: exact=56726 lossy=33286
Buffers: shared hit=309234 read=184261
-> Bitmap Index Scan on message_body_idx (cost=0.00..65.68
rows=4991 width=0) (actual time=
54.599..54.599 rows=151967 loops=1)
Index Cond: (body_tsvector @@
to_tsquery('postgres'::text))
Buffers: shared hit=1 read=37
Planning time: 0.493 ms
Execution time: 2276.412 ms
(12 rows)
With precalculation:
EXPLAIN (ANALYZE TRUE, BUFFERS TRUE) SELECT COUNT(*) FROM messages WHERE
body_tsvector @@ to_tsquery('postgres');
QUERY
PLAN
------------------------------------------------------------------------------------------------------
----------------------------------------
Aggregate (cost=192269.70..192269.71 rows=1 width=8) (actual
time=1458.679..1458.680 rows=1 loops=1)
Buffers: shared hit=309234 read=184261
-> Bitmap Heap Scan on messages (cost=1445.68..191883.51
rows=154474 width=0) (actual time=70.069
..1433.999 rows=151967 loops=1)
Recheck Cond: (body_tsvector @@ to_tsquery('postgres'::text))
Rows Removed by Index Recheck: 118531
Heap Blocks: exact=56726 lossy=33286
Buffers: shared hit=309234 read=184261
-> Bitmap Index Scan on message_body_idx (cost=0.00..1406.81
rows=154474 width=0) (actual t
ime=56.149..56.149 rows=151967 loops=1)
Index Cond: (body_tsvector @@
to_tsquery('postgres'::text))
Buffers: shared hit=1 read=37
Planning time: 1.644 ms
Execution time: 1459.836 ms
(12 rows)
Patch is attached. It isn't done yet:
- changing documentation (partly because of next lines);
- precalculation of expressions IS DISTINCT FROM and NULLIF which use
nonvolatile equality operators;
- precalculation of expressions "scalar op ANY/ALL (array)" which use
nonvolatile operators;
- precalculation of row compare expressions which use nonvolatile
operators.
--
Marina Polyakova
Postgres Professional: http://www.postgrespro.com
+7 926 92 00 265
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Sorry, attached patch.
-------- Исходное сообщение --------
Тема: WIP Patch: Precalculate stable functions
Дата: 20-04-2017 19:56
От: Marina Polyakova <m.polyakova@postgrespro.ru>
Кому: pgsql-hackers@postgresql.org
Hello everyone!
Now in Postgresql only immutable functions are precalculated; stable
functions are calculated for every row so in fact they don't differ from
volatile functions.
There's a proposal to precalculate stable and immutable functions (=
calculate once for all output rows, but as many times as function is
mentioned in query), if they don't return a set and their arguments are
constants or recursively precalculated functions. The same for
operators' functions, strict functions, tracking functions. It can be
very effective, for example, there's a comparison for full text search
in messages (Intel® Core™ i5-6500 CPU @ 3.20GHz × 4, RAM 8Gb):
Without precalculation:
EXPLAIN (ANALYZE TRUE, BUFFERS TRUE) SELECT COUNT(*) FROM messages WHERE
body_tsvector @@ to_tsquery('postgres');
QUERY
PLAN
------------------------------------------------------------------------------------------------------
------------------------------------
Aggregate (cost=18714.82..18714.83 rows=1 width=8) (actual
time=2275.334..2275.334 rows=1 loops=1)
Buffers: shared hit=309234 read=184261
-> Bitmap Heap Scan on messages (cost=66.93..18702.34 rows=4991
width=0) (actual time=70.661..224
7.462 rows=151967 loops=1)
Recheck Cond: (body_tsvector @@ to_tsquery('postgres'::text))
Rows Removed by Index Recheck: 118531
Heap Blocks: exact=56726 lossy=33286
Buffers: shared hit=309234 read=184261
-> Bitmap Index Scan on message_body_idx (cost=0.00..65.68
rows=4991 width=0) (actual time=
54.599..54.599 rows=151967 loops=1)
Index Cond: (body_tsvector @@
to_tsquery('postgres'::text))
Buffers: shared hit=1 read=37
Planning time: 0.493 ms
Execution time: 2276.412 ms
(12 rows)
With precalculation:
EXPLAIN (ANALYZE TRUE, BUFFERS TRUE) SELECT COUNT(*) FROM messages WHERE
body_tsvector @@ to_tsquery('postgres');
QUERY
PLAN
------------------------------------------------------------------------------------------------------
----------------------------------------
Aggregate (cost=192269.70..192269.71 rows=1 width=8) (actual
time=1458.679..1458.680 rows=1 loops=1)
Buffers: shared hit=309234 read=184261
-> Bitmap Heap Scan on messages (cost=1445.68..191883.51
rows=154474 width=0) (actual time=70.069
..1433.999 rows=151967 loops=1)
Recheck Cond: (body_tsvector @@ to_tsquery('postgres'::text))
Rows Removed by Index Recheck: 118531
Heap Blocks: exact=56726 lossy=33286
Buffers: shared hit=309234 read=184261
-> Bitmap Index Scan on message_body_idx (cost=0.00..1406.81
rows=154474 width=0) (actual t
ime=56.149..56.149 rows=151967 loops=1)
Index Cond: (body_tsvector @@
to_tsquery('postgres'::text))
Buffers: shared hit=1 read=37
Planning time: 1.644 ms
Execution time: 1459.836 ms
(12 rows)
Patch is attached. It isn't done yet:
- changing documentation (partly because of next lines);
- precalculation of expressions IS DISTINCT FROM and NULLIF which use
nonvolatile equality operators;
- precalculation of expressions "scalar op ANY/ALL (array)" which use
nonvolatile operators;
- precalculation of row compare expressions which use nonvolatile
operators.
--
Marina Polyakova
Postgres Professional: http://www.postgrespro.com
+7 926 92 00 265
Attachments:
Precalculate-stable-functions.patchtext/x-diff; name=Precalculate-stable-functions.patchDownload
From 46d590281129083d524751805797ef0a3c386df0 Mon Sep 17 00:00:00 2001
From: Marina Polyakova <m.polyakova@postgrespro.ru>
Date: Thu, 20 Apr 2017 19:23:05 +0300
Subject: [PATCH 2/2] Precalculate stable functions
Now in Postgresql only immutable functions are precalculated; stable functions
are calculated for every row so in fact they don't differ from volatile
functions.
In this patch function / operator is precalculated if:
1) it doesn't return set,
2) it's not volatile itself,
3) its arguments are constants or nonvolatile too (functions or operators).
Costs are changed to reflect the changed behaviour.
---
src/backend/executor/execExpr.c | 41 ++
src/backend/executor/execExprInterp.c | 196 +++++-
src/backend/optimizer/path/costsize.c | 84 ++-
src/include/fmgr.h | 4 +
src/include/nodes/primnodes.h | 2 +
.../expected/precalculate_stable_functions.out | 784 +++++++++++++++++++++
src/test/regress/serial_schedule | 1 +
.../regress/sql/precalculate_stable_functions.sql | 240 +++++++
8 files changed, 1321 insertions(+), 31 deletions(-)
create mode 100644 src/test/regress/expected/precalculate_stable_functions.out
create mode 100644 src/test/regress/sql/precalculate_stable_functions.sql
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index 15d693f..8ba1dcf 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -2061,6 +2061,22 @@ ExecInitFunc(ExprEvalStep *scratch, Expr *node, List *args, Oid funcid,
int argno;
ListCell *lc;
+ /*
+ * Function is not volatile if:
+ * 1) it doesn't return set,
+ * 2) it's not volatile itself,
+ * 3) its arguments are constants or nonvolatile too (functions or
+ * operators).
+ *
+ * Operator is not volatile if:
+ * 1) its function doesn't return set,
+ * 1) its function is not volatile itself,
+ * 3) its arguments are constants or nonvolatile too (functions or
+ * operators).
+ */
+ bool is_volatile;
+ bool has_nonconst_or_volatile_input = false;
+
/* Check permission to call function */
aclresult = pg_proc_aclcheck(funcid, GetUserId(), ACL_EXECUTE);
if (aclresult != ACLCHECK_OK)
@@ -2110,12 +2126,16 @@ ExecInitFunc(ExprEvalStep *scratch, Expr *node, List *args, Oid funcid,
foreach(lc, args)
{
Expr *arg = (Expr *) lfirst(lc);
+ bool arg_is_nonvolatile_function,
+ arg_is_nonvolatile_operator;
if (IsA(arg, Const))
{
/*
* Don't evaluate const arguments every round; especially
* interesting for constants in comparisons.
+ *
+ * Const arg doesn't affect value of has_nonconst_or_volatile_input.
*/
Const *con = (Const *) arg;
@@ -2126,10 +2146,31 @@ ExecInitFunc(ExprEvalStep *scratch, Expr *node, List *args, Oid funcid,
{
ExecInitExprRec(arg, parent, state,
&fcinfo->arg[argno], &fcinfo->argnull[argno]);
+
+ /* arg is not const, but it may be volatile function or operator */
+ arg_is_nonvolatile_function = IsA(arg, FuncExpr) &&
+ !((const FuncExpr *) arg)->isVolatile;
+ arg_is_nonvolatile_operator = IsA(arg, OpExpr) &&
+ !((const OpExpr *) arg)->isVolatile;
+ if (!(arg_is_nonvolatile_function || arg_is_nonvolatile_operator))
+ has_nonconst_or_volatile_input = true;
}
argno++;
}
+ is_volatile = has_nonconst_or_volatile_input ||
+ contain_volatile_functions((Node *) node);
+ /* for recursive check of volatile functions or operators */
+ if (IsA(node, FuncExpr))
+ ((FuncExpr *) node)->isVolatile = is_volatile;
+ else if (IsA(node, OpExpr))
+ ((OpExpr *) node)->isVolatile = is_volatile;
+ /* for execution of this expression */
+ fcinfo->isVolatile = is_volatile;
+ fcinfo->isExecuted = false;
+ fcinfo->nonVolatileResultIsNull = false;
+ fcinfo->nonVolatileResult = (Datum) 0;
+
/* Insert appropriate opcode depending on strictness and stats level */
if (pgstat_track_functions <= flinfo->fn_stats)
{
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index fed0052..7220f61 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -279,6 +279,7 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
TupleTableSlot *innerslot;
TupleTableSlot *outerslot;
TupleTableSlot *scanslot;
+ MemoryContext oldContext;
/*
* This array has to be in the same order as enum ExprEvalOp.
@@ -646,9 +647,40 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
{
FunctionCallInfo fcinfo = op->d.func.fcinfo_data;
- fcinfo->isnull = false;
- *op->resvalue = (op->d.func.fn_addr) (fcinfo);
- *op->resnull = fcinfo->isnull;
+ if (!fcinfo->isVolatile && fcinfo->isExecuted)
+ {
+ /* use saved result */
+ fcinfo->isnull = fcinfo->nonVolatileResultIsNull;
+ *op->resvalue = fcinfo->nonVolatileResult;
+ *op->resnull = fcinfo->isnull;
+ }
+ else
+ {
+ /*
+ * If function is not volatile then switch per-query memory
+ * context. It is necessary to save result between all tuples.
+ */
+ if (!fcinfo->isVolatile)
+ oldContext = MemoryContextSwitchTo(
+ econtext->ecxt_per_query_memory);
+
+ /* execute function as usual */
+ fcinfo->isnull = false;
+ *op->resvalue = (op->d.func.fn_addr) (fcinfo);
+ *op->resnull = fcinfo->isnull;
+
+ /*
+ * Save result for nonvolatile functions and switch memory
+ * context back.
+ */
+ if (!fcinfo->isVolatile)
+ {
+ fcinfo->nonVolatileResultIsNull = fcinfo->isnull;
+ fcinfo->nonVolatileResult = *op->resvalue;
+ fcinfo->isExecuted = true;
+ MemoryContextSwitchTo(oldContext);
+ }
+ }
EEO_NEXT();
}
@@ -659,18 +691,58 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
bool *argnull = fcinfo->argnull;
int argno;
- /* strict function, so check for NULL args */
- for (argno = 0; argno < op->d.func.nargs; argno++)
+ if (!fcinfo->isVolatile && fcinfo->isExecuted)
+ {
+ /* use saved result */
+ fcinfo->isnull = fcinfo->nonVolatileResultIsNull;
+ if (!fcinfo->isnull)
+ *op->resvalue = fcinfo->nonVolatileResult;
+ *op->resnull = fcinfo->isnull;
+ }
+ else
{
- if (argnull[argno])
+ /* strict function, so check for NULL args */
+ for (argno = 0; argno < op->d.func.nargs; argno++)
{
- *op->resnull = true;
- goto strictfail;
+ if (argnull[argno])
+ {
+ *op->resnull = true;
+
+ if (!fcinfo->isVolatile)
+ {
+ fcinfo->nonVolatileResultIsNull = *op->resnull;
+ fcinfo->isExecuted = true;
+ }
+
+ goto strictfail;
+ }
}
+
+ /*
+ * If function is not volatile then switch per-query memory
+ * context. It is necessary to save result between all tuples.
+ */
+ if (!fcinfo->isVolatile)
+ oldContext = MemoryContextSwitchTo(
+ econtext->ecxt_per_query_memory);
+
+ /* execute function as usual */
+ fcinfo->isnull = false;
+ *op->resvalue = (op->d.func.fn_addr) (fcinfo);
+ *op->resnull = fcinfo->isnull;
+
+ /*
+ * Save result for nonvolatile functions and switch memory
+ * context back.
+ */
+ if (!fcinfo->isVolatile)
+ {
+ fcinfo->nonVolatileResultIsNull = fcinfo->isnull;
+ fcinfo->nonVolatileResult = *op->resvalue;
+ fcinfo->isExecuted = true;
+ MemoryContextSwitchTo(oldContext);
+ }
}
- fcinfo->isnull = false;
- *op->resvalue = (op->d.func.fn_addr) (fcinfo);
- *op->resnull = fcinfo->isnull;
strictfail:
EEO_NEXT();
@@ -681,13 +753,44 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
FunctionCallInfo fcinfo = op->d.func.fcinfo_data;
PgStat_FunctionCallUsage fcusage;
- pgstat_init_function_usage(fcinfo, &fcusage);
+ if (!fcinfo->isVolatile && fcinfo->isExecuted)
+ {
+ /* use saved result */
+ fcinfo->isnull = fcinfo->nonVolatileResultIsNull;
+ *op->resvalue = fcinfo->nonVolatileResult;
+ *op->resnull = fcinfo->isnull;
+ }
+ else
+ {
+ pgstat_init_function_usage(fcinfo, &fcusage);
- fcinfo->isnull = false;
- *op->resvalue = (op->d.func.fn_addr) (fcinfo);
- *op->resnull = fcinfo->isnull;
+ /*
+ * If function is not volatile then switch per-query memory
+ * context. It is necessary to save result between all tuples.
+ */
+ if (!fcinfo->isVolatile)
+ oldContext = MemoryContextSwitchTo(
+ econtext->ecxt_per_query_memory);
- pgstat_end_function_usage(&fcusage, true);
+ /* execute function as usual */
+ fcinfo->isnull = false;
+ *op->resvalue = (op->d.func.fn_addr) (fcinfo);
+ *op->resnull = fcinfo->isnull;
+
+ /*
+ * Save result for nonvolatile functions and switch memory
+ * context back.
+ */
+ if (!fcinfo->isVolatile)
+ {
+ fcinfo->nonVolatileResultIsNull = fcinfo->isnull;
+ fcinfo->nonVolatileResult = *op->resvalue;
+ fcinfo->isExecuted = true;
+ MemoryContextSwitchTo(oldContext);
+ }
+
+ pgstat_end_function_usage(&fcusage, true);
+ }
EEO_NEXT();
}
@@ -699,23 +802,62 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
bool *argnull = fcinfo->argnull;
int argno;
- /* strict function, so check for NULL args */
- for (argno = 0; argno < op->d.func.nargs; argno++)
+ if (!fcinfo->isVolatile && fcinfo->isExecuted)
+ {
+ /* use saved result */
+ fcinfo->isnull = fcinfo->nonVolatileResultIsNull;
+ if (!fcinfo->isnull)
+ *op->resvalue = fcinfo->nonVolatileResult;
+ *op->resnull = fcinfo->isnull;
+ }
+ else
{
- if (argnull[argno])
+ /* strict function, so check for NULL args */
+ for (argno = 0; argno < op->d.func.nargs; argno++)
{
- *op->resnull = true;
- goto strictfail_fusage;
+ if (argnull[argno])
+ {
+ *op->resnull = true;
+
+ if (!fcinfo->isVolatile)
+ {
+ fcinfo->nonVolatileResultIsNull = *op->resnull;
+ fcinfo->isExecuted = true;
+ }
+
+ goto strictfail_fusage;
+ }
}
- }
- pgstat_init_function_usage(fcinfo, &fcusage);
+ pgstat_init_function_usage(fcinfo, &fcusage);
- fcinfo->isnull = false;
- *op->resvalue = (op->d.func.fn_addr) (fcinfo);
- *op->resnull = fcinfo->isnull;
+ /*
+ * If function is not volatile then switch per-query memory
+ * context. It is necessary to save result between all tuples.
+ */
+ if (!fcinfo->isVolatile)
+ oldContext = MemoryContextSwitchTo(
+ econtext->ecxt_per_query_memory);
- pgstat_end_function_usage(&fcusage, true);
+ /* execute function as usual */
+ fcinfo->isnull = false;
+ *op->resvalue = (op->d.func.fn_addr) (fcinfo);
+ *op->resnull = fcinfo->isnull;
+
+ /*
+ * Save result for nonvolatile functions and switch memory
+ * context back.
+ */
+ if (!fcinfo->isVolatile)
+ {
+ fcinfo->nonVolatileResultIsNull = fcinfo->isnull;
+ fcinfo->nonVolatileResult = *op->resvalue;
+ fcinfo->isExecuted = true;
+ MemoryContextSwitchTo(oldContext);
+ }
+
+ pgstat_end_function_usage(&fcusage, true);
+ }
strictfail_fusage:
EEO_NEXT();
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 52643d0..310ca0f 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -3539,11 +3539,87 @@ cost_qual_eval_walker(Node *node, cost_qual_eval_context *context)
*/
if (IsA(node, FuncExpr))
{
- context->total.per_tuple +=
- get_func_cost(((FuncExpr *) node)->funcid) * cpu_operator_cost;
+ /* firstly recurse into children */
+ bool children_result = expression_tree_walker(node,
+ cost_qual_eval_walker,
+ (void *) context);
+ FuncExpr *funcexpr = (FuncExpr *) node;
+ ListCell *arg;
+ bool has_nonconst_or_volatile_input = false;
+ double func_cost = get_func_cost(funcexpr->funcid) *
+ cpu_operator_cost;
+ bool func_returns_set = funcexpr->funcretset ||
+ expression_returns_set((Node *) funcexpr->args);
+
+ foreach(arg, funcexpr->args) {
+ void *arg_lfirst = lfirst(arg);
+ bool arg_is_const = IsA(arg_lfirst, Const);
+ bool arg_is_nonvolatile_function = IsA(arg_lfirst, FuncExpr) &&
+ !((const FuncExpr *) arg_lfirst)->isVolatile;
+ bool arg_is_nonvolatile_operator = IsA(arg_lfirst, OpExpr) &&
+ !((const OpExpr *) arg_lfirst)->isVolatile;
+ if (!(arg_is_const ||
+ arg_is_nonvolatile_function ||
+ arg_is_nonvolatile_operator))
+ has_nonconst_or_volatile_input = true;
+ }
+
+ funcexpr->isVolatile = func_returns_set ||
+ has_nonconst_or_volatile_input ||
+ contain_volatile_functions((Node *) &funcexpr->xpr);
+
+ if (funcexpr->isVolatile)
+ context->total.per_tuple += func_cost;
+ else
+ context->total.startup += func_cost;
+
+ return children_result;
+ }
+ else if (IsA(node, OpExpr))
+ {
+ bool children_result;
+ OpExpr *opexpr = (OpExpr *) node;
+ ListCell *arg;
+ bool has_nonconst_or_volatile_input = false;
+ double func_cost;
+ bool op_returns_set;
+
+ /* rely on struct equivalence to treat these all alike */
+ set_opfuncid(opexpr);
+
+ /* firstly recurse into children */
+ children_result = expression_tree_walker(node, cost_qual_eval_walker,
+ (void *) context);
+ op_returns_set = opexpr->opretset ||
+ expression_returns_set((Node *) opexpr->args);
+
+ func_cost = get_func_cost(opexpr->opfuncid) * cpu_operator_cost;
+
+ foreach(arg, opexpr->args) {
+ void *arg_lfirst = lfirst(arg);
+ bool arg_is_const = IsA(arg_lfirst, Const);
+ bool arg_is_nonvolatile_function = IsA(arg_lfirst, FuncExpr) &&
+ !((const FuncExpr *) arg_lfirst)->isVolatile;
+ bool arg_is_nonvolatile_operator = IsA(arg_lfirst, OpExpr) &&
+ !((const OpExpr *) arg_lfirst)->isVolatile;
+ if (!(arg_is_const ||
+ arg_is_nonvolatile_function ||
+ arg_is_nonvolatile_operator))
+ has_nonconst_or_volatile_input = true;
+ }
+
+ opexpr->isVolatile = op_returns_set ||
+ has_nonconst_or_volatile_input ||
+ contain_volatile_functions((Node *) &opexpr->xpr);
+
+ if (opexpr->isVolatile)
+ context->total.per_tuple += func_cost;
+ else
+ context->total.startup += func_cost;
+
+ return children_result;
}
- else if (IsA(node, OpExpr) ||
- IsA(node, DistinctExpr) ||
+ else if (IsA(node, DistinctExpr) ||
IsA(node, NullIfExpr))
{
/* rely on struct equivalence to treat these all alike */
diff --git a/src/include/fmgr.h b/src/include/fmgr.h
index cfb7b77..57b89eb 100644
--- a/src/include/fmgr.h
+++ b/src/include/fmgr.h
@@ -84,6 +84,10 @@ typedef struct FunctionCallInfoData
short nargs; /* # arguments actually passed */
Datum arg[FUNC_MAX_ARGS]; /* Arguments passed to function */
bool argnull[FUNC_MAX_ARGS]; /* T if arg[i] is actually NULL */
+ bool isVolatile;
+ bool isExecuted; /* for nonvolatile functions */
+ bool nonVolatileResultIsNull;
+ Datum nonVolatileResult;
} FunctionCallInfoData;
/*
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index b87fe84..d3ba632 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -455,6 +455,7 @@ typedef struct FuncExpr
Oid inputcollid; /* OID of collation that function should use */
List *args; /* arguments to the function */
int location; /* token location, or -1 if unknown */
+ bool isVolatile;
} FuncExpr;
/*
@@ -500,6 +501,7 @@ typedef struct OpExpr
Oid inputcollid; /* OID of collation that operator should use */
List *args; /* arguments to the operator (1 or 2) */
int location; /* token location, or -1 if unknown */
+ bool isVolatile;
} OpExpr;
/*
diff --git a/src/test/regress/expected/precalculate_stable_functions.out b/src/test/regress/expected/precalculate_stable_functions.out
new file mode 100644
index 0000000..f3f26d5
--- /dev/null
+++ b/src/test/regress/expected/precalculate_stable_functions.out
@@ -0,0 +1,784 @@
+--
+-- PRECALCULATE STABLE FUNCTIONS
+--
+-- Create volatile functions for testing
+CREATE OR REPLACE FUNCTION public.x_vlt (
+)
+RETURNS integer VOLATILE AS
+$body$
+BEGIN
+ RAISE NOTICE 'v';
+ RETURN 1;
+END;
+$body$
+LANGUAGE 'plpgsql';
+CREATE OR REPLACE FUNCTION public.equal_integers_vlt (
+ integer,
+ integer
+)
+RETURNS boolean VOLATILE AS
+$body$
+BEGIN
+ RAISE NOTICE 'equal integers volatile';
+ RETURN $1 = $2;
+END;
+$body$
+LANGUAGE 'plpgsql';
+-- Create stable functions for testing
+CREATE OR REPLACE FUNCTION public.x_stl (
+)
+RETURNS integer STABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 's';
+ RETURN 1;
+END;
+$body$
+LANGUAGE 'plpgsql';
+CREATE OR REPLACE FUNCTION public.x_stl2 (
+ integer
+)
+RETURNS integer STABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 's2';
+ RETURN $1;
+END;
+$body$
+LANGUAGE 'plpgsql';
+CREATE OR REPLACE FUNCTION public.x_stl2_strict (
+ integer
+)
+RETURNS integer STABLE STRICT AS
+$body$
+BEGIN
+ RAISE NOTICE 's2 strict';
+ RETURN $1;
+END;
+$body$
+LANGUAGE 'plpgsql';
+CREATE OR REPLACE FUNCTION public.equal_integers_stl (
+ integer,
+ integer
+)
+RETURNS boolean STABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 'equal integers stable';
+ RETURN $1 = $2;
+END;
+$body$
+LANGUAGE 'plpgsql';
+CREATE OR REPLACE FUNCTION public.x_stl2_boolean (
+ boolean
+)
+RETURNS boolean STABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 's2 boolean';
+ RETURN $1;
+END;
+$body$
+LANGUAGE 'plpgsql';
+CREATE OR REPLACE FUNCTION public.equal_booleans_stl_strict (
+ boolean,
+ boolean
+)
+RETURNS boolean STABLE STRICT AS
+$body$
+BEGIN
+ RAISE NOTICE 'equal booleans stable strict';
+ RETURN $1 = $2;
+END;
+$body$
+LANGUAGE 'plpgsql';
+-- Create immutable functions for testing
+CREATE OR REPLACE FUNCTION public.x_imm2 (
+ integer
+)
+RETURNS integer IMMUTABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 'i2';
+ RETURN $1;
+END;
+$body$
+LANGUAGE 'plpgsql';
+CREATE OR REPLACE FUNCTION public.x_imm2_strict (
+ integer
+)
+RETURNS integer IMMUTABLE STRICT AS
+$body$
+BEGIN
+ RAISE NOTICE 'i2 strict';
+ RETURN $1;
+END;
+$body$
+LANGUAGE 'plpgsql';
+CREATE OR REPLACE FUNCTION public.equal_integers_imm (
+ integer,
+ integer
+)
+RETURNS boolean IMMUTABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 'equal integers immutable';
+ RETURN $1 = $2;
+END;
+$body$
+LANGUAGE 'plpgsql';
+-- Create operators for testing
+CREATE operator === (PROCEDURE = equal_integers_vlt, LEFTARG = integer, RIGHTARG = integer);
+CREATE operator ==== (PROCEDURE = equal_integers_stl, LEFTARG = integer, RIGHTARG = integer);
+CREATE operator ===== (PROCEDURE = equal_integers_imm, LEFTARG = integer, RIGHTARG = integer);
+CREATE operator ====== (PROCEDURE = equal_booleans_stl_strict, LEFTARG = boolean, RIGHTARG = boolean);
+-- Simple functions testing
+SELECT x_vlt() FROM generate_series(1, 3) x; -- should not be precalculated
+NOTICE: v
+NOTICE: v
+NOTICE: v
+ x_vlt
+-------
+ 1
+ 1
+ 1
+(3 rows)
+
+SELECT x_stl() FROM generate_series(1, 3) x;
+NOTICE: s
+ x_stl
+-------
+ 1
+ 1
+ 1
+(3 rows)
+
+SELECT x_vlt() FROM generate_series(1, 4) x WHERE x_vlt() < x; -- should not be precalculated
+NOTICE: v
+NOTICE: v
+NOTICE: v
+NOTICE: v
+NOTICE: v
+NOTICE: v
+NOTICE: v
+ x_vlt
+-------
+ 1
+ 1
+ 1
+(3 rows)
+
+SELECT x_stl() FROM generate_series(1, 4) x WHERE x_stl() < x;
+NOTICE: s
+NOTICE: s
+NOTICE: s
+ x_stl
+-------
+ 1
+ 1
+ 1
+(3 rows)
+
+-- Functions with constant arguments and nested functions testing
+SELECT x_stl2(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: v
+NOTICE: s2
+NOTICE: v
+NOTICE: s2
+NOTICE: v
+NOTICE: s2
+NOTICE: v
+NOTICE: s2
+ x_stl2
+--------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_imm2(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: v
+NOTICE: i2
+NOTICE: v
+NOTICE: i2
+NOTICE: v
+NOTICE: i2
+NOTICE: v
+NOTICE: i2
+ x_imm2
+--------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_stl2(x_stl2(1)) FROM generate_series(1, 4) x;
+NOTICE: s2
+NOTICE: s2
+ x_stl2
+--------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_imm2(x_stl2(1)) FROM generate_series(1, 4) x;
+NOTICE: s2
+NOTICE: i2
+ x_imm2
+--------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+-- Strict functions testing
+SELECT x_stl2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: v
+NOTICE: s2 strict
+NOTICE: v
+NOTICE: s2 strict
+NOTICE: v
+NOTICE: s2 strict
+NOTICE: v
+NOTICE: s2 strict
+ x_stl2_strict
+---------------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_imm2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: v
+NOTICE: i2 strict
+NOTICE: v
+NOTICE: i2 strict
+NOTICE: v
+NOTICE: i2 strict
+NOTICE: v
+NOTICE: i2 strict
+ x_imm2_strict
+---------------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_stl2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x;
+NOTICE: s2 strict
+NOTICE: s2 strict
+ x_stl2_strict
+---------------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_imm2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x;
+NOTICE: s2 strict
+NOTICE: i2 strict
+ x_imm2_strict
+---------------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+-- Strict functions with null arguments testing
+SELECT x_stl2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x;
+NOTICE: s2
+ x_stl2_strict
+---------------
+
+
+
+
+(4 rows)
+
+SELECT x_imm2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x;
+NOTICE: s2
+ x_imm2_strict
+---------------
+
+
+
+
+(4 rows)
+
+-- Operators testing
+SELECT 1 === 2 FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT 1 ==== 2 FROM generate_series(1, 4) x;
+NOTICE: equal integers stable
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT 1 ===== 2 FROM generate_series(1, 4) x;
+NOTICE: equal integers immutable
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+-- Nested and strict operators testing
+SELECT (x_vlt() ==== 2) ====== (x_vlt() ===== 3) FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: equal booleans stable strict
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: equal booleans stable strict
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: equal booleans stable strict
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: equal booleans stable strict
+ ?column?
+----------
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT (1 ==== 2) ====== (3 ==== 3) FROM generate_series(1, 4) x;
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+NOTICE: equal booleans stable strict
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT x_stl2_boolean(NULL) ====== (3 ==== 3) FROM generate_series(1, 4) x;
+NOTICE: s2 boolean
+NOTICE: equal integers stable
+ ?column?
+----------
+
+
+
+
+(4 rows)
+
+-- Mixed functions and operators testing
+SELECT x_vlt() ==== 2 FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: v
+NOTICE: equal integers stable
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT x_vlt() ===== 2 FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: v
+NOTICE: equal integers immutable
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT x_stl() ==== x_stl() FROM generate_series(1, 4) x;
+NOTICE: s
+NOTICE: s
+NOTICE: equal integers stable
+ ?column?
+----------
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT x_stl2_boolean(1 ==== 2) FROM generate_series(1, 4) x;
+NOTICE: equal integers stable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ f
+ f
+ f
+ f
+(4 rows)
+
+-- Tracking functions testing
+SET track_functions TO 'all';
+SELECT x_vlt() FROM generate_series(1, 3) x; -- should not be precalculated
+NOTICE: v
+NOTICE: v
+NOTICE: v
+ x_vlt
+-------
+ 1
+ 1
+ 1
+(3 rows)
+
+SELECT x_stl() FROM generate_series(1, 3) x;
+NOTICE: s
+ x_stl
+-------
+ 1
+ 1
+ 1
+(3 rows)
+
+SELECT x_vlt() FROM generate_series(1, 4) x WHERE x_vlt() < x; -- should not be precalculated
+NOTICE: v
+NOTICE: v
+NOTICE: v
+NOTICE: v
+NOTICE: v
+NOTICE: v
+NOTICE: v
+ x_vlt
+-------
+ 1
+ 1
+ 1
+(3 rows)
+
+SELECT x_stl() FROM generate_series(1, 4) x WHERE x_stl() < x;
+NOTICE: s
+NOTICE: s
+NOTICE: s
+ x_stl
+-------
+ 1
+ 1
+ 1
+(3 rows)
+
+SELECT x_stl2(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: v
+NOTICE: s2
+NOTICE: v
+NOTICE: s2
+NOTICE: v
+NOTICE: s2
+NOTICE: v
+NOTICE: s2
+ x_stl2
+--------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_imm2(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: v
+NOTICE: i2
+NOTICE: v
+NOTICE: i2
+NOTICE: v
+NOTICE: i2
+NOTICE: v
+NOTICE: i2
+ x_imm2
+--------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_stl2(x_stl2(1)) FROM generate_series(1, 4) x;
+NOTICE: s2
+NOTICE: s2
+ x_stl2
+--------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_imm2(x_stl2(1)) FROM generate_series(1, 4) x;
+NOTICE: s2
+NOTICE: i2
+ x_imm2
+--------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_stl2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: v
+NOTICE: s2 strict
+NOTICE: v
+NOTICE: s2 strict
+NOTICE: v
+NOTICE: s2 strict
+NOTICE: v
+NOTICE: s2 strict
+ x_stl2_strict
+---------------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_imm2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: v
+NOTICE: i2 strict
+NOTICE: v
+NOTICE: i2 strict
+NOTICE: v
+NOTICE: i2 strict
+NOTICE: v
+NOTICE: i2 strict
+ x_imm2_strict
+---------------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_stl2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x;
+NOTICE: s2 strict
+NOTICE: s2 strict
+ x_stl2_strict
+---------------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_imm2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x;
+NOTICE: s2 strict
+NOTICE: i2 strict
+ x_imm2_strict
+---------------
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+SELECT x_stl2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x;
+NOTICE: s2
+ x_stl2_strict
+---------------
+
+
+
+
+(4 rows)
+
+SELECT x_imm2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x;
+NOTICE: s2
+ x_imm2_strict
+---------------
+
+
+
+
+(4 rows)
+
+SELECT 1 === 2 FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+NOTICE: equal integers volatile
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT 1 ==== 2 FROM generate_series(1, 4) x;
+NOTICE: equal integers stable
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT 1 ===== 2 FROM generate_series(1, 4) x;
+NOTICE: equal integers immutable
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT (x_vlt() ==== 2) ====== (x_vlt() ===== 3) FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: equal booleans stable strict
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: equal booleans stable strict
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: equal booleans stable strict
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: equal booleans stable strict
+ ?column?
+----------
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT (1 ==== 2) ====== (3 ==== 3) FROM generate_series(1, 4) x;
+NOTICE: equal integers stable
+NOTICE: equal integers stable
+NOTICE: equal booleans stable strict
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT x_stl2_boolean(NULL) ====== (3 ==== 3) FROM generate_series(1, 4) x;
+NOTICE: s2 boolean
+NOTICE: equal integers stable
+ ?column?
+----------
+
+
+
+
+(4 rows)
+
+SELECT x_vlt() ==== 2 FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: v
+NOTICE: equal integers stable
+NOTICE: v
+NOTICE: equal integers stable
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT x_vlt() ===== 2 FROM generate_series(1, 4) x; -- should not be precalculated
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: v
+NOTICE: equal integers immutable
+NOTICE: v
+NOTICE: equal integers immutable
+ ?column?
+----------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SELECT x_stl() ==== x_stl() FROM generate_series(1, 4) x;
+NOTICE: s
+NOTICE: s
+NOTICE: equal integers stable
+ ?column?
+----------
+ t
+ t
+ t
+ t
+(4 rows)
+
+SELECT x_stl2_boolean(1 ==== 2) FROM generate_series(1, 4) x;
+NOTICE: equal integers stable
+NOTICE: s2 boolean
+ x_stl2_boolean
+----------------
+ f
+ f
+ f
+ f
+(4 rows)
+
+SET track_functions TO DEFAULT;
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 04206c3..f2710b9 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -179,3 +179,4 @@ test: with
test: xml
test: event_trigger
test: stats
+test: precalculate_stable_functions
diff --git a/src/test/regress/sql/precalculate_stable_functions.sql b/src/test/regress/sql/precalculate_stable_functions.sql
new file mode 100644
index 0000000..bcef2ca
--- /dev/null
+++ b/src/test/regress/sql/precalculate_stable_functions.sql
@@ -0,0 +1,240 @@
+--
+-- PRECALCULATE STABLE FUNCTIONS
+--
+
+-- Create volatile functions for testing
+
+CREATE OR REPLACE FUNCTION public.x_vlt (
+)
+RETURNS integer VOLATILE AS
+$body$
+BEGIN
+ RAISE NOTICE 'v';
+ RETURN 1;
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION public.equal_integers_vlt (
+ integer,
+ integer
+)
+RETURNS boolean VOLATILE AS
+$body$
+BEGIN
+ RAISE NOTICE 'equal integers volatile';
+ RETURN $1 = $2;
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+-- Create stable functions for testing
+
+CREATE OR REPLACE FUNCTION public.x_stl (
+)
+RETURNS integer STABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 's';
+ RETURN 1;
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION public.x_stl2 (
+ integer
+)
+RETURNS integer STABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 's2';
+ RETURN $1;
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION public.x_stl2_strict (
+ integer
+)
+RETURNS integer STABLE STRICT AS
+$body$
+BEGIN
+ RAISE NOTICE 's2 strict';
+ RETURN $1;
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION public.equal_integers_stl (
+ integer,
+ integer
+)
+RETURNS boolean STABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 'equal integers stable';
+ RETURN $1 = $2;
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION public.x_stl2_boolean (
+ boolean
+)
+RETURNS boolean STABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 's2 boolean';
+ RETURN $1;
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION public.equal_booleans_stl_strict (
+ boolean,
+ boolean
+)
+RETURNS boolean STABLE STRICT AS
+$body$
+BEGIN
+ RAISE NOTICE 'equal booleans stable strict';
+ RETURN $1 = $2;
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+-- Create immutable functions for testing
+
+CREATE OR REPLACE FUNCTION public.x_imm2 (
+ integer
+)
+RETURNS integer IMMUTABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 'i2';
+ RETURN $1;
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION public.x_imm2_strict (
+ integer
+)
+RETURNS integer IMMUTABLE STRICT AS
+$body$
+BEGIN
+ RAISE NOTICE 'i2 strict';
+ RETURN $1;
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+CREATE OR REPLACE FUNCTION public.equal_integers_imm (
+ integer,
+ integer
+)
+RETURNS boolean IMMUTABLE AS
+$body$
+BEGIN
+ RAISE NOTICE 'equal integers immutable';
+ RETURN $1 = $2;
+END;
+$body$
+LANGUAGE 'plpgsql';
+
+-- Create operators for testing
+
+CREATE operator === (PROCEDURE = equal_integers_vlt, LEFTARG = integer, RIGHTARG = integer);
+CREATE operator ==== (PROCEDURE = equal_integers_stl, LEFTARG = integer, RIGHTARG = integer);
+CREATE operator ===== (PROCEDURE = equal_integers_imm, LEFTARG = integer, RIGHTARG = integer);
+CREATE operator ====== (PROCEDURE = equal_booleans_stl_strict, LEFTARG = boolean, RIGHTARG = boolean);
+
+-- Simple functions testing
+
+SELECT x_vlt() FROM generate_series(1, 3) x; -- should not be precalculated
+SELECT x_stl() FROM generate_series(1, 3) x;
+
+SELECT x_vlt() FROM generate_series(1, 4) x WHERE x_vlt() < x; -- should not be precalculated
+SELECT x_stl() FROM generate_series(1, 4) x WHERE x_stl() < x;
+
+-- Functions with constant arguments and nested functions testing
+
+SELECT x_stl2(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+SELECT x_imm2(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+
+SELECT x_stl2(x_stl2(1)) FROM generate_series(1, 4) x;
+SELECT x_imm2(x_stl2(1)) FROM generate_series(1, 4) x;
+
+-- Strict functions testing
+
+SELECT x_stl2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+SELECT x_imm2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+
+SELECT x_stl2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x;
+SELECT x_imm2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x;
+
+-- Strict functions with null arguments testing
+
+SELECT x_stl2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x;
+SELECT x_imm2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x;
+
+-- Operators testing
+
+SELECT 1 === 2 FROM generate_series(1, 4) x; -- should not be precalculated
+SELECT 1 ==== 2 FROM generate_series(1, 4) x;
+SELECT 1 ===== 2 FROM generate_series(1, 4) x;
+
+-- Nested and strict operators testing
+
+SELECT (x_vlt() ==== 2) ====== (x_vlt() ===== 3) FROM generate_series(1, 4) x; -- should not be precalculated
+SELECT (1 ==== 2) ====== (3 ==== 3) FROM generate_series(1, 4) x;
+SELECT x_stl2_boolean(NULL) ====== (3 ==== 3) FROM generate_series(1, 4) x;
+
+-- Mixed functions and operators testing
+
+SELECT x_vlt() ==== 2 FROM generate_series(1, 4) x; -- should not be precalculated
+SELECT x_vlt() ===== 2 FROM generate_series(1, 4) x; -- should not be precalculated
+
+SELECT x_stl() ==== x_stl() FROM generate_series(1, 4) x;
+SELECT x_stl2_boolean(1 ==== 2) FROM generate_series(1, 4) x;
+
+-- Tracking functions testing
+
+SET track_functions TO 'all';
+
+SELECT x_vlt() FROM generate_series(1, 3) x; -- should not be precalculated
+SELECT x_stl() FROM generate_series(1, 3) x;
+
+SELECT x_vlt() FROM generate_series(1, 4) x WHERE x_vlt() < x; -- should not be precalculated
+SELECT x_stl() FROM generate_series(1, 4) x WHERE x_stl() < x;
+
+SELECT x_stl2(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+SELECT x_imm2(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+
+SELECT x_stl2(x_stl2(1)) FROM generate_series(1, 4) x;
+SELECT x_imm2(x_stl2(1)) FROM generate_series(1, 4) x;
+
+SELECT x_stl2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+SELECT x_imm2_strict(x_vlt()) FROM generate_series(1, 4) x; -- should not be precalculated
+
+SELECT x_stl2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x;
+SELECT x_imm2_strict(x_stl2_strict(1)) FROM generate_series(1, 4) x;
+
+SELECT x_stl2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x;
+SELECT x_imm2_strict(x_stl2(NULL)) FROM generate_series(1, 4) x;
+
+SELECT 1 === 2 FROM generate_series(1, 4) x; -- should not be precalculated
+SELECT 1 ==== 2 FROM generate_series(1, 4) x;
+SELECT 1 ===== 2 FROM generate_series(1, 4) x;
+
+SELECT (x_vlt() ==== 2) ====== (x_vlt() ===== 3) FROM generate_series(1, 4) x; -- should not be precalculated
+SELECT (1 ==== 2) ====== (3 ==== 3) FROM generate_series(1, 4) x;
+SELECT x_stl2_boolean(NULL) ====== (3 ==== 3) FROM generate_series(1, 4) x;
+
+SELECT x_vlt() ==== 2 FROM generate_series(1, 4) x; -- should not be precalculated
+SELECT x_vlt() ===== 2 FROM generate_series(1, 4) x; -- should not be precalculated
+
+SELECT x_stl() ==== x_stl() FROM generate_series(1, 4) x;
+SELECT x_stl2_boolean(1 ==== 2) FROM generate_series(1, 4) x;
+
+SET track_functions TO DEFAULT;
\ No newline at end of file
--
1.9.1
Marina Polyakova <m.polyakova@postgrespro.ru> writes:
Now in Postgresql only immutable functions are precalculated; stable
functions are calculated for every row so in fact they don't differ from
volatile functions.
There's a proposal to precalculate stable and immutable functions (=
calculate once for all output rows, but as many times as function is
mentioned in query), if they don't return a set and their arguments are
constants or recursively precalculated functions.
Have you looked at the previous efforts in this direction? The last
discussion I can find is
/messages/by-id/CABRT9RA-RomVS-yzQ2wUtZ=m-eV61LcbrL1P1J3jydPStTfc6Q@mail.gmail.com
In particular, that relied on the planner to decide which subtrees were
worth caching and insert marker nodes for the purpose. I'm not certain
that that's better than putting the intelligence into execExpr.c, but
I'm not sure it isn't either. In principle we could afford to spend
more effort on making such determinations at plan time than we should
do at executor startup. Also, the fundamental implementation seemed
less invasive, in that only the marker node type had to know about the
caching behavior, whereas I gather from your description that what you
are doing is going to end up touching almost all node types.
v10's new expression eval technology is sufficiently different that
it may well be that that old approach isn't very relevant anymore.
But it would be a good idea to look.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers