-- Setup data

create table test_numeric (
  id serial not null primary key,
  partitionid int not null,
  num_fixed_scale numeric(10,2) not null,
  num_variable_scale numeric not null
);
  
insert into test_numeric (partitionid,num_fixed_scale,num_variable_scale) 
select i % 50 as partitionid, -- so we can PARTITION BY something.
       cast(i as numeric),
       cast(i::text || '.' || lpad('1',i % 10,'0') as numeric)
from generate_series(1,20000) s(i);


create table test_float (
  id serial not null,
  partitionid int not null,
  float_4 real not null,
  float_8 double precision not null,
  primary key (id)
);


insert into test_float (partitionid,float_4,float_8) 
select i % 50 as partitionid, -- so we can PARTITION BY something.
       cast(i::text || '.' || lpad('1',6 - (i % 6),'0') as real),
       cast(i::text || '.' || lpad('1',6 - (i % 6),'0') as double precision)
from generate_series(1,20000) s(i);


/***********************
 Fixed scale numeric
************************/


-- Should always be able to inverse transition.
select sum(num_fixed_scale) over(order by id rows between current row and unbounded following) from test_numeric;
/* Results in miliseconds 

Patched: 
50.958
63.820
64.719
52.860
64.921

Unpatched:
61358.856
61180.370
62642.495
61416.385
62383.012

Comp.	Patched	Unpatched			
average	59.4556	61796.2236	Avg Increase (times)	1039.367589	103936.76%
median	63.82	61416.385	Median Increase (times)	962.3375901	96233.76%

*/

-- always inverse transition sliding window at most 21 rows tall.
-- this should always be able to perform inverse transitions.
select sum(num_fixed_scale) over(order by id rows between 10 preceding and 10 following) from test_numeric;

/* Results in muliseconds

Patched:
86.511
119.326
117.112
104.280
109.103

Unpatched:
201.511
206.530
190.573
209.594
188.201

Comp.	Patched	Unpatched			
average	107.2664	199.2818	Avg Increase (times)	1.857821275	185.78%
median	109.103	201.511	Median Increase (times)	1.84697946	184.70%

*/

-- same as above but with partition by.
select sum(num_fixed_scale) over(partition by partitionid order by id rows between 10 preceding and 10 following) from test_numeric;

/* Results in muliseconds

Patched:
119.247
115.929
115.964
128.497
128.967

Unpatched:
215.987
203.738
214.281
215.797
224.511

Comp.	Patched	Unpatched			
average	121.720	214.862	Avg Increase (times)	1.765210219	176.52%
median	119.247	215.797	Median Increase (times)	1.809663975	180.97%

*/

-- no inverse transitions.
select sum(num_fixed_scale) over(order by id) from test_numeric;

/* Results in miliseconds

Patched:
56.063
52.682
50.710
62.129
55.812

Unpatched:
46.229
62.251
59.218
63.113
53.349

Comp.	Patched	Unpatched			
avg		59.4556	61796.2236	Avg Increase (times)	1039.367589	103936.76%
median	63.82	61416.385	Median Increase (times)	962.3375901	96233.76%

*/

-- normal aggregate
select sum(num_fixed_scale) from test_numeric;

/* Results in miliseconds

Patched:
13.476
12.612
12.457
12.797
12.564

Unpatched:
11.292
13.158
11.849
13.565
12.986

Comp.	Patched	Unpatched			
avg		12.7812	12.57	Avg Increase (times)	0.98347573	98.35%
median	12.612	12.986	Median Increase (times)	1.029654297	102.97%

*/

/***********************
 Variable scale numeric
************************/
-- tests for numeric with a really bad case of changing scale.
select sum(num_variable_scale) over(order by id rows between current row and unbounded following) from test_numeric;

/* Results in miliseconds

Patched:
69.246
58.574
70.410
65.415
62.043

Unpatched:
67073.005
66895.327
67511.977
69317.587
68647.335

Comp.	Patched	Unpatched			
avg		65.1376	67889.0462	Avg Increase (times)	1042.240522	104224.05%
median	65.415	67511.977	Median Increase (times)	1032.056516	103205.65%


*/

--explain (analyze true, verbose true)
select sum(num_variable_scale) over(order by id rows between 10 preceding and 10 following) from test_numeric;

/* Results in miliseconds

Patched:
112.186
123.454
113.636
111.475
109.149

Unpatched
202.757
202.288
206.305
218.756
202.841

Comp.	Patched	Unpatched			
average	113.98	206.5894	Avg Increase (times)	1.812505703	181.25%
median	112.186	202.841	Median Increase (times)	1.808077657	180.81%

*/

-- same as above but with partition by.
select sum(num_variable_scale) over(partition by partitionid order by id rows between 10 preceding and 10 following) from test_numeric;

/* Results in miliseconds

Patched:
112.828
121.361
131.561	
139.665
150.856

Unpatched:
212.636
237.160
207.431
212.518
198.667

Comp.	Patched	Unpatched			
average	131.2542	213.6824	Avg Increase (times)	1.628004285	162.80%
median	131.561	212.518	Median Increase (times)	1.615357135	161.54%

*/

-- no inverse transitions.
select sum(num_variable_scale) over(order by id) from test_numeric;

/* Results in miliseconds

Patched:
58.420
61.845
57.822
60.797
54.084

Unpatched:
55.116
65.192
47.140
62.661
57.197

Comp.	Patched	Unpatched			
average	58.5936	57.4612	Avg Increase (times)	0.980673657	98.07%
median	58.42	57.197	Median Increase (times)	0.979065389	97.91%

*/

-- normal aggregate
select sum(num_variable_scale) from test_numeric;

/* Results in miliseconds

Patched:
6.955
13.834
14.286
14.208
14.240

Unpatched:
14.109
13.939
14.292
8.209
14.364

Comp.	Patched	Unpatched			
average	12.7046	12.9826	Avg Increase (times)	1.021881838	102.19%
median	14.208	14.109	Median Increase (times)	0.993032095	99.30%

*/

/***********************
 MAX(int) test
************************/

-- best case for inverse transitions
select max(id) over (order by id rows between current row and unbounded following) from test_numeric;

/* Results in miliseconds

Patched:
50.259
56.310
43.048
49.984
48.939

Unpatched:
36799.741
36559.310
36595.636
36507.597
36670.881

Comp.	Patched	Unpatched			
average	49.708	36626.633	Avg Increase (times)	736.835781	73683.58%
median	49.984	36595.636	Median Increase (times)	732.147007	73214.70%

*/

-- worst case
select max(id) over (order by id DESC rows between current row and unbounded following) from test_numeric;

/* Results in miliseconds

Patched:
37477.440
37849.856
37485.064

Unpatched:
36397.710
36619.854
38127.652

Comp.	Patched	Unpatched			
average	37604.12	37048.40533	Avg Increase (times)	0.985221974	98.52%
median	37485.064	36619.854	Median Increase (times)	0.97691854	97.69%

*/

select count(id) over (order by id rows between current row and unbounded following) from test_numeric;

/* Results in miliseconds

Patched:

53.961
51.007
47.533
55.729
50.744

Unpatched:
38123.509
38492.724
38215.065
38940.284
38336.407

Comp.	Patched	Unpatched			
average	51.7948	38421.5978	Avg Increase (times)	741.8041541	74180.42%
median	51.007	38336.407	Median Increase (times)	751.5910953	75159.11%

*/


/***********************
 the strange case of 1 row window frames.
************************/

select count(*) over (order by id rows between current row and current row) from test_numeric;

/* Results in miliseconds

Patched:
27.779
41.351
43.434
48.595
60.952

Unpatched:
48.353
44.417
53.545
44.822
38.380
*/




/***********************
 cast float to numeric
************************/

select sum(float_4::numeric) over (order by id rows between current row and 10 following) from test_float;

/* Results in miliseconds

Patched:
182.801
149.210
159.477
157.277
169.348

Unpatched:
472.680
491.553
476.515
481.021
476.519

*/