第3回：PostgreSQL – SQLチューニング

技術者向け・データベースの技術情報発信

2017.09.01

はじめに

こんにちは、マルチDBソリューション部の川本です。今回の技術情報は、PostgreSQL9.5（Amazon RDS for PostgreSQL）で実施したSQLチューニングについてです。

対象は、AテーブルからBテーブルを引き算し、差分抽出を行うSQLです。

ベン図でいうと以下の通りです。

Aテーブルはバッチ処理によりCSVデータを元に毎朝作成されます。データ件数は約1400万件あり、3万件/日ずつ増加します。（INSERTのみ）

【インポートコマンド】

bunzip2 -c A.csv.bz2|psql ... -c "copy A (...) from stdin with csv header;"

Bテーブルは上記処理の直前にAテーブルをリネームして再作成されます。

【Bテーブル作成コマンド】

drop table B;
alter table A rename to B;

テーブル同士の引き算なので、まずはじめは「EXCEPT」で実行してみました。
まずは、A-Bが何件になるのか、PKとなっている列のみを指定し確認してみます。

【EXCEPT（PK列のみ）】

explain analyze select f_date, job_no from a except select f_date, job_no from b;
                                                                                         QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 SetOp Except  (cost=5726757.03..5943140.85 rows=1444107 width=12) (actual time=266861.353..266963.120 rows=31043 loops=1)
   ->  Sort  (cost=5726757.03..5798884.97 rows=28851176 width=12) (actual time=199330.965..235575.801 rows=28851575 loops=1)
         Sort Key: "*SELECT* 1".f_date, "*SELECT* 1".job_no
         Sort Method: external merge  Disk: 846080kB
         ->  Append  (cost=0.56..1165664.52 rows=28851176 width=12) (actual time=3.410..148250.519 rows=28851575 loops=1)
               ->  Subquery Scan on "*SELECT* 1"  (cost=0.56..583459.23 rows=14441067 width=12) (actual time=3.408..45819.531 rows=14441309
                     ->  Index Only Scan using pk_a on a  (cost=0.56..439048.57 rows=14441067 width=12) (actual time=3.404..
                           Heap Fetches: 0
               ->  Subquery Scan on "*SELECT* 2"  (cost=0.56..582205.29 rows=14410109 width=12) (actual time=0.080..45535.699 rows=14410266
                     ->  Index Only Scan using pk_b on b  (cost=0.56..438104.20 rows=14410109 width=12) (act
                           Heap Fetches: 0
 Planning time: 0.121 ms
 Execution time: 267124.140 ms
(13 行)

A-Bは31043行となり、3万件/日の情報ともあっているので問題なさそうです。
次に取得したいテーブル全列を指定してSQLを実行してみます。

【EXCEPT】

explain analyze select * from a except select * from b;
^CCancel request sent
ERROR:  canceling statement due to user request

全然帰ってこないので、いったん処理を中断しました。

EXCEPTについて、他の書き方が無いか調べてみると、LEFT OUTER JOINしてから、NULLのものだけ抽出する処理に置き換えられそうなので、SQLを書き換えて実行してみました。

【LEFT OUTER JOIN＋IS NULL】

explain analyze 
 select a.* from a left outer join b on a.f_date=b.f_date and a.job_no=b.job_no where b.job_no is null;
                                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Merge Anti Join  (cost=1.12..45711313.54 rows=294152 width=1821) (actual time=761117.664..763055.743 rows=31043 loops=1)
   Merge Cond: ((a.f_date = b.f_date) AND (a.job_no = b.job_no))
   ->  Index Scan using pk_a on a  (cost=0.56..45087508.35 rows=14441067 width=1821) (actual time=0.009..702237.768 rows=1
   ->  Index Only Scan using pk_b on b  (cost=0.56..438104.20 rows=14410109 width=12) (actual time=0.011..
         Heap Fetches: 0
 Planning time: 0.690 ms
 Execution time: 763086.863 ms ---> 約13分
(7 行)

結果は、31043行取得で、実行時間が約13分で帰ってくるようになりました。

上記実行計画の中で、pk_aを使用したIndex Scanのコストが、pk_bを使用したIndex Only Scanより10倍くらい大きいので、この部分を何とかできないかいろいろ試行錯誤してみると、以下のようにSQLを書き換えることで早くなりました。

【INNER JOIN＋LEFT OUTER JOIN】

explain analyze 
 select c.* from a c inner join (
 select a.f_date,a.job_no from a left outer join b on a.f_date=b.f_date and a.job_no=b.job_no where b.job_no is null) b 
 on c.f_date=b.f_date and c.job_no=b.job_no
;
                                                                                    QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=1.68..3342052.84 rows=99809 width=1821) (actual time=63039.197..65246.290 rows=31043 loops=1)
   ->  Merge Anti Join  (cost=1.12..1062853.75 rows=294152 width=12) (actual time=63038.419..63138.294 rows=31043 loops=1)
         Merge Cond: ((a.f_date = b.f_date) AND (a.job_no = b.job_no))
         ->  Index Only Scan using pk_a on a  (cost=0.56..439048.57 rows=14441067 width=12) (actual time=0.011..15599.310
               Heap Fetches: 0
         ->  Index Only Scan using pk_b on b  (cost=0.56..438104.20 rows=14410109 width=12) (actual time=0
               Heap Fetches: 0
   ->  Index Scan using pk_a on a c  (cost=0.56..7.74 rows=1 width=1821) (actual time=0.061..0.063 rows=1 loops=31043)
         Index Cond: ((f_date = a.f_date) AND (job_no = a.job_no))
 Planning time: 1.030 ms
 Execution time: 65277.855 ms ---> 1分5秒
(11 行)

結果は、31043行取得で、実行時間が約1分で帰ってくるようになりました。

SQLのチューニングポイントは以下となります。