質問編集履歴
2
遅くない方の実行計画を追加
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -83,4 +83,28 @@
|
|
|
83
83
|
| +-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)))
|
|
84
84
|
+-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))
|
|
85
85
|
Total runtime: 169664.207 ms
|
|
86
|
-
```
|
|
86
|
+
```
|
|
87
|
+
|
|
88
|
+
```ここに言語を入力
|
|
89
|
+
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))
|
|
90
|
+
+-HashAggregate (cost=51.65..52.94 rows=129 width=136) (actual time=0.216..0.237 rows=188 loops=1)
|
|
91
|
+
| +-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)
|
|
92
|
+
| +-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)
|
|
93
|
+
| +-Hash (cost=37.52..37.52 rows=189 width=18) (actual time=0.109..0.109 rows=188 loops=1)
|
|
94
|
+
| +-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))
|
|
95
|
+
+-Hash (cost=3345.97..3345.97 rows=1 width=68) (actual time=3364.415..3364.415 rows=35 loops=1)
|
|
96
|
+
+-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)
|
|
97
|
+
+-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))
|
|
98
|
+
| +-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)
|
|
99
|
+
| | +-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)))
|
|
100
|
+
| | +-GroupAggregate (cost=1350.68..1383.88 rows=242 width=48) (actual time=19.845..20.747 rows=145 loops=1)
|
|
101
|
+
| | +-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
|
|
102
|
+
| | +-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)))
|
|
103
|
+
| +-GroupAggregate (cost=1350.68..1938.96 rows=242 width=61) (actual time=1.461..95.469 rows=145 loops=35)
|
|
104
|
+
| +-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
|
|
105
|
+
| +-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)))
|
|
106
|
+
+-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))
|
|
107
|
+
Total runtime: 3365.548 ms
|
|
108
|
+
|
|
109
|
+
```
|
|
110
|
+
遅くない方の実行計画です。
|
1
コードの編集・実行計画の追加
title
CHANGED
|
File without changes
|
body
CHANGED
|
@@ -1,12 +1,15 @@
|
|
|
1
1
|
変更前と同等の時間位で処理が行いたいです。
|
|
2
2
|
|
|
3
|
+
```ここに言語を入力
|
|
3
4
|
select a.xxx
|
|
4
5
|
from (
|
|
5
|
-
sql1
|
|
6
|
+
sql1
|
|
6
7
|
) a
|
|
7
8
|
left join (
|
|
8
|
-
sql2
|
|
9
|
+
sql2
|
|
9
10
|
) b on a.xxx = b.xxx and a.yyy = b.yyy
|
|
11
|
+
|
|
12
|
+
```
|
|
10
13
|
（xxx,yyy はsql1のメインテーブルのキー項目です）
|
|
11
14
|
|
|
12
15
|
現在
|
|
@@ -25,6 +28,7 @@
|
|
|
25
28
|
|
|
26
29
|
sql1の結果が減少しているのに処理時間が劇的に増加する要因はどんなことが考えられるでしょうか？sql2の変更はありません。
|
|
27
30
|
|
|
31
|
+
```ここに言語を入力
|
|
28
32
|
変更前sql1
|
|
29
33
|
select
|
|
30
34
|
a1.xxx
|
|
@@ -54,4 +58,29 @@
|
|
|
54
58
|
and a1.xxx = 1 （この行を追加しました）
|
|
55
59
|
group by
|
|
56
60
|
a1.xxx
|
|
57
|
-
, a1.yyy
|
|
61
|
+
, a1.yyy
|
|
62
|
+
```
|
|
63
|
+
|
|
64
|
+
|
|
65
|
+
データベースはpostgresでA5により実行計画を出力しました。
|
|
66
|
+
よろしくお願いいたします。
|
|
67
|
+
|
|
68
|
+
```ここに言語を入力
|
|
69
|
+
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))
|
|
70
|
+
+-HashAggregate (cost=17.87..17.88 rows=1 width=136) (actual time=0.195..0.252 rows=47 loops=1)
|
|
71
|
+
| +-Nested Loop (cost=0.00..17.86 rows=1 width=136) (actual time=0.023..0.180 rows=47 loops=1)
|
|
72
|
+
| +-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)
|
|
73
|
+
| +-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))
|
|
74
|
+
+-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)
|
|
75
|
+
+-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))
|
|
76
|
+
| +-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)
|
|
77
|
+
| | +-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)))
|
|
78
|
+
| | +-GroupAggregate (cost=1350.68..1383.88 rows=242 width=48) (actual time=0.430..1.526 rows=145 loops=47)
|
|
79
|
+
| | +-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
|
|
80
|
+
| | +-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)))
|
|
81
|
+
| +-GroupAggregate (cost=1350.68..1938.96 rows=242 width=61) (actual time=0.987..103.010 rows=145 loops=1645)
|
|
82
|
+
| +-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
|
|
83
|
+
| +-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)))
|
|
84
|
+
+-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))
|
|
85
|
+
Total runtime: 169664.207 ms
|
|
86
|
+
```
|