クエリコストの見方 ポスグレ

Question

やりたいことはクエリコストの改善なのですが
考え方を教えて頂きたいです。
ゴールは「適切にINDEXが張られているか」と
どこでコストが掛かっているのかを解明したいです。

クエリで一番コストがかかっているところは
以下の所だと思っているのですが
あってますでしょうか？

__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 になっているように思います。
こちらは、先輩方に教えていただいたことですが
https://lets.postgresql.jp/sites/default/files/2016-11/Explaining_Explain_ja.pdf
↑の中に
「値のシーケンシャルI/Oで１ページを読みこむコストを1とした際の相対値で示される」とあります

Sort  (cost=10988.02..10988.07 rows=17 width=1394) (actual time=3605.324..3608.863 rows=3790 loops=1)	
  Sort Key: mrc.reg_time, mm.message_id	
  Sort Method: external merge  Disk: 3184kB	
  CTE tenant_post_detail_list	
    -> ** __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)	__**
            
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

Comments

[m.ts10806]

>コストは1以上あってはならないという認識
この根拠は何でしょうか？記事など出展を質問本文に追記してください

[riomakopa]

こちらは、先輩方に教えていただいたことですが
https://lets.postgresql.jp/sites/default/files/2016-11/Explaining_Explain_ja.pdf
↑の中に
```
「値のシーケンシャルI/Oで１ページを読みこむコストを1とした際の相対値で示される」とあります

[m.ts10806]

質問本文に追記してください(質問は編集できます)
ただ、目的が「コストの改善」なのであれば実行計画の結果だけではある程度原因は追えても具体的な解決策の提案とはならないように思いますが、この質問のゴールはどこに置いていますか？その辺りも含めて質問に追記してください

[退会済みユーザー]

Nested Loop ではじめからコストが高いやつが、なんの式を書いているか次第のような気がするので、結局SQL文を見ないとはじまらない。

[Orlofsky]

https://teratail.com/questions/167224 のように使っているテーブルの CREATE TABLE, CREATE INDEX と INSERT で提示するとSQLをすぐ実行できるので、適切なコメントが付き易いかと。

[退会済みユーザー]

有識者にお金を払ってチューニングしてもらったほうがいいと思いますが無料でしたいなら
関連する DDL を全部出してもらわないと根本敵な解決にはならないとおもいます。

というか、これここで出しちゃだめなSQLにみえる（先方（問い合わせ先）があるってことは守秘義務違反は大丈夫なのか）

Answer 1

(コストは1以上あってはならないという認識があります）

関係ないですね。

値のシーケンシャルI/Oで１ページを読みこむコストを1とした際の相対値で示される

これはコスト値の値がどのようなものかの説明です。

確認するべきは、actual timeです。

チューニングが必要なのは以下の部分です。

->  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) 

CTE Scan部分は244ms掛かっていますので、可能ならチューニングですけど、それよりも、それとの突合でNested Loop部分が3000msと500ms位になってますので、ここを何とかしないと改善されません。

追記

※SQLが追加されたので。
一番コストが掛かっているのは以下ですね。

            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   <>   ' ')

ここは先ず、t_message_mngにpost_idとuser_idのインデックスが必要ですね。
先ずは両方の項目を持ったインデックスを適用して、改善されないなら、別々のインデックスにして、or結合じゃなく、left join で各々結合する。

追記

以下は問題部分だけのSQLです。
先ずはこれで検証してみましょう。

WITH tenant_post_detail_list AS (select ～)
SELECT
     mm.*
    ,tpl.tenant_post_name
 FROM
    .t_message_mng AS mm
    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   <>   ' ')

上記を計測したのち、以下の組み替えたものを実行してみてください。

WITH tenant_post_detail_list AS (select ～)
SELECT
     mm.*
   , case when tpl1.post_id <> 'tenant' AND tpl1.user_id = ' ' then tpl1.tenant_post_name
     else case when tpl2.post_id = 'tenant' AND tpl2.user_id <> ' ' then tpl2.tenant_post_name
     end end tenant_post_name
 FROM
    .t_message_mng AS mm
    left JOIN tenant_post_detail_list tpl1
    ON .post_id = mm.post_id or 
    left JOIN tenant_post_detail_list tpl2
    ON tpl2.user_id = mm.user_id

さらに、t_message_mngにpost_idとuser_idのインデックスを追加し計測してみて下さい。
効果が無ければ、インデックスが適用されるようにSQLを見直しです。

Comments

[退会済みユーザー]

そこのNested Loopがすっごく気になる。

[riomakopa]

sazi様　m6u様
お二人にご教授頂いたことを整理してやってみます。
ありがとうございます。
また、お聞きするかもしれませんがよろしくお願いいたします。

[riomakopa]

m6u様

mm.span_from と mm.span_toはdate型でした。

sazi様
お恥ずかしいですが、別々のINDEXにするということは以下であってますでしょうか？

‘‘‘
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)
LEFT JOIN tenant_post_detail_list tpl
ON
(tpl.post_id = mm.post_id
AND tpl.post_id <> 'tenant'
AND tpl.user_id = ' ')
‘‘‘
↑に変更したことで体感です4秒かかっていたのが
0.5秒に短縮できたのですが・・自身がないです・・

[sazi]

別々のINDEXにするというのは、問い合わせを別にするという事になります。
(SQLを分けるのでななくサブクエリーにするということ）
t_message_mng とtenant_post_detail_list をそれぞれの条件で結合したものをunionすることになります。

[sazi]

追記したので、確認してみて下さい。

Answer 2

テーブルの構造も教えてほしいところだけど、
以下、気になったところ。

mm.span_from と mm.span_to ってtimestamp型だろうか、
もし文字列での比較をやっているならtimestamp型を駆使できるように構造を見直すべきで
インデックスが利いてなかったりしないかな。

tpl.user_id や tpl.post_id が文字列っぽいけどvarcharなのかそれともtextなのか。
使用する文字列のパターンが数限られるのであればいっそ整数値にしてしまって、
インデックスを利かせやすくしたほうが良いのではないかと。

REINDEX文の活用も。

->  Nested Loop  (cost=268.15..1182.94 rows=1 width=52) (actual time=11.120..35.718 rows=664 loops=1)

664行ひねりだすのに実実行時間で11～35ミリ秒35引く11で24ミリ秒もかかってるここが気になる。
インデックス使えてないんだろうね。

Comments

[sazi]

時間の単位はミリ秒(ms)ですよ。
また、actual timeは、開始...終了で表現されるので、差分が実行に掛かった時間です。
なので、指摘部分は、35-11=24msです。

[退会済みユーザー]

こっ恥ずかしいミスのご指摘感謝します。