teratail
回答率
85.36%
teratail header banner
teratail header banner
質問するログイン新規登録
トップに関する質問SQLの処理時間での質問です。

編集履歴

質問編集履歴

2

遅くない方の実行計画を追加

2019/10/11 09:08

投稿

rezaq
rezaq

スコア5

test CHANGED
File without changes
test CHANGED
@@ -169,3 +169,51 @@
169
169
  Total runtime: 169664.207 ms
170
170
 
171
171
  ```
172
+
173
+
174
+
175
+ ```ここに言語を入力
176
+
177
+ Hash Left Join (cost=3397.63..3401.18 rows=129 width=11) (actual time=3364.634..3364.695 rows=188 loops=1) Hash Cond: (((a1.xxx)::text = (oh.xxx)::text) AND ((a1.yyy)::text = (gd.yyy)::text))
178
+
179
+ +-HashAggregate (cost=51.65..52.94 rows=129 width=136) (actual time=0.216..0.237 rows=188 loops=1)
180
+
181
+ | +-Hash Join (cost=39.88..51.00 rows=129 width=136) (actual time=0.117..0.177 rows=188 loops=1) Hash Cond: ((a1.yyy)::text = (b1.yyy)::text)
182
+
183
+ | +-Seq Scan on m_aaa a1 (cost=0.00..9.06 rows=206 width=136) (actual time=0.005..0.023 rows=206 loops=1)
184
+
185
+ | +-Hash (cost=37.52..37.52 rows=189 width=18) (actual time=0.109..0.109 rows=188 loops=1)
186
+
187
+ | +-Seq Scan on m_bbb b1 (cost=0.00..37.52 rows=189 width=18) (actual time=0.003..0.082 rows=188 loops=1) Filter: (((baaa)::text <> '1'::text) AND (bccc = 2::numeric))
188
+
189
+ +-Hash (cost=3345.97..3345.97 rows=1 width=68) (actual time=3364.415..3364.415 rows=35 loops=1)
190
+
191
+ +-Nested Loop Left Join (cost=2701.36..3345.97 rows=1 width=68) (actual time=95.513..3364.331 rows=35 loops=1) Join Filter: ((oi.yyy)::text = (od.yyy)::text)
192
+
193
+ +-Nested Loop Left Join (cost=2701.36..3343.23 rows=1 width=162) (actual time=95.497..3363.791 rows=35 loops=1) Join Filter: (((oh.ono)::text = (od.ono)::text) AND (od.seq1 = gd.seq1) AND ((od.yyy)::text = (gd.yyy)::text))
194
+
195
+ | +-Nested Loop (cost=1350.68..1397.62 rows=1 width=78) (actual time=20.295..20.814 rows=35 loops=1) Join Filter: ((oh.ono)::text = (gd.ono)::text)
196
+
197
+ | | +-Index Scan using idx_do_c on do oh (cost=0.00..8.29 rows=1 width=20) (actual time=0.012..0.014 rows=1 loops=1) Index Cond: ((ono)::text = '2000425'::text) Filter: ((odt >= date_trunc('month'::text, (to_date('2019/10/01'::text, 'yyyy/MM/dd'::text))::timestamp with time zone)) AND (odt <= (date_trunc('month'::text, (to_date('2019/10/01'::text, 'yyyy/MM/dd'::text))::timestamp with time zone) + '1 mon -1 days'::interval)))
198
+
199
+ | | +-GroupAggregate (cost=1350.68..1383.88 rows=242 width=48) (actual time=19.845..20.747 rows=145 loops=1)
200
+
201
+ | | +-Sort (cost=1350.68..1356.72 rows=2414 width=48) (actual time=19.829..19.971 rows=2197 loops=1) Sort Key: c1.dod.ono, c1.dod.seq1, c1.dod.yyy
202
+
203
+ | | +-Seq Scan on dod (cost=0.00..1215.05 rows=2414 width=48) (actual time=12.013..18.242 rows=2197 loops=1) Filter: ((odt >= date_trunc('month'::text, (to_date('2019/10/01'::text, 'yyyy/MM/dd'::text))::timestamp with time zone)) AND (odt <= (date_trunc('month'::text, (to_date('2019/10/01'::text, 'yyyy/MM/dd'::text))::timestamp with time zone) + '1 mon -1 days'::interval)))
204
+
205
+ | +-GroupAggregate (cost=1350.68..1938.96 rows=242 width=61) (actual time=1.461..95.469 rows=145 loops=35)
206
+
207
+ | +-Sort (cost=1350.68..1356.72 rows=2414 width=61) (actual time=0.572..0.843 rows=2197 loops=35) Sort Key: c1.dod.ono, c1.dod.seq1, c1.dod.yyy
208
+
209
+ | +-Seq Scan on dod (cost=0.00..1215.05 rows=2414 width=61) (actual time=11.924..18.389 rows=2197 loops=1) Filter: ((odt >= date_trunc('month'::text, (to_date('2019/10/01'::text, 'yyyy/MM/dd'::text))::timestamp with time zone)) AND (odt <= (date_trunc('month'::text, (to_date('2019/10/01'::text, 'yyyy/MM/dd'::text))::timestamp with time zone) + '1 mon -1 days'::interval)))
210
+
211
+ +-Index Scan using idx_doi_c on doi oi (cost=0.00..2.72 rows=1 width=39) (actual time=0.012..0.012 rows=1 loops=35) Index Cond: (((oi.ono)::text = (od.ono)::text) AND (oi.seq1 = od.seq1))
212
+
213
+ Total runtime: 3365.548 ms
214
+
215
+
216
+
217
+ ```
218
+
219
+ 遅くない方の実行計画です。

1

コードの編集・実行計画の追加

2019/10/11 09:08

投稿

rezaq
rezaq

スコア5

test CHANGED
File without changes
test CHANGED
@@ -1,20 +1,26 @@
1
1
  変更前と同等の時間位で処理が行いたいです。
2
2
 
3
3
 
4
+
5
+ ```ここに言語を入力
4
6
 
5
7
  select a.xxx
6
8
 
7
9
  from (
8
10
 
9
- sql1
11
+ sql1
10
12
 
11
13
  ) a
12
14
 
13
15
  left join (
14
16
 
15
- sql2
17
+ sql2
16
18
 
17
19
  ) b on a.xxx = b.xxx and a.yyy = b.yyy
20
+
21
+
22
+
23
+ ```
18
24
 
19
25
  （xxx,yyy はsql1のメインテーブルのキー項目です）
20
26
 
@@ -51,6 +57,8 @@
51
57
  sql1の結果が減少しているのに処理時間が劇的に増加する要因はどんなことが考えられるでしょうか？sql2の変更はありません。
52
58
 
53
59
 
60
+
61
+ ```ここに言語を入力
54
62
 
55
63
  変更前sql1
56
64
 
@@ -111,3 +119,53 @@
111
119
  a1.xxx
112
120
 
113
121
  , a1.yyy
122
+
123
+ ```
124
+
125
+
126
+
127
+
128
+
129
+ データベースはpostgresでA5により実行計画を出力しました。
130
+
131
+ よろしくお願いいたします。
132
+
133
+
134
+
135
+ ```ここに言語を入力
136
+
137
+ Nested Loop Left Join (cost=2719.23..3363.87 rows=1 width=11) (actual time=1606.520..169663.178 rows=47 loops=1) Join Filter: (((oh.purchase_code)::text = (a1.xxx)::text) AND ((gd.yyy)::text = (a1.yyy)::text))
138
+
139
+ +-HashAggregate (cost=17.87..17.88 rows=1 width=136) (actual time=0.195..0.252 rows=47 loops=1)
140
+
141
+ | +-Nested Loop (cost=0.00..17.86 rows=1 width=136) (actual time=0.023..0.180 rows=47 loops=1)
142
+
143
+ | +-Seq Scan on m_aaa a1 (cost=0.00..9.57 rows=1 width=136) (actual time=0.010..0.048 rows=61 loops=1) Filter: ((xxx)::text = '1'::text)
144
+
145
+ | +-Index Scan using idx_m_bbb_c on m_bbb b1 (cost=0.00..8.27 rows=1 width=18) (actual time=0.002..0.002 rows=1 loops=61) Index Cond: ((b1.yyy)::text = (a1.yyy)::text) Filter: (((baaa)::text <> '1'::text) AND (bccc = 2::numeric))
146
+
147
+ +-Nested Loop Left Join (cost=2701.36..3345.97 rows=1 width=68) (actual time=60.623..3609.810 rows=35 loops=47) Join Filter: ((oi.yyy)::text = (od.yyy)::text)
148
+
149
+ +-Nested Loop Left Join (cost=2701.36..3343.23 rows=1 width=162) (actual time=60.607..3609.160 rows=35 loops=47) Join Filter: (((oh.ono)::text = (od.ono)::text) AND (od.seq1 = gd.seq1) AND ((od.yyy)::text = (gd.yyy)::text))
150
+
151
+ | +-Nested Loop (cost=1350.68..1397.62 rows=1 width=78) (actual time=1.001..1.610 rows=35 loops=47) Join Filter: ((oh.ono)::text = (gd.ono)::text)
152
+
153
+ | | +-Index Scan using idx_do_c on do oh (cost=0.00..8.29 rows=1 width=20) (actual time=0.022..0.026 rows=1 loops=47) Index Cond: ((ono)::text = '2000425'::text) Filter: ((odt >= date_trunc('month'::text, (to_date('2019/10/01'::text, 'yyyy/MM/dd'::text))::timestamp with time zone)) AND (odt <= (date_trunc('month'::text, (to_date('2019/10/01'::text, 'yyyy/MM/dd'::text))::timestamp with time zone) + '1 mon -1 days'::interval)))
154
+
155
+ | | +-GroupAggregate (cost=1350.68..1383.88 rows=242 width=48) (actual time=0.430..1.526 rows=145 loops=47)
156
+
157
+ | | +-Sort (cost=1350.68..1356.72 rows=2414 width=48) (actual time=0.421..0.604 rows=2197 loops=47) Sort Key: c1.dod.ono, c1.dod.seq1, c1.dod.yyy
158
+
159
+ | | +-Seq Scan on dod (cost=0.00..1215.05 rows=2414 width=48) (actual time=10.864..18.156 rows=2197 loops=1) Filter: ((odt >= date_trunc('month'::text, (to_date('2019/10/01'::text, 'yyyy/MM/dd'::text))::timestamp with time zone)) AND (odt <= (date_trunc('month'::text, (to_date('2019/10/01'::text, 'yyyy/MM/dd'::text))::timestamp with time zone) + '1 mon -1 days'::interval)))
160
+
161
+ | +-GroupAggregate (cost=1350.68..1938.96 rows=242 width=61) (actual time=0.987..103.010 rows=145 loops=1645)
162
+
163
+ | +-Sort (cost=1350.68..1356.72 rows=2414 width=61) (actual time=0.012..0.300 rows=2197 loops=1645) Sort Key: c1.dod.ono, c1.dod.seq1, c1.dod.yyy
164
+
165
+ | +-Seq Scan on dod (cost=0.00..1215.05 rows=2414 width=61) (actual time=10.121..17.058 rows=2197 loops=1) Filter: ((odt >= date_trunc('month'::text, (to_date('2019/10/01'::text, 'yyyy/MM/dd'::text))::timestamp with time zone)) AND (odt <= (date_trunc('month'::text, (to_date('2019/10/01'::text, 'yyyy/MM/dd'::text))::timestamp with time zone) + '1 mon -1 days'::interval)))
166
+
167
+ +-Index Scan using idx_doi_c on doi oi (cost=0.00..2.72 rows=1 width=39) (actual time=0.014..0.014 rows=1 loops=1645) Index Cond: (((oi.ono)::text = (od.ono)::text) AND (oi.seq1 = od.seq1))
168
+
169
+ Total runtime: 169664.207 ms
170
+
171
+ ```