-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy patharray_avg.sql
198 lines (175 loc) · 7.58 KB
/
array_avg.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
begin;
drop function if exists array_avg(int2[]);
drop function if exists array_avg(int4[]);
drop function if exists array_avg(int8[]);
drop function if exists array_avg(numeric[]);
CREATE OR REPLACE FUNCTION array_avg(int2[])
RETURNS numeric SET search_path from current
AS '/home/jian/Desktop/regress_pgsql/array_avg', 'array_avg'
LANGUAGE c IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION array_avg(int4[])
RETURNS numeric SET search_path from current
AS '/home/jian/Desktop/regress_pgsql/array_avg', 'array_avg'
LANGUAGE c IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION array_avg(int8[])
RETURNS numeric SET search_path from current
AS '/home/jian/Desktop/regress_pgsql/array_avg', 'array_avg'
LANGUAGE c IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION array_avg(numeric[])
RETURNS numeric SET search_path from current
AS '/home/jian/Desktop/regress_pgsql/array_avg', 'array_avg'
LANGUAGE c IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION array_avg(interval[])
RETURNS interval SET search_path from current
AS '/home/jian/Desktop/regress_pgsql/array_avg', 'array_avg'
LANGUAGE c IMMUTABLE STRICT;
commit;
select array_avg('{1,2,3,4,5,null,null}'::int8[])
,array_avg('{1,2,3,4,5,null,null}'::int4[])
,array_avg('{1,2,3,4,5,null,null}'::int2[])
,array_avg('{1,2,3,4,5,null,null}'::numeric[])
,array_avg('{1s,2s,3s,4s,5s,null,null}'::interval[]);
select array_avg('{null,NULL,NULL}'::int2[])
,array_avg('{null,NULL,NULL}'::int4[])
,array_avg('{null,NULL,NULL}'::int8[])
,array_avg('{null,NULL,NULL}'::numeric[]);
select array_avg(null::int8[]),array_avg(null::numeric[]),array_avg(null::int4[]),array_avg(null::int2[]);
select array_avg('{}'::int8[]);
select array_avg('{}'::numeric[]);
select array_avg('{}'::int4[]);
select array_avg('{}'::int2[]);
--validate
(
select avg(a) as a,avg(b) as b,avg(c) as c,avg(f) as f,avg(k) as k
from test_generic
)
EXCEPT
select array_avg(a) as a,array_avg(b) as b,array_avg(c) as c
,array_avg(f) as f
,array_avg(k) as k
from test_generic_array;
-------------------------------------------------------------------------------------------
--performance test
-------------------------------------------------------------------------------------------
--validate result first.
(select avg(a) as a,avg(b) as b,avg(c) as c,avg(f) as f,avg(k) as k from test_generic_big )
EXCEPT
(select array_avg(a) as a,array_avg(b) as b,array_avg(c) as c, array_avg(f) as f, array_avg(k) as k
from test_generic_big_array);
explain(analyze, buffers,costs off)
select avg(a) as a
,avg(b) as b
,avg(c) as c
,avg(f) as f
,avg(k) as k
from test_generic_big \watch c=3
/*
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Finalize Aggregate (actual time=2351.591..2369.598 rows=1 loops=1)
Buffers: shared hit=3744 read=13498
-> Gather (actual time=2351.524..2369.539 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=3744 read=13498
-> Partial Aggregate (actual time=2341.951..2341.952 rows=1 loops=3)
Buffers: shared hit=3744 read=13498
-> Parallel Seq Scan on test_generic_big (actual time=0.025..286.677 rows=333333 loops=3)
Buffers: shared hit=3744 read=13498
Planning Time: 0.223 ms
Execution Time: 2369.806 ms
(12 rows)
*/
explain(analyze, buffers,costs off)
select
array_avg(a) as a
,array_avg(b) as b
,array_avg(c) as c
,array_avg(f) as f
,array_avg(k) as k
from test_generic_big_array \watch c=3
/*
QUERY PLAN
------------------------------------------------------------------------------------
Seq Scan on test_generic_big_array (actual time=6023.759..6737.916 rows=1 loops=1)
Buffers: shared hit=2550
Planning Time: 0.078 ms
Execution Time: 6737.967 ms
(4 rows)
*/
------------------------------------------------------------------------------------
explain(analyze, buffers,costs off)
select * from
(select avg(a.a) as a from test_generic_big_array,unnest(a) a) sub
,
(select avg(b.b) as b from test_generic_big_array ,unnest(b) b) sub1 \watch c=3
/*
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Nested Loop (actual time=5360.209..5360.214 rows=1 loops=1)
Buffers: shared hit=766, temp read=3174 written=3174
-> Aggregate (actual time=2683.191..2683.194 rows=1 loops=1)
Buffers: shared hit=256, temp read=1465 written=1465
-> Nested Loop (actual time=885.807..2222.085 rows=1000000 loops=1)
Buffers: shared hit=256, temp read=1465 written=1465
-> Seq Scan on test_generic_big_array (actual time=0.011..0.017 rows=1 loops=1)
Buffers: shared hit=1
-> Function Scan on unnest a (actual time=885.789..1663.174 rows=1000000 loops=1)
Buffers: shared hit=255, temp read=1465 written=1465
-> Aggregate (actual time=2677.013..2677.014 rows=1 loops=1)
Buffers: shared hit=510, temp read=1709 written=1709
-> Nested Loop (actual time=886.509..2220.186 rows=1000000 loops=1)
Buffers: shared hit=510, temp read=1709 written=1709
-> Seq Scan on test_generic_big_array test_generic_big_array_1 (actual time=0.014..0.020 rows=1 loops=1)
Buffers: shared hit=1
-> Function Scan on unnest b (actual time=886.488..1663.193 rows=1000000 loops=1)
Buffers: shared hit=509, temp read=1709 written=1709
Planning Time: 0.322 ms
Execution Time: 5374.315 ms
(20 rows)
*/
explain(analyze, buffers,costs off)
select array_avg(a) as a ,array_avg(b) as b
from test_generic_big_array \watch c=3
/*
QUERY PLAN
------------------------------------------------------------------------------------
Seq Scan on test_generic_big_array (actual time=3131.723..3492.046 rows=1 loops=1)
Buffers: shared hit=765
Planning Time: 0.060 ms
Execution Time: 3492.087 ms
(4 rows)
*/
------------------------------------------------------------------------------------
explain(analyze, buffers,costs off)
select avg(a) as a
from test_generic_big \watch c=3
/*
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Finalize Aggregate (actual time=581.452..594.009 rows=1 loops=1)
Buffers: shared hit=4032 read=13210
-> Gather (actual time=581.354..593.981 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=4032 read=13210
-> Partial Aggregate (actual time=573.254..573.256 rows=1 loops=3)
Buffers: shared hit=4032 read=13210
-> Parallel Seq Scan on test_generic_big (actual time=0.022..281.959 rows=333333 loops=3)
Buffers: shared hit=4032 read=13210
Planning Time: 0.113 ms
Execution Time: 594.124 ms
(12 rows)
*/
explain(analyze, buffers,costs off)
select array_avg(a) as a
from test_generic_big_array \watch c=3
/*
QUERY PLAN
------------------------------------------------------------------------------------
Seq Scan on test_generic_big_array (actual time=1544.979..1722.415 rows=1 loops=1)
Buffers: shared hit=256
Planning Time: 0.106 ms
Execution Time: 1722.474 ms
(4 rows)
*/