質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.49%

SQLの処理時間での質問です。

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 826

score 5

変更前と同等の時間位で処理が行いたいです。

select a.xxx
from (
    sql1
) a
left join (
    sql2
) b on a.xxx = b.xxx and  a.yyy = b.yyy


(xxx,yyy はsql1のメインテーブルのキー項目です)

現在
sql1を処理するのに0.004s 取得件数188件
sql2を処理するのに0.044s 取得件数35件
上記sqlを処理するのに3.455s 取得件数188件
かかります。

変更後
sql1に xxx = 1 という条件を付加すると
sql1を処理するのに0.004s 取得件数47件
sql2を処理するのに0.044s 取得件数35件
上記sqlを処理するのに169.268s
かかります。

sql1の結果が減少しているのに処理時間が劇的に増加する要因はどんなことが考えられるでしょうか?sql2の変更はありません。

変更前sql1
    select
       a1.xxx
      , a1.yyy 
    from
      m_aaa a1 
      inner join m_bbb b1 
        on a1.yyy = b1.yyy 
    where
      b1.baaa <> 1 
      and b1.bccc = 1 
    group by
      a1.xxx
      , a1.yyy

変更後sql1
    select
       a1.xxx
      , a1.yyy 
    from
      m_aaa a1 
      inner join m_bbb b1 
        on a1.yyy = b1.yyy 
    where
      b1.baaa <> 1 
      and b1.bccc = 1 
      and a1.xxx = 1    (この行を追加しました)
    group by
      a1.xxx
      , a1.yyy

データベースはpostgresでA5により実行計画を出力しました。
よろしくお願いいたします。

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))
 +-HashAggregate  (cost=17.87..17.88 rows=1 width=136) (actual time=0.195..0.252 rows=47 loops=1)
 |  +-Nested Loop  (cost=0.00..17.86 rows=1 width=136) (actual time=0.023..0.180 rows=47 loops=1)
 |     +-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)
 |     +-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))
 +-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)
    +-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))
    |  +-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)
    |  |  +-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)))
    |  |  +-GroupAggregate  (cost=1350.68..1383.88 rows=242 width=48) (actual time=0.430..1.526 rows=145 loops=47)
    |  |     +-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
    |  |        +-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)))
    |  +-GroupAggregate  (cost=1350.68..1938.96 rows=242 width=61) (actual time=0.987..103.010 rows=145 loops=1645)
    |     +-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
    |        +-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)))
    +-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))
Total runtime: 169664.207 ms
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))
 +-HashAggregate  (cost=51.65..52.94 rows=129 width=136) (actual time=0.216..0.237 rows=188 loops=1)
 |  +-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)
 |     +-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)
 |     +-Hash  (cost=37.52..37.52 rows=189 width=18) (actual time=0.109..0.109 rows=188 loops=1)
 |        +-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))
 +-Hash  (cost=3345.97..3345.97 rows=1 width=68) (actual time=3364.415..3364.415 rows=35 loops=1)
    +-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)
       +-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))
       |  +-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)
       |  |  +-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)))
       |  |  +-GroupAggregate  (cost=1350.68..1383.88 rows=242 width=48) (actual time=19.845..20.747 rows=145 loops=1)
       |  |     +-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
       |  |        +-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)))
       |  +-GroupAggregate  (cost=1350.68..1938.96 rows=242 width=61) (actual time=1.461..95.469 rows=145 loops=35)
       |     +-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
       |        +-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)))
       +-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))
Total runtime: 3365.548 ms


遅くない方の実行計画です。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • Orlofsky

    2019/10/11 15:45

    SQLなどのコードは https://teratail.com/help/question-tips#questionTips3-7 の [コード] に修正してください。

    キャンセル

  • sazi

    2019/10/11 15:46

    DBMSの明記及び実行計画を取得し、質問に追記して下さい。

    キャンセル

  • m.ts10806

    2019/10/11 15:48

    あまり仮の話すぎると可能性の話すらもしづらいので実際のテーブル定義も提示いただいた方が

    キャンセル

  • sazi

    2019/10/11 17:32

    遅くない方の実行計画も追記して下さい。

    キャンセル

回答 1

checkベストアンサー

0

考えられるものとして、
1.sql1の条件を追加した事により、使用されるインデックスが変わる。
2.そのインデックスに結合条件の項目が含まれていないまたは含まれていても非効率。
という事で、2の結合の際に遅くなっているという事が考えられます。

実行計画を確認すれば、もっと明らかにはなるでしょうが。

追記

変更後

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


変更前

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


実行計画で見ての通り結合時のメソッドが変わってしまっていて、actual timeが増大していますね。
ここが変更前と同じくhash joinになるような、インデックスを適用して下さい。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/10/12 00:20 編集

    SQLにしても実行計画にしても加工してますよね?
    加工してもいいですけど、少なくとも整合性の取れた、テーブル定義やインデックスとそれに合ったSQLと実行計画を提示されない限り役に立てる回答はできないです。
    そもそも、ヒント程度の回答で何とかなるというレベルの情報しか無い質問ですから。

    キャンセル

  • 2019/10/12 00:28 編集

    現時点で言える事は、「結合条件になっている項目すべてが含まれるようなインデックスを作成してみて」程度です。
    それでもアクセスプランが変わるかどうかは保証されませんが。

    キャンセル

  • 2019/10/12 13:20

    すみません。できるかぎり整合性をとったつもりでしたが変換忘れ等ありました。
    インデックス作成してやってみます。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.49%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る