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

Question

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

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

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

Comments

[Orlofsky]

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

[sazi]

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

[m.ts10806]

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

[sazi]

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

Answer 1

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

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

追記

変更後

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になるような、インデックスを適用して下さい。

Comments

[rezaq]

「hash joinにするようなインデックスを適用」とするには、どのようにすればよいのでしょうか？

[Orlofsky]

データベースによって指定方法が違うから、どのデータベースを使うのかを質問のタグで示したり、バージョンも明記した方が適切なコメントが付き易いです。他の人も書いているのに気が付きませんか？それとも無視？

[sazi]

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

[sazi]

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

[rezaq]

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