Re: functions: VOLATILE performs better than STABLE

[David Rowley]

On 25 March 2018 at 18:00, Laurenz Albe  wrote:
> Peter wrote:
>> Over all, VOLATILE performs better than STABLE.
>
> The reason is that the subquery with the VOLATILE function can be
> flattened; see the EXPLAIN (VERBOSE) output.
>
> There is not guarantee that less volatility means better performance.

Although, it would be nice.

TPC-H Q1 does appear to be crafted to allow database with smarter
expression evaluation to get a better score.

It would probably require some sort of recursive expression evaluation
where at each level we check if that expression has already been seen,
if it has, then replace it with some sort of placeholder, then
evaluate each placeholder in the required order.

Probably the first part could be done during planning.  It would mean
targetlists would need to carry a bit more weight.

It would be an interesting project to work on, but not planning to personally.

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: Slow planning time for custom function

[bk]

Hi,

thanks for your help which already resolved the issue for me. I worked through 
your replies and it is indeed a startup delay for the first call to a plv8 
function in a session. I pasted the query plans below for comparison.

```
explain analyze select normalizeunidiff('')

 QUERY PLAN 
 
-
 Result  (cost=0.00..0.01 rows=1 width=32) (actual time=0.002..0.002 rows=1 
loops=1)
 Planning time: 1863.782 ms
 Execution time: 0.022 ms
```

Then I ran again multiple times, to make sure that there was not some kind of 
startup delay:
```
select normalizeunidiff('');
explain analyze select normalizeunidiff('');

 QUERY PLAN 
 
-
 Result  (cost=0.00..0.01 rows=1 width=32) (actual time=0.000..0.001 rows=1 
loops=1)
 Planning time: 0.190 ms
 Execution time: 0.008 ms
```

Thanks again
-Ben


> On 24. Mar 2018, at 02:52, David Rowley  wrote:
> 
> On 24 March 2018 at 14:35, Andres Freund  wrote:
>> How long does planning take if you repeat this? I wonder if a good chunk
>> of those 1.8s is initial loading of plv8.
> 
> Maybe, but it also could be the execution of the function, after all,
> the planner does invoke immutable functions:
> 
> # explain verbose select lower('TEST');
>QUERY PLAN
> ---
> Result  (cost=0.00..0.01 rows=1 width=32)
>   Output: 'test'::text
> (2 rows)
> 
> Would be interesting to see what changes without the IMMUTABLE flag.
> 
> -- 
> David Rowley   http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services