-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathqueries.txt
191 lines (157 loc) · 4.16 KB
/
queries.txt
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
SELECT distinct(e.id)
FROM t_experiment e, t_result r, t_product p,
t_statisticalsummary ss0, t_field p0
WHERE p0.name = 'x' AND ss0.numericvalue_value > 500.0 AND ss0.axisId = p0.Id AND ss0.containerId = p.id AND p.containerId = r.id AND r.containerId = e.id
2
16
18
20
SELECT r.id
FROM t_result r, t_product p,
t_statisticalsummary ss0, t_field p0
WHERE p0.name = 'x' AND r.containerId = 20 AND ss0.numericvalue_value > 100.0::double precision AND ss0.axisId = p0.Id AND ss0.containerId = p.id AND p.containerId = r.id
SELECT ss0.numericvalue_value, ss0.axisId, ss0.containerId
FROM
t_statisticalsummary ss0
WHERE ss0.numericvalue_value >= 900.0::double precision
limit 10000
SELECT ss0.id, ss1.id
FROM
t_statisticalsummary ss0, t_field p0,
t_statisticalsummary ss1, t_field p1,
t_statisticalsummary ss2, t_field p2,
t_statisticalsummary ss3, t_field p3
WHERE
p0.name = 'x' AND ss0.numericvalue_value > 900.0::double precision AND ss0.axisId = p0.Id
AND
p1.name = 'y' AND ss1.numericvalue_value > 300.0::double precision AND ss1.axisId = p1.Id
AND
p2.name = 'z' AND ss2.numericvalue_value > 100.0::double precision AND ss2.axisId = p2.Id
AND
p3.name = 'vx' AND ss3.numericvalue_value > 300.0::double precision AND ss3.axisId = p3.Id
AND
ss0.containerId = ss1.containerId
AND
ss1.containerId = ss2.containerId
AND
ss2.containerId = ss3.containerId
SELECT
distinct(e.id)
FROM
t_experiment e,
t_result r,
t_product p,
t_statisticalsummary ss0, t_field p0,
t_statisticalsummary ss1, t_field p1,
t_statisticalsummary ss2, t_field p2,
t_statisticalsummary ss3, t_field p3
WHERE
p0.name = 'x' AND ss0.numericvalue_value > 900.0::double precision AND ss0.axisId = p0.Id
AND
p1.name = 'y' AND ss1.numericvalue_value > 300.0::double precision AND ss1.axisId = p1.Id
AND
p2.name = 'z' AND ss2.numericvalue_value > 100.0::double precision AND ss2.axisId = p2.Id
AND
p3.name = 'vx' AND ss3.numericvalue_value > 300.0::double precision AND ss3.axisId = p3.Id
AND
ss0.containerId = ss1.containerId
AND
ss1.containerId = ss2.containerId
AND
ss2.containerId = ss3.containerId
AND
ss0.containerId = p.id
AND
p.containerId = r.id
AND
r.containerId = e.id
LIMIT 10
18s
20
SELECT
distinct(e.id)
FROM
t_experiment e,
t_result r,
t_product p,
t_statisticalsummary ss0, t_field p0,
t_statisticalsummary ss1, t_field p1
WHERE
p0.name = 'x' AND ss0.numericvalue_value > 900.0::double precision AND ss0.axisId = p0.Id
AND
p1.name = 'y' AND ss1.numericvalue_value > 300.0::double precision AND ss1.axisId = p1.Id
AND
ss0.containerId = ss1.containerId
AND
ss0.containerId = p.id
AND
p.containerId = r.id
AND
r.containerId = e.id
LIMIT 10
2.2seconds:
2
18
20
16
SUPER STATS VIEW:
SELECT
experiment.protocolid as protocolId,
experiment.id as experimentId,
result.id as resultId,
product.id as productId,
stats.id as statsId,
stats.axisid as axisId,
stats.numericvalue_value,
stats.statistic
FROM
t_experiment experiment,
t_result result,
t_product product,
t_statisticalsummary stats
WHERE
stats.containerId = product.id
AND
product.containerId = result.id
AND
result.containerId = experiment.id
LIMIT 10
SELECT
distinct(ss0.experimentid)
FROM
flat_stats ss0, field p0,
flat_stats ss1, field p1
WHERE
p0.name = 'x' AND ss0.numericvalue_value > 900.0::double precision AND ss0.axisId = p0.Id
AND
p1.name = 'y' AND ss1.numericvalue_value > 300.0::double precision AND ss1.axisId = p1.Id
AND
ss0.productid = ss1.productid
LIMIT 10
3.4seconds:
2
18
20
16
SELECT
distinct(ss0.experimentid)
FROM
flat_stats ss0, field p0,
flat_stats ss1, field p1,
flat_stats ss2, field p2,
flat_stats ss3, field p3
WHERE
p0.name = 'x' AND ss0.numericvalue_value > 900.0::double precision AND ss0.axisId = p0.Id
AND
p1.name = 'y' AND ss1.numericvalue_value > 300.0::double precision AND ss1.axisId = p1.Id
AND
p2.name = 'z' AND ss2.numericvalue_value > 100.0::double precision AND ss2.axisId = p2.Id
AND
p3.name = 'vx' AND ss3.numericvalue_value > 300.0::double precision AND ss3.axisId = p3.Id
AND
ss0.productid = ss1.productid
AND
ss1.productid = ss2.productid
AND
ss2.productid = ss3.productid
8.1s