Re: Changing SQL Inlining Behaviour (or...?) - Mailing list pgsql-hackers

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
actuallySQL 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
inliningthose 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
doublecalculation. 
> - 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
syntaxto CREATE FUNCTION: have the inlining logic look at the cost of the wrapper and the cost of parameters, and if
thecost of the wrapper "greatly exceeded" the cost of the parameters, then inline. So the PostGIS project would just
setthe cost of our wrappers very high, and we'd get the behaviour we want, while other users who want to use wrappers
toforce 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
notmaterialize 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