teratail
回答率
85.29%
質問するログイン新規登録
トップに関する質問クエリコストの見方　ポスグレ

編集履歴

質問編集履歴

4

修正

2019/01/07 07:37

投稿

riomakopa
riomakopa

スコア30

title CHANGED
File without changes
body CHANGED
@@ -42,10 +42,10 @@
42
42
  ```
43
43
 
44
44
  SQL
45
+
46
+ explain analyze
45
47
  ```
46
- explain analyze
47
-
48
- WITH tenant_post_detail_list AS
48
+ WITH tenant_post_detail_list AS
49
49
  (SELECT
50
50
  u.post_id AS post_id
51
51
  ,u.user_id AS user_id
@@ -166,7 +166,6 @@
166
166
  GROUP BY reply_message_id
167
167
  ) AS reply_check
168
168
  ON reply_check.max_reply_message_id = mm.message_id
169
-
170
169
  WHERE
171
170
  mrc.del_flg = 0
172
171
  AND mrc.post_id = '20100'
@@ -176,4 +175,4 @@
176
175
 
177
176
  mrc.reg_time DESC
178
177
  ,mm.message_id ASC
179
- ```
178
+ ```

3

修正

2019/01/07 07:37

投稿

riomakopa
riomakopa

スコア30

title CHANGED
File without changes
body CHANGED
@@ -1,5 +1,7 @@
1
1
  やりたいことはクエリコストの改善なのですが
2
2
  考え方を教えて頂きたいです。
3
+ ゴールは「適切にINDEXが張られているか」と
4
+ どこでコストが掛かっているのかを解明したいです。
3
5
 
4
6
  クエリで一番コストがかかっているところは
5
7
  以下の所だと思っているのですが
@@ -20,10 +22,9 @@
20
22
  こちらは、先輩方に教えていただいたことですが
21
23
  https://lets.postgresql.jp/sites/default/files/2016-11/Explaining_Explain_ja.pdf
22
24
  ↑の中に
23
- ```
24
25
  「値のシーケンシャルI/Oで１ページを読みこむコストを1とした際の相対値で示される」とあります
25
26
 
26
- 全文
27
+
27
28
  ```
28
29
  Sort (cost=10988.02..10988.07 rows=17 width=1394) (actual time=3605.324..3608.863 rows=3790 loops=1)
29
30
  Sort Key: mrc.reg_time, mm.message_id
@@ -35,62 +36,144 @@
35
36
  -> Nested Loop (cost=276.87..1198.38 rows=1 width=54) (actual time=12.257..242.928 rows=664 loops=1)
36
37
  -> Nested Loop Left Join (cost=276.59..1197.17 rows=1 width=48) (actual time=12.240..236.827 rows=664 loops=1)
37
38
  -> Nested Loop (cost=268.15..1182.94 rows=1 width=52) (actual time=11.120..35.718 rows=664 loops=1) __**
38
- Join Filter: (ct.revision = c.revision)
39
- Rows Removed by Join Filter: 97
40
- -> Hash Join (cost=267.88..348.99 rows=179 width=82) (actual time=11.067..14.269 rows=761 loops=1)
41
- Hash Cond: (((t.tenant_code)::text = (u_1.tenant_code)::text) AND (t.revision = ct.revision))
42
- -> Seq Scan on m_tenant t (cost=0.00..73.61 rows=761 width=19) (actual time=0.005..0.925 rows=761 loops=1)
43
- -> Hash (cost=255.59..255.59 rows=819 width=63) (actual time=11.048..11.048 rows=761 loops=1)
44
- Buckets: 1024 Batches: 1 Memory Usage: 75kB
45
- -> Hash Join (cost=42.12..255.59 rows=819 width=63) (actual time=2.167..9.805 rows=761 loops=1)
46
- Hash Cond: ((u_1.tenant_code)::text = (ct.tenant_code)::text)
47
- -> Seq Scan on m_user u_1 (cost=0.00..179.95 rows=2895 width=14) (actual time=0.019..2.987 rows=2895 loops=1)
48
- -> Hash (cost=32.61..32.61 rows=761 width=49) (actual time=2.134..2.134 rows=761 loops=1)
49
- Buckets: 1024 Batches: 1 Memory Usage: 63kB
50
- -> Seq Scan on m_contract ct (cost=0.00..32.61 rows=761 width=49) (actual time=0.014..1.136 rows=761 loops=1)
51
- -> Index Scan using m_contract_idx_user_id on m_contract c (cost=0.28..4.65 rows=1 width=18) (actual time=0.021..0.024 rows=1 loops=761)
52
- Index Cond: ((user_id)::text = (u_1.user_id)::text)
53
- Filter: (((u_1.tenant_code)::text = (tenant_code)::text) AND (revision = (SubPlan 2)))
54
- Rows Removed by Filter: 0
55
- SubPlan 2
56
- -> Result (cost=4.29..4.30 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=1025)
57
- InitPlan 1 (returns $1)
58
- -> Limit (cost=0.28..4.29 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=1025)
59
- -> Index Only Scan Backward using m_contract_pkey on m_contract (cost=0.28..4.29 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1025)
60
- Index Cond: ((tenant_code = (c.tenant_code)::text) AND (revision IS NOT NULL))
61
- Heap Fetches: 0
62
- -> Hash Join (cost=8.44..14.22 rows=1 width=10) (actual time=0.174..0.299 rows=1 loops=664)
63
- Hash Cond: (((m_compartment.compartment_id)::text = (cp.compartment_id)::text) AND ((max(m_compartment.use_to)) = cp.use_to))
64
- -> HashAggregate (cost=8.23..10.33 rows=210 width=11) (actual time=0.002..0.140 rows=210 loops=664)
65
- Group Key: m_compartment.compartment_id
66
- -> Seq Scan on m_compartment (cost=0.00..7.18 rows=210 width=11) (actual time=0.004..0.322 rows=210 loops=1)
67
- Filter: (('2019-01-07'::date >= use_from) AND ('2019-01-07'::date <= use_to))
68
- Rows Removed by Filter: 2
69
- -> Hash (cost=0.19..0.19 rows=1 width=14) (actual time=0.009..0.009 rows=1 loops=664)
70
- Buckets: 1024 Batches: 1 Memory Usage: 1kB
71
- -> Index Scan using m_compartment_pkey on m_compartment cp (cost=0.14..0.19 rows=1 width=14) (actual time=0.004..0.005 rows=1 loops=664)
72
- Index Cond: ((ct.compartment_id)::text = (compartment_id)::text)
73
- -> Index Scan using m_user_pkey on m_user u (cost=0.28..1.19 rows=1 width=12) (actual time=0.005..0.006 rows=1 loops=664)
74
- Index Cond: ((user_id)::text = (ct.user_id)::text)
75
- -> Index Scan using m_zone_pkey on m_zone z (cost=0.14..0.21 rows=1 width=25) (actual time=0.003..0.004 rows=1 loops=664)
76
- Index Cond: ((cp.zone_id)::text = (zone_id)::text)
77
- -> Index Scan using m_category_pkey on m_category cg (cost=0.14..0.18 rows=1 width=24) (actual time=0.003..0.005 rows=1 loops=664)
78
- Index Cond: ((t.category_id1)::text = (category_id)::text)
79
- Filter: (category_kbn = 1)
80
- -> Seq Scan on m_post p (cost=0.00..1.91 rows=13 width=20) (actual time=0.010..0.077 rows=14 loops=1)
81
- Filter: ((del_flg = 0) AND (((sc_id)::text = 'marinoa'::text) OR ((sc_id)::text = 'ALL'::text)))
82
- Rows Removed by Filter: 38
83
- -> Hash Left Join (cost=4635.00..9786.84 rows=17 width=1394) (actual time=35.896..3585.607 rows=3790 loops=1)
84
- Hash Cond: (mm.message_id = reply_check.max_reply_message_id)
85
- -> Nested Loop Left Join (cost=133.38..5284.81 rows=17 width=1390) (actual time=27.238..3567.075 rows=3790 loops=1)
86
- -> Nested Loop (cost=133.11..5279.44 rows=17 width=1386) (actual time=27.226..3547.307 rows=3790 loops=1)
87
- -> Nested Loop (cost=132.68..4502.88 rows=115 width=1370) (actual time=27.183..3164.768 rows=24443 loops=1)
88
- -> CTE Scan on tenant_post_detail_list tpl (cost=0.00..0.42 rows=1 width=102) (actual time=12.285..256.152 rows=677 loops=1)
89
- Filter: ((((post_id)::text <> 'tenant'::text) AND ((user_id)::text = ' '::text)) OR (((post_id)::text = 'tenant'::text) AND ((user_id)::text <> ' '::text)))
90
- Rows Removed by Filter: 1
91
- -> Bitmap Heap Scan on t_message_mng mm (cost=132.68..4502.30 rows=16 width=1338) (actual time=3.181..4.243 rows=36 loops=677)
92
-
93
-
39
+
94
40
  Planning time: 13.235 ms
95
41
  Execution time: 3611.990 ms
96
- ```
42
+ ```
43
+
44
+ SQL
45
+ ```
46
+ explain analyze
47
+
48
+ WITH tenant_post_detail_list AS
49
+ (SELECT
50
+ u.post_id AS post_id
51
+ ,u.user_id AS user_id
52
+ ,ct.tenant_code AS tenant_code
53
+ ,ct.tenant_name AS tenant_post_name
54
+ ,z.zone_id AS zone_id
55
+ ,z.zone_name AS zone_name
56
+ ,cg.category_id AS category_id
57
+ ,cg.category_name AS category_name
58
+ FROM
59
+ .v_max_revision_all_tenant AS mrt
60
+ INNER JOIN .m_contract AS ct
61
+ ON mrt.tenant_code = ct.tenant_code
62
+ AND mrt.revision = ct.revision
63
+ INNER JOIN .m_tenant AS t
64
+ ON mrt.tenant_code = t.tenant_code
65
+ AND mrt.revision = t.revision
66
+ INNER JOIN m_user AS u
67
+ ON ct.user_id = u.user_id
68
+ LEFT OUTER JOIN
69
+ ( SELECT
70
+ cp.compartment_id
71
+ ,cp.zone_id
72
+ ,cp.floor_id
73
+ FROM
74
+ .m_compartment AS cp
75
+ INNER JOIN (
76
+ SELECT
77
+ compartment_id
78
+ ,MAX(use_to) AS max_use_to
79
+ FROM
80
+ .m_compartment
81
+ WHERE 'now' BETWEEN use_from AND use_to
82
+ GROUP BY compartment_id
83
+ ) AS cp2
84
+ ON cp2.compartment_id=cp.compartment_id
85
+ AND cp2.max_use_to=cp.use_to
86
+ ) AS cp
87
+ ON ct.compartment_id = cp.compartment_id
88
+ LEFT OUTER JOIN .m_zone AS z
89
+ ON cp.zone_id = z.zone_id
90
+ LEFT OUTER JOIN .m_category AS cg
91
+ ON t.category_id1 = cg.category_id
92
+ AND cg.category_kbn = 1
93
+
94
+ UNION ALL
95
+
96
+ SELECT
97
+ p.post_id AS post_id
98
+ ,' ' AS user_id
99
+ ,' ' AS tenant_code
100
+ ,p.post_name AS tenant_post_name
101
+ ,' ' AS zone_id
102
+ ,' ' AS zone_name
103
+ ,' ' AS category_id
104
+ ,' ' AS category_name
105
+ FROM m_post AS p
106
+ WHERE
107
+ (p.sc_id = 'marinoa' OR p.sc_id = 'ALL')
108
+ AND p.del_flg = 0
109
+ )
110
+
111
+ SELECT
112
+ mm.*
113
+ ,mrc.message_receive_id
114
+ ,mrc.reg_time AS receive_date
115
+ ,tpl.tenant_post_name
116
+ ,mrp.reply_no
117
+ ,(CASE
118
+ WHEN mm.confirm_req_flg = 1
119
+ AND mrc.confirm_flg = 0
120
+ THEN 0
121
+ WHEN mm.confirm_req_flg = 0
122
+ AND mrc.read_flg = 0
123
+ THEN 1
124
+ WHEN
125
+ (mm.confirm_req_flg = 1
126
+ AND mrc.confirm_flg = 1)
127
+ OR
128
+ (mm.confirm_req_flg = 0
129
+ AND mrc.read_flg = 1)
130
+ THEN 2
131
+ END
132
+ ) AS state
133
+ ,reply_check.max_reply_message_id IS NOT NULL AS replied_flg
134
+
135
+ FROM
136
+ .t_message_receive AS mrc
137
+ INNER JOIN .t_message_mng AS mm
138
+ ON mm.message_id = mrc.message_id
139
+ AND
140
+ ((mm.span_flg =1
141
+ AND mm.span_from <= '01/07/2019 00:00:00.000'
142
+ AND mm.span_to >= '01/07/2019 00:00:00.000')
143
+ OR
144
+ mm.span_Flg = 0)
145
+ INNER JOIN tenant_post_detail_list tpl
146
+ ON
147
+ (tpl.post_id = mm.post_id
148
+ AND tpl.post_id <> 'tenant'
149
+ AND tpl.user_id = ' ')
150
+ OR
151
+ (tpl.user_id = mm.user_id
152
+ AND tpl.post_id = 'tenant'
153
+ AND tpl.user_id <> ' ')
154
+ LEFT OUTER JOIN .t_message_reply AS mrp
155
+ ON mrp.message_receive_id = mrc.message_receive_id
156
+ AND mrp.reply_no = 1
157
+ LEFT OUTER JOIN
158
+ (SELECT max(reply_message_id) AS max_reply_message_id
159
+ FROM .t_message_mng
160
+ WHERE
161
+ reply_message_id IS NOT NULL
162
+
163
+
164
+ AND post_id = '20100'
165
+
166
+ GROUP BY reply_message_id
167
+ ) AS reply_check
168
+ ON reply_check.max_reply_message_id = mm.message_id
169
+
170
+ WHERE
171
+ mrc.del_flg = 0
172
+ AND mrc.post_id = '20100'
173
+ AND mm.message_name LIKE '%%' ESCAPE '~'
174
+ AND mm.note LIKE '%%' ESCAPE '~'
175
+ ORDER BY
176
+
177
+ mrc.reg_time DESC
178
+ ,mm.message_id ASC
179
+ ```

2

修正

2019/01/07 07:35

投稿

riomakopa
riomakopa

スコア30

title CHANGED
File without changes
body CHANGED
@@ -4,6 +4,8 @@
4
4
  クエリで一番コストがかかっているところは
5
5
  以下の所だと思っているのですが
6
6
  あってますでしょうか？
7
+
8
+
7
9
  ```
8
10
  __Append (cost=277.15..1200.83 rows=14 width=204) (actual time=12.277..253.460 rows=678 loops=1)
9
11
  -> Nested Loop Left Join (cost=277.15..1198.78 rows=1 width=93) (actual time=12.275..252.529 rows=664 loops=1)
@@ -15,6 +17,11 @@
15
17
  理由は、costが200以上掛かっているためです。
16
18
  (コストは1以上あってはならないという認識があります）
17
19
  結果的にcost=10988.02..10988.07 になっているように思います。
20
+ こちらは、先輩方に教えていただいたことですが
21
+ https://lets.postgresql.jp/sites/default/files/2016-11/Explaining_Explain_ja.pdf
22
+ ↑の中に
23
+ ```
24
+ 「値のシーケンシャルI/Oで１ページを読みこむコストを1とした際の相対値で示される」とあります
18
25
 
19
26
  全文
20
27
  ```

1

追記

2019/01/07 07:30

投稿

riomakopa
riomakopa

スコア30

title CHANGED
File without changes
body CHANGED
@@ -1,6 +1,21 @@
1
1
  やりたいことはクエリコストの改善なのですが
2
2
  考え方を教えて頂きたいです。
3
3
 
4
+ クエリで一番コストがかかっているところは
5
+ 以下の所だと思っているのですが
6
+ あってますでしょうか？
7
+ ```
8
+ __Append (cost=277.15..1200.83 rows=14 width=204) (actual time=12.277..253.460 rows=678 loops=1)
9
+ -> Nested Loop Left Join (cost=277.15..1198.78 rows=1 width=93) (actual time=12.275..252.529 rows=664 loops=1)
10
+ -> Nested Loop Left Join (cost=277.01..1198.60 rows=1 width=76) (actual time=12.266..247.347 rows=664 loops=1)
11
+ -> Nested Loop (cost=276.87..1198.38 rows=1 width=54) (actual time=12.257..242.928 rows=664 loops=1)
12
+ -> Nested Loop Left Join (cost=276.59..1197.17 rows=1 width=48) (actual time=12.240..236.827 rows=664 loops=1)
13
+ -> Nested Loop (cost=268.15..1182.94 rows=1 width=52) (actual time=11.120..35.718 rows=664 loops=1) __**
14
+ ```
15
+ 理由は、costが200以上掛かっているためです。
16
+ (コストは1以上あってはならないという認識があります）
17
+ 結果的にcost=10988.02..10988.07 になっているように思います。
18
+
4
19
  全文
5
20
  ```
6
21
  Sort (cost=10988.02..10988.07 rows=17 width=1394) (actual time=3605.324..3608.863 rows=3790 loops=1)
@@ -67,16 +82,7 @@
67
82
  Filter: ((((post_id)::text <> 'tenant'::text) AND ((user_id)::text = ' '::text)) OR (((post_id)::text = 'tenant'::text) AND ((user_id)::text <> ' '::text)))
68
83
  Rows Removed by Filter: 1
69
84
  -> Bitmap Heap Scan on t_message_mng mm (cost=132.68..4502.30 rows=16 width=1338) (actual time=3.181..4.243 rows=36 loops=677)
70
- Recheck Cond: (((tpl.post_id)::text = (post_id)::text) OR ((tpl.user_id)::text = (user_id)::text))
71
- Filter: (((message_name)::text ~~ '%%'::text) AND ((note)::text ~~ '%%'::text) AND (((span_flg = 1) AND (span_from <= '2019-01-07'::date) AND (span_to >= '2019-01-07'::date)) OR (span_flg = 0)) AND ((((tpl.post_id)::text = (post_id)::text) AND ((tpl.post_id)::text <> 'tenant'::text) AND ((tpl.user_id)::text = ' '::text)) OR (((tpl.user_id)::text = (user_id)::text) AND ((tpl.post_id)::text = 'tenant'::text) AND ((tpl.user_id)::text <> ' '::text))))
72
- Rows Removed by Filter: 2690
73
- Heap Blocks: exact=949070
74
- -> BitmapOr (cost=132.68..132.68 rows=3214 width=0) (actual time=0.406..0.406 rows=0 loops=677)
75
- -> Bitmap Index Scan on t_message_mng_idx_post_id (cost=0.00..127.57 rows=3104 width=0) (actual time=0.353..0.353 rows=2327 loops=677)
76
- Index Cond: ((tpl.post_id)::text = (post_id)::text)
77
- -> Bitmap Index Scan on t_message_mng_idx_user_id (cost=0.00..5.10 rows=109 width=0) (actual time=0.049..0.049 rows=435 loops=677)
78
- Index Cond: ((tpl.user_id)::text = (user_id)::text)
79
-
85
+
80
86
 
81
87
  Planning time: 13.235 ms
82
88
  Execution time: 3611.990 ms