Re: Infinite Interval

hi.

fixed the doc special value inf/-inf reference. didn't fix the EXTRACT
function doc issue.

I refactor the avg(interval), sum(interval), so moving aggregate,
plain aggregate both work with +inf/-inf.
no performance degradation, in fact, some performance gains.

--setup for test performance.
create unlogged table interval_aggtest AS
select g::int as a
,make_interval(years => g % 100, days => g % 100, hours => g %
200 , secs => random()::numeric(3,2) *100 ) as b
from generate_series(1, 100_000) g;
--use foreign data wrapper to copy exact content to interval_aggtest_no_patch
create unlogged table interval_aggtest_no_patch AS
select * from interval_aggtest;

--queryA
explain (analyze, costs off, buffers)
SELECT a, avg(b) OVER(ROWS BETWEEN 1 preceding AND 2 FOLLOWING)
from interval_aggtest \watch i=0.1 c=10

--queryB
explain (analyze, costs off, buffers)
SELECT a, avg(b) OVER(ROWS BETWEEN 1 preceding AND 2 FOLLOWING)
from interval_aggtest_no_patch \watch i=0.1 c=10

--queryC
explain (analyze, costs off, buffers)
SELECT a, sum(b) OVER(ROWS BETWEEN 1 preceding AND 2 FOLLOWING)
from interval_aggtest \watch i=0.1 c=10

--queryD
explain (analyze, costs off, buffers)
SELECT a, sum(b) OVER(ROWS BETWEEN 1 preceding AND 2 FOLLOWING)
from interval_aggtest_no_patch \watch i=0.1 c=10

--queryE
explain (analyze, costs off, buffers)
SELECT sum(b), avg(b)
from interval_aggtest \watch i=0.1 c=10

--queryF
explain (analyze, costs off, buffers)
SELECT sum(b), avg(b)
from interval_aggtest_no_patch \watch i=0.1 c=10

queryA execute 10 time, last executed time(ms) 748.258
queryB execute 10 time, last executed time(ms) 1059.750

queryC execute 10 time, last executed time(ms) 697.887
queryD execute 10 time, last executed time(ms) 708.462

queryE execute 10 time, last executed time(ms) 156.237
queryF execute 10 time, last executed time(ms) 405.451
---------------------------------------------------------------------
The result seems right, I am not %100 sure the code it's correct.
That's the best I can think of. You can work based on that.