teratail
回答率
85.48%
質問するログイン新規登録

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

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

新規登録して質問してみよう
ただいま回答率
85.48%
トップSQLに関する質問
SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Q&A

解決済

1回答

443閲覧

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

rezaq
rezaq

総合スコア5

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

0グッド

0クリップ

投稿2019/10/11 06:37

編集2019/10/11 09:08

0

0

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

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

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

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

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

sazi
sazi

2019/10/11 06:46

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

2019/10/11 06:48

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

2019/10/11 08:32

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

回答1件

0

ベストアンサー

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

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

追記

変更後

Explain
1Nested Loop Left Join  
2　(cost=2719.23..3363.87 rows=1 width=11) 
3　(actual time=1606.520..169663.178 rows=47 loops=1)  
4　Join Filter: 
5　　(((oh.purchase_code)::text = (a1.xxx)::text) AND ((gd.yyy)::text = (a1.yyy)::text))

変更前

Explain
1Hash Left Join 
2 (cost=3397.63..3401.18 rows=129 width=11) 
3 (actual time=3364.634..3364.695 rows=188 loops=1)  
4 Hash Cond: 
5  (((a1.xxx)::text = (oh.xxx)::text) AND ((a1.yyy)::text = (gd.yyy)::text))

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

投稿2019/10/11 06:51

編集2019/10/11 10:16
sazi
sazi

総合スコア25195

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

rezaq
rezaq

2019/10/11 12:33

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

2019/10/11 12:48

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

2019/10/11 15:23 編集

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

2019/10/12 16:47 編集

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

2019/10/12 04:20

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

質問の解決につながる回答をしましょう。 サンプルコードなど、より具体的な説明があると質問者の理解の助けになります。 また、読む側のことを考えた、分かりやすい文章を心がけましょう。

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

ただいまの回答率
85.48%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問

トップSQLに関する質問

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