All, > So, context: > > - We want PostGIS to parallelize more. In order to achieve that we need to > mark our functions with more realistic COSTs. Much much higher COSTs. > - When we do that, we hit a different problem. Our most commonly used > functions, ST_Intersects(), ST_DWithin() are actually SQL wrapper functions > are more complex combinations of index operators and exact computational > geometry functions. > - In the presence of high cost parameters that are used multiple times in SQL > functions, PostgreSQL will stop inlining those functions, in an attempt to > save the costs of double-calculating the parameters. > - For us, that's the wrong choice, because we lose the index operators at the > same time as we "save" the cost of double calculation. > - We need our wrapper functions inlined, even when they are carrying a high > COST. > > At pgconf.eu, I canvassed this problem and some potential solutions: > ... > * Solution #2 - Quick and dirty and invisible. Tom suggested a hack that > achieves the aims of #1 but without adding syntax to CREATE FUNCTION: have > the inlining logic look at the cost of the wrapper and the cost of > parameters, and if the cost of the wrapper "greatly exceeded" the cost of the > parameters, then inline. So the PostGIS project would just set the cost of > our wrappers very high, and we'd get the behaviour we want, while other users > who want to use wrappers to force caching of calculations would have zero > coded wrapper functions. Pros: Solves the problem and easy to implement, I'm > happy to contribute. Cons: it's so clearly a hack involving hidden (from > users) magic. > ... > So my question to hackers is: which is less worse, #1 or #2, to implement and > submit to commitfest, in case #3 does not materialize in time for PgSQL 12?
I've been working with Paul to create and test a patch (attached) that addresses Solution #2. This patch essentially modifies the inlining logic to compare the cost of the function with the total cost of the parameters. The goal as stated above, is that for these kinds of functions, they would be assigned relatively high cost to trigger the inlining case. The modification that this patch makes is the following: * Collect the cost for each parameter (no longer short circuits when a single parameter is costly). * Compare the total cost of all parameters to the individual cost of the function. * If the function cost is greater than the parameters, then it will inline the function. * If the function cost is less than the parameters, then it will perform the original parameter checks (short circuiting as necessary). I've included a constant called "INLINE_FUNC_COST_FACTOR" that is currently set to '1'. This is meant to assume for adjustments to what "greatly exceeded" means in the description above. Perhaps this isn't necessary, but I wanted to at least initially provide the flexibility in case it were. I have also attached a simple test case as was originally previously by Paul to demonstrate the functionality. -Adam
pg-example.sql
Description: application/sql
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index f4446169f5..58755151fa 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -4648,6 +4648,9 @@ inline_function(Oid funcid, Oid result_type, Oid result_collid,
Query *querytree;
Node *newexpr;
int *usecounts;
+ double *paramcosts;
+ Cost total_param_cost;
+ double func_cost;
ListCell *arg;
int i;
@@ -4839,9 +4842,20 @@ inline_function(Oid funcid, Oid result_type, Oid result_collid,
* substituted.
*/
usecounts = (int *) palloc0(funcform->pronargs * sizeof(int));
+ paramcosts = (double *) palloc0(funcform->pronargs * sizeof(double));
newexpr = substitute_actual_parameters(newexpr, funcform->pronargs,
args, usecounts);
+ /*
+ * Get the cost of the function without parameters.
+ *
+ * Calculation based on how costsize.c performs lookup for function costs
+ * via 'cost_qual_eval_walker'.
+ */
+ func_cost = get_func_cost(funcid) * cpu_operator_cost;
+
+ total_param_cost = 0.0;
+
/* Now check for parameter usage */
i = 0;
foreach(arg, args)
@@ -4867,10 +4881,10 @@ inline_function(Oid funcid, Oid result_type, Oid result_collid,
*/
if (contain_subplans(param))
goto fail;
+
cost_qual_eval(&eval_cost, list_make1(param), NULL);
- if (eval_cost.startup + eval_cost.per_tuple >
- 10 * cpu_operator_cost)
- goto fail;
+ paramcosts[i] = (eval_cost.startup + eval_cost.per_tuple);
+ total_param_cost += (eval_cost.startup + eval_cost.per_tuple);
/*
* Check volatility last since this is more expensive than the
@@ -4883,6 +4897,19 @@ inline_function(Oid funcid, Oid result_type, Oid result_collid,
}
/*
+ * If the cost of the function is greater than the sum of all the param
+ * costs, then inline. Otherwise, check the cost of each param.
+ */
+ if (func_cost * INLINE_FUNC_COST_FACTOR < total_param_cost)
+ {
+ for (i = 0; i < funcform->pronargs; i++)
+ {
+ if (paramcosts[i] > 10 * cpu_operator_cost)
+ goto fail;
+ }
+ }
+
+ /*
* Whew --- we can make the substitution. Copy the modified expression
* out of the temporary memory context, and clean up.
*/
diff --git a/src/include/optimizer/clauses.h b/src/include/optimizer/clauses.h
index ed854fdd40..475df16b28 100644
--- a/src/include/optimizer/clauses.h
+++ b/src/include/optimizer/clauses.h
@@ -20,6 +20,8 @@
#define is_opclause(clause) ((clause) != NULL && IsA(clause, OpExpr))
#define is_funcclause(clause) ((clause) != NULL && IsA(clause, FuncExpr))
+#define INLINE_FUNC_COST_FACTOR 1
+
typedef struct
{
int numWindowFuncs; /* total number of WindowFuncs found */
