Mastering PostgreSQL 12 - Third Edition

Improving efficiency

The aggregates we've 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; psql: NOTICE:  intermediate: 2.5,  per row:  1 
psql: NOTICE:  intermediate: 4.7,  per row:  2 
psql: NOTICE:  intermediate: 9.1,  per row:  3 
psql: NOTICE:  intermediate: 15.7,  per row:  4 
psql: NOTICE:  intermediate: 2.5,  per row:  2 
psql: NOTICE:  intermediate: 6.9,  per row:  3 
psql: NOTICE:  intermediate: 13.5,  per row:  4 
psql: NOTICE:  intermediate: 22.3,  per row:  5 
... 

For every line, PostgreSQL will process the full window. If the sliding ...