編集履歴

質問編集履歴

修正

2019/01/07 07:37

投稿

riomakopa

スコア30

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -86,13 +86,13 @@
 SQL
-```
  explain analyze
+```
- WITH tenant_post_detail_list AS
+WITH tenant_post_detail_list AS
         (SELECT
@@ -334,8 +334,6 @@
               ON  reply_check.max_reply_message_id = mm.message_id
         WHERE
             mrc.del_flg = 0
@@ -354,4 +352,4 @@
              ,mm.message_id ASC
-   ```
+```

修正

2019/01/07 07:37

投稿

riomakopa

スコア30

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -2,6 +2,10 @@
 考え方を教えて頂きたいです。
+ゴールは「適切にINDEXが張られているか」と
+どこでコストが掛かっているのかを解明したいです。
 クエリで一番コストがかかっているところは
@@ -42,13 +46,11 @@
 ↑の中に
-```
 「値のシーケンシャルI/Oで１ページを読みこむコストを1とした際の相対値で示される」とあります
-全文
 ```
@@ -72,120 +74,284 @@
                                   ->  Nested Loop  (cost=268.15..1182.94 rows=1 width=52) (actual time=11.120..35.718 rows=664 loops=1)	__**
-                                        Join Filter: (ct.revision = c.revision)
-                                        Rows Removed by Join Filter: 97
-                                        ->  Hash Join  (cost=267.88..348.99 rows=179 width=82) (actual time=11.067..14.269 rows=761 loops=1)
-                                              Hash Cond: (((t.tenant_code)::text = (u_1.tenant_code)::text) AND (t.revision = ct.revision))
-                                              ->  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)
-                                              ->  Hash  (cost=255.59..255.59 rows=819 width=63) (actual time=11.048..11.048 rows=761 loops=1)
-                                                    Buckets: 1024  Batches: 1  Memory Usage: 75kB
-                                                    ->  Hash Join  (cost=42.12..255.59 rows=819 width=63) (actual time=2.167..9.805 rows=761 loops=1)
-                                                          Hash Cond: ((u_1.tenant_code)::text = (ct.tenant_code)::text)
-                                                          ->  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)
-                                                          ->  Hash  (cost=32.61..32.61 rows=761 width=49) (actual time=2.134..2.134 rows=761 loops=1)
-                                                                Buckets: 1024  Batches: 1  Memory Usage: 63kB
-                                                                ->  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)
-                                        ->  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)
-                                              Index Cond: ((user_id)::text = (u_1.user_id)::text)
-                                              Filter: (((u_1.tenant_code)::text = (tenant_code)::text) AND (revision = (SubPlan 2)))
-                                              Rows Removed by Filter: 0
-                                              SubPlan 2
-                                                ->  Result  (cost=4.29..4.30 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=1025)
-                                                      InitPlan 1 (returns $1)
-                                                        ->  Limit  (cost=0.28..4.29 rows=1 width=4) (actual time=0.006..0.007 rows=1 loops=1025)
-                                                              ->  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)
-                                                                    Index Cond: ((tenant_code = (c.tenant_code)::text) AND (revision IS NOT NULL))
-                                                                    Heap Fetches: 0
-                                  ->  Hash Join  (cost=8.44..14.22 rows=1 width=10) (actual time=0.174..0.299 rows=1 loops=664)
-                                        Hash Cond: (((m_compartment.compartment_id)::text = (cp.compartment_id)::text) AND ((max(m_compartment.use_to)) = cp.use_to))
-                                        ->  HashAggregate  (cost=8.23..10.33 rows=210 width=11) (actual time=0.002..0.140 rows=210 loops=664)
-                                              Group Key: m_compartment.compartment_id
-                                              ->  Seq Scan on m_compartment  (cost=0.00..7.18 rows=210 width=11) (actual time=0.004..0.322 rows=210 loops=1)
-                                                    Filter: (('2019-01-07'::date >= use_from) AND ('2019-01-07'::date <= use_to))
-                                                    Rows Removed by Filter: 2
-                                        ->  Hash  (cost=0.19..0.19 rows=1 width=14) (actual time=0.009..0.009 rows=1 loops=664)
-                                              Buckets: 1024  Batches: 1  Memory Usage: 1kB
-                                              ->  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)
-                                                    Index Cond: ((ct.compartment_id)::text = (compartment_id)::text)
-                            ->  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)
-                                  Index Cond: ((user_id)::text = (ct.user_id)::text)
-                      ->  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)
-                            Index Cond: ((cp.zone_id)::text = (zone_id)::text)
-                ->  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)
-                      Index Cond: ((t.category_id1)::text = (category_id)::text)
-                      Filter: (category_kbn = 1)
-          ->  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)
-                Filter: ((del_flg = 0) AND (((sc_id)::text = 'marinoa'::text) OR ((sc_id)::text = 'ALL'::text)))
-                Rows Removed by Filter: 38
-  ->  Hash Left Join  (cost=4635.00..9786.84 rows=17 width=1394) (actual time=35.896..3585.607 rows=3790 loops=1)
-        Hash Cond: (mm.message_id = reply_check.max_reply_message_id)
-        ->  Nested Loop Left Join  (cost=133.38..5284.81 rows=17 width=1390) (actual time=27.238..3567.075 rows=3790 loops=1)
-              ->  Nested Loop  (cost=133.11..5279.44 rows=17 width=1386) (actual time=27.226..3547.307 rows=3790 loops=1)
-                    ->  Nested Loop  (cost=132.68..4502.88 rows=115 width=1370) (actual time=27.183..3164.768 rows=24443 loops=1)
-                          ->  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)
-                                Filter: ((((post_id)::text <> 'tenant'::text) AND ((user_id)::text = ' '::text)) OR (((post_id)::text = 'tenant'::text) AND ((user_id)::text <> ' '::text)))
-                                Rows Removed by Filter: 1
-                          ->  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)
 Planning time: 13.235 ms
 Execution time: 3611.990 ms
 ```
+SQL
+```
+ explain analyze
+ WITH tenant_post_detail_list AS
+        (SELECT
+            u.post_id AS post_id
+           ,u.user_id AS user_id
+           ,ct.tenant_code AS tenant_code
+           ,ct.tenant_name AS tenant_post_name
+           ,z.zone_id AS zone_id
+           ,z.zone_name AS zone_name
+           ,cg.category_id AS category_id
+           ,cg.category_name AS category_name
+        FROM
+            .v_max_revision_all_tenant AS mrt
+            INNER JOIN .m_contract AS ct
+            ON  mrt.tenant_code = ct.tenant_code
+            AND mrt.revision = ct.revision
+            INNER JOIN .m_tenant AS t
+            ON  mrt.tenant_code = t.tenant_code
+            AND mrt.revision = t.revision
+            INNER JOIN m_user AS u
+            ON ct.user_id = u.user_id
+            LEFT OUTER JOIN
+            (   SELECT
+                    cp.compartment_id
+                   ,cp.zone_id
+                   ,cp.floor_id
+                FROM
+                    .m_compartment AS cp
+                INNER JOIN (
+                    SELECT
+                    compartment_id
+                    ,MAX(use_to) AS max_use_to
+                FROM
+                    .m_compartment
+                    WHERE 'now' BETWEEN use_from AND use_to
+                    GROUP BY compartment_id
+                ) AS cp2
+                ON cp2.compartment_id=cp.compartment_id
+                AND cp2.max_use_to=cp.use_to
+            ) AS cp
+            ON ct.compartment_id = cp.compartment_id
+            LEFT OUTER JOIN .m_zone AS z
+            ON cp.zone_id = z.zone_id
+            LEFT OUTER JOIN .m_category AS cg
+            ON t.category_id1  = cg.category_id
+            AND cg.category_kbn = 1
+        UNION ALL
+        SELECT
+            p.post_id AS post_id
+           ,' ' AS user_id
+           ,' ' AS tenant_code
+           ,p.post_name AS tenant_post_name
+           ,' ' AS zone_id
+           ,' ' AS zone_name
+           ,' ' AS category_id
+           ,' ' AS category_name
+        FROM m_post AS p
+        WHERE
+            (p.sc_id = 'marinoa' OR p.sc_id = 'ALL')
+            AND p.del_flg = 0
+        )
+        SELECT
+            mm.*
+           ,mrc.message_receive_id
+           ,mrc.reg_time AS receive_date
+           ,tpl.tenant_post_name
+           ,mrp.reply_no
+           ,(CASE
+                WHEN mm.confirm_req_flg = 1
+                AND mrc.confirm_flg = 0
+                THEN 0
+                WHEN mm.confirm_req_flg = 0
+                AND mrc.read_flg = 0
+                THEN 1
+                WHEN
+                    (mm.confirm_req_flg = 1
+                     AND mrc.confirm_flg = 1)
+                     OR
+                     (mm.confirm_req_flg = 0
+                     AND mrc.read_flg = 1)
+                THEN 2
+             END
+            ) AS state
+           ,reply_check.max_reply_message_id IS NOT NULL AS replied_flg
+        FROM
+            .t_message_receive AS mrc
+            INNER JOIN .t_message_mng AS mm
+            ON  mm.message_id = mrc.message_id
+            AND
+                ((mm.span_flg =1
+                    AND mm.span_from   <=   '01/07/2019 00:00:00.000'
+                    AND mm.span_to   >=   '01/07/2019 00:00:00.000')
+                OR
+                    mm.span_Flg = 0)
+            INNER JOIN tenant_post_detail_list tpl
+            ON
+               (tpl.post_id = mm.post_id
+                AND tpl.post_id   <>   'tenant'
+                AND tpl.user_id = ' ')
+            OR
+               (tpl.user_id = mm.user_id
+                AND tpl.post_id = 'tenant'
+                AND tpl.user_id   <>   ' ')
+            LEFT OUTER JOIN .t_message_reply AS mrp
+            ON  mrp.message_receive_id = mrc.message_receive_id
+            AND mrp.reply_no = 1
+            LEFT OUTER JOIN
+                (SELECT max(reply_message_id) AS max_reply_message_id
+                FROM .t_message_mng
+                WHERE
+                 reply_message_id IS NOT NULL
+                    AND post_id = '20100'
+                GROUP BY reply_message_id
+                ) AS reply_check
+              ON  reply_check.max_reply_message_id = mm.message_id
+        WHERE
+            mrc.del_flg = 0
+            AND mrc.post_id = '20100'
+            AND mm.message_name LIKE '%%' ESCAPE '~'
+            AND mm.note LIKE '%%' ESCAPE '~'
+        ORDER BY
+              mrc.reg_time DESC
+             ,mm.message_id ASC
+   ```

修正

2019/01/07 07:35

投稿

riomakopa

スコア30

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -9,6 +9,10 @@
 以下の所だと思っているのですが
 あってますでしょうか？
 ```
@@ -31,6 +35,16 @@
 (コストは1以上あってはならないという認識があります）
 結果的にcost=10988.02..10988.07 になっているように思います。
+こちらは、先輩方に教えていただいたことですが
+https://lets.postgresql.jp/sites/default/files/2016-11/Explaining_Explain_ja.pdf
+↑の中に
+```
+「値のシーケンシャルI/Oで１ページを読みこむコストを1とした際の相対値で示される」とあります

追記

2019/01/07 07:30

投稿

riomakopa

スコア30

test CHANGED Viewed

File without changes

test CHANGED Viewed

@@ -1,6 +1,36 @@
 やりたいことはクエリコストの改善なのですが
 考え方を教えて頂きたいです。
+クエリで一番コストがかかっているところは
+以下の所だと思っているのですが
+あってますでしょうか？
+```
+__Append  (cost=277.15..1200.83 rows=14 width=204) (actual time=12.277..253.460 rows=678 loops=1)
+          ->  Nested Loop Left Join  (cost=277.15..1198.78 rows=1 width=93) (actual time=12.275..252.529 rows=664 loops=1)
+                ->  Nested Loop Left Join  (cost=277.01..1198.60 rows=1 width=76) (actual time=12.266..247.347 rows=664 loops=1)
+                      ->  Nested Loop  (cost=276.87..1198.38 rows=1 width=54) (actual time=12.257..242.928 rows=664 loops=1)
+                            ->  Nested Loop Left Join  (cost=276.59..1197.17 rows=1 width=48) (actual time=12.240..236.827 rows=664 loops=1)
+                                  ->  Nested Loop  (cost=268.15..1182.94 rows=1 width=52) (actual time=11.120..35.718 rows=664 loops=1)	__**
+```
+理由は、costが200以上掛かっているためです。
+(コストは1以上あってはならないという認識があります）
+結果的にcost=10988.02..10988.07 になっているように思います。
@@ -136,25 +166,7 @@
                           ->  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)
-                                Recheck Cond: (((tpl.post_id)::text = (post_id)::text) OR ((tpl.user_id)::text = (user_id)::text))
-                                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))))
-                                Rows Removed by Filter: 2690
-                                Heap Blocks: exact=949070
-                                ->  BitmapOr  (cost=132.68..132.68 rows=3214 width=0) (actual time=0.406..0.406 rows=0 loops=677)
-                                      ->  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)
-                                            Index Cond: ((tpl.post_id)::text = (post_id)::text)
-                                      ->  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)
-                                            Index Cond: ((tpl.user_id)::text = (user_id)::text)