-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy path10M.sql
120 lines (114 loc) · 4.92 KB
/
10M.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
TRUNCATE TABLE auction_json;
ALTER SEQUENCE auction_json_id_seq RESTART;
INSERT INTO auction_json (title, start_date, end_date, item)
SELECT
'Title ' || cnt,
'2024-01-01'::timestamp + (INTERVAL '1 minute' * (cnt-1)),
'2024-01-01'::timestamp + (INTERVAL '1 minute' * (cnt+6)),
JSON_BUILD_OBJECT(
'type', 'book',
'genre', 'Genre '||cnt,
'author', 'Author ' || cnt % 1000,
'title', 'Title ' || cnt,
'description', 'Description ' || cnt,
'startDate', '2024-01-01'::timestamp + (INTERVAL '1 minute' * (cnt-1)),
'endDate', '2024-01-01'::timestamp + (INTERVAL '1 minute' * (cnt+6))
)::json
FROM generate_series(1, 10000000) AS t(cnt);
TRUNCATE TABLE auction_json_indexed;
ALTER SEQUENCE auction_json_indexed_id_seq RESTART;
INSERT INTO auction_json_indexed (title, start_date, end_date, item)
SELECT
'Title ' || cnt,
'2024-01-01'::timestamp + (INTERVAL '1 minute' * (cnt-1)),
'2024-01-01'::timestamp + (INTERVAL '1 minute' * (cnt+6)),
JSON_BUILD_OBJECT(
'type', 'book',
'genre', 'Genre '||cnt,
'author', 'Author ' || cnt % 1000,
'title', 'Title ' || cnt,
'description', 'Description ' || cnt,
'startDate', '2024-01-01'::timestamp + (INTERVAL '1 minute' * (cnt-1)),
'endDate', '2024-01-01'::timestamp + (INTERVAL '1 minute' * (cnt+6))
)::json
FROM generate_series(1, 10000000) AS t(cnt);
TRUNCATE TABLE auction_jsonb;
ALTER SEQUENCE auction_jsonb_id_seq RESTART;
INSERT INTO auction_jsonb (title, start_date, end_date, item)
SELECT
'Title ' || cnt,
'2024-01-01'::timestamp + (INTERVAL '1 minute' * (cnt-1)),
'2024-01-01'::timestamp + (INTERVAL '1 minute' * (cnt+6)),
JSON_BUILD_OBJECT(
'type', 'book',
'genre', 'Genre '||cnt,
'author', 'Author ' || cnt % 1000,
'title', 'Title ' || cnt,
'description', 'Description ' || cnt,
'startDate', '2024-01-01'::timestamp + (INTERVAL '1 minute' * (cnt-1)),
'endDate', '2024-01-01'::timestamp + (INTERVAL '1 minute' * (cnt+6))
)::json
FROM generate_series(1, 10000000) AS t(cnt);
TRUNCATE TABLE auction_jsonb_indexed;
ALTER SEQUENCE auction_jsonb_indexed_id_seq RESTART;
INSERT INTO auction_jsonb_indexed (title, start_date, end_date, item)
SELECT
'Title ' || cnt,
'2024-01-01'::timestamp + (INTERVAL '1 minute' * (cnt-1)),
'2024-01-01'::timestamp + (INTERVAL '1 minute' * (cnt+6)),
JSON_BUILD_OBJECT(
'type', 'book',
'genre', 'Genre '||cnt,
'author', 'Author ' || cnt % 1000,
'title', 'Title ' || cnt,
'description', 'Description ' || cnt,
'startDate', '2024-01-01'::timestamp + (INTERVAL '1 minute' * (cnt-1)),
'endDate', '2024-01-01'::timestamp + (INTERVAL '1 minute' * (cnt+6))
)::json
FROM generate_series(1, 10000000) AS t(cnt);
TRUNCATE TABLE auction_jsonb_gin;
ALTER SEQUENCE auction_jsonb_gin_id_seq RESTART;
INSERT INTO auction_jsonb_gin (title, start_date, end_date, item)
SELECT
'Title ' || cnt,
'2024-01-01'::timestamp + (INTERVAL '1 minute' * (cnt-1)),
'2024-01-01'::timestamp + (INTERVAL '1 minute' * (cnt+6)),
JSON_BUILD_OBJECT(
'type', 'book',
'genre', 'Genre '||cnt,
'author', 'Author ' || cnt % 1000,
'title', 'Title ' || cnt,
'description', 'Description ' || cnt,
'startDate', '2024-01-01'::timestamp + (INTERVAL '1 minute' * (cnt-1)),
'endDate', '2024-01-01'::timestamp + (INTERVAL '1 minute' * (cnt+6))
)::json
FROM generate_series(1, 10000000) AS t(cnt);
-- the additional columns are generated by postgres (and may not be explicitly assigned a value)
TRUNCATE TABLE auction_generated_fields;
ALTER SEQUENCE auction_generated_fields_id_seq RESTART;
INSERT INTO auction_generated_fields (item)
SELECT
JSON_BUILD_OBJECT(
'type', 'book',
'genre', 'Genre '||cnt,
'author', 'Author ' || cnt % 1000,
'title', 'Title ' || cnt,
'description', 'Description ' || cnt,
'startDate', '2024-01-01'::timestamp + (INTERVAL '1 minute' * (cnt-1)),
'endDate', '2024-01-01'::timestamp + (INTERVAL '1 minute' * (cnt+6))
)::json
FROM generate_series(1, 10000000) AS t(cnt);
TRUNCATE TABLE auction_generated_fields_indexed;
ALTER SEQUENCE auction_generated_fields_indexed_id_seq RESTART;
INSERT INTO auction_generated_fields_indexed (item)
SELECT
JSON_BUILD_OBJECT(
'type', 'book',
'genre', 'Genre '||cnt,
'author', 'Author ' || cnt % 1000,
'title', 'Title ' || cnt,
'description', 'Description ' || cnt,
'startDate', '2024-01-01'::timestamp + (INTERVAL '1 minute' * (cnt-1)),
'endDate', '2024-01-01'::timestamp + (INTERVAL '1 minute' * (cnt+6))
)::json
FROM generate_series(1, 10000000) AS t(cnt);