Mastering PostgreSQL 10

Improving efficiency

The aggregates defined so far can already achieve quite a lot. However, if you are using sliding windows, the number of function calls will simply explode. Here is what happens:

test=# SELECT taxi_price(x::numeric) 
   OVER (ROWS BETWEEN 0 FOLLOWING AND 3 FOLLOWING)  
FROM generate_series(1, 5) AS x; NOTICE:  intermediate: 2.5,  per row:  1 
NOTICE:  intermediate: 4.7,  per row:  2 
NOTICE:  intermediate: 9.1,  per row:  3 
NOTICE:  intermediate: 15.7,  per row:  4 
NOTICE:  intermediate: 2.5,  per row:  2 
NOTICE:  intermediate: 6.9,  per row:  3 
NOTICE:  intermediate: 13.5,  per row:  4 
NOTICE:  intermediate: 22.3,  per row:  5 
... 

For every line, PostgreSQL will process the full window. If the sliding window is large, efficiency will go down the drain. ...