-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy patharray_test_setup.sql
104 lines (95 loc) · 4.2 KB
/
array_test_setup.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
/*
test setup for the following functions
* array_max
* array_min
* array_avg
* array_sum
* array_median
* array_nonull_count
*/
set extra_float_digits to 0;
drop table if exists test_generic;
create table test_generic as
select g::int2 + (random() * 100)::int2 as a
,g::int4 + (random() * 100)::int4 as b
,g::int8 + (random() * 100)::int8 as c
,g::float4 + (random() * 100)::float4 as d
,g::float8 + (random() * 100)::float8 as e
,g::numeric(10,3) + (random() * 100)::numeric(10,3) as f
,(now() - interval '2 month')::date + (random() * 10)::int as g
,(now())::time(0) as h
,(now() - interval '2 month')::timestamp(0) + (random() * 10)::int * interval '10 hour' as i
,(now() - interval '2 month')::timestamptz(0) + (random() * 10)::int * interval '10 hour' as j
,(interval '2 month') * (random() * 10)::int as k
,(now() - interval '2 month')::timetz(0) + (random() * 10)::int * interval '10 hour' as l
,'7/A25801C8'::pg_lsn + (random() * 100)::numeric(10,0) as m
from generate_series(1,10) g;
drop table if exists test_generic_array;
create table test_generic_array as
SELECT array_agg(a) as a
,array_agg(b) as b
,array_agg(c) as c
,array_agg(d) as d
,array_agg(e) as e
,array_agg(f) as f
,array_agg(g) as g
,array_agg(h) as h
,array_agg(i) as i
,array_agg(j) as j
,array_agg(k) as k
,array_agg(l) as l
,array_agg(m) as m
from test_generic;
--test_performance.
set extra_float_digits to 0;
drop table if exists test_generic_big;
create table test_generic_big as
select (random() * 30000)::int2 as a
,g::int4 * (random() * 100)::int4 as b
,g::int8 * (random() * 100)::int8 as c
,g::float4 + (random() * 100)::float4 as d
,g::float8 + (random() * 100)::float8 as e
,g::numeric(10,3) + (random() * 100)::numeric(10,3) as f
,(now() - interval '2 month')::date + (random() * 10)::int as g
,(now())::time(0) as h
,(now() - interval '2 month')::timestamp(0) + (random() * 10)::int * interval '10 hour' as i
,(now() - interval '2 month')::timestamptz(0) + (random() * 10)::int * interval '10 hour' as j
,(interval '2 month') * (random() * 10)::numeric(10,3) as k
,(now() - interval '2 month')::timetz(0) + interval '10 hour' * (random() * 10)::numeric(10,3) as l
,'7/A25801C8'::pg_lsn + (random() * 100)::numeric(10,3) as m
from generate_series(1,1e6) g;
drop table if exists test_generic_big_array;
create table test_generic_big_array as
SELECT array_agg(a) as a
,array_agg(b) as b
,array_agg(c) as c
,array_agg(d) as d
,array_agg(e) as e
,array_agg(f) as f
,array_agg(g) as g
,array_agg(h) as h
,array_agg(i) as i
,array_agg(j) as j
,array_agg(k) as k
,array_agg(l) as l
,array_agg(m) as m
from test_generic_big;
vacuum analyze test_generic_big_array;
vacuum analyze test_generic_big;
create table test_generic_big_normal(like test_generic_big including all);
--less random data, more sane data.
insert into test_generic_big_normal
select (random() * 30000)::int2 % 20 as a
,(g::int4 + (random() * 1e6)::int4) % 1e5 as b
,(g::int8 + (random() * 1e6)::int8) % 1e5 as c
,g::float4 + (random() * 100)::float4 as d
,g::float8 + (random() * 100)::float8 as e
,g::numeric(10,3) + ((random() * 1e7)::numeric(10,3) % 1e6)::numeric(10,3) as f
,(now() - interval '2 month')::date + (random() * 1e5)::int % 10000::int as g
,(now())::time(0) as h
,(now() - interval '2 month')::timestamp(0) + (random() * 1000)::int % 100 * interval '10 hour' as i
,(now() - interval '2 month')::timestamptz(0) + (random() * 1000)::int % 100 * interval '10 hour' as j
,(interval '2 month') * (random() * 10)::numeric(10,3) as k
,(now() - interval '2 month')::timetz(0) + interval '10 hour' * (random() * 10)::numeric(10,3) as l
,'7/A25801C8'::pg_lsn + (random() * 100)::numeric(10,3) as m
from generate_series(1,1e6) g;