12時間かかってたバッチ処理を3分にした話 #SQL

はじめに

この記事ではOFFSETによって取得に時間がかかっているクエリの改善方法を紹介します。

本題

前提

以下のようなクエリを実行し、商品を全件取得するまで繰り返すバッチ処理がありました。
商品は約2000万件あり、このバッチ処理の実行に12時間かかっていました。

SELECT
  商品.id
FROM
  商品
  JOIN 出品者 ON 出品者.id = 商品.出品者id
WHERE
  商品.表示するか
  AND 出品者.有効か
ORDER BY
  商品.id
OFFSET ?
LIMIT 10000

原因

なぜ遅いのか。
最も大きな原因にOFFSETを利用していることが挙げられます。
OFFSETは指定された件数までのレコードを取得した上で破棄します。
つまり最後のレコードを取得するときには、すべてのレコードを取得することになります。
そのため、OFFSETを利用すると重複して取得することとなり、遅くなります。
実行計画を確認するとOFFSETが0のときは、取得件数が少ないためNested Loopを用いますが、OFFSETが1000000のときは、Merge Joinを用います。

-- OFFSET 0の場合
 Limit  (cost=0.00..126917.40 rows=12000 width=34) (actual time=1.226..2083.035 rows=12000 loops=1)
   ->  Nested Loop  (cost=0.00..16199043.75 rows=2158264 width=45) (actual time=0.509..2042.357 rows=12000 loops=1)
         ->  Index Scan using 商品.pkey on 商品 (cost=0.00..1727215.12 rows=2530890 width=56) (actual time=0.366..1932.874 rows=12000 loops=1)
               Filter: (商品.表示するか)
         ->  Index Scan using 出品者.pkey on 出品者 (cost=0.00..3.08 rows=1 width=11) (actual time=0.004..0.005 rows=1 loops=12000)
               Index Cond: ("outer".出品者id = 出品者.id)
               Filter: (出品者.有効か)
 Total runtime: 2085.098 ms

-- OFFSET 1000000の場合
 Limit  (cost=2895973.53..2896003.53 rows=12000 width=34) (actual time=208973.814..208977.226 rows=12000 loops=1)
   ->  Sort  (cost=2893473.53..2898869.19 rows=2158264 width=34) (actual time=208764.225..208922.369 rows=1012000 loops=1)
         Sort Key: 商品.id
         ->  Merge Join  (cost=2491633.72..2590528.60 rows=2158264 width=34) (actual time=190246.472..193253.043 rows=2443095 loops=1)
               Merge Cond: ("outer".id = "inner".出品者id)
               ->  Index Scan using 出品者.pkey on 出品者 (cost=0.00..64474.22 rows=814267 width=11) (actual time=0.043..1468.153 rows=813538 loops=1)
                     Filter: (出品者.有効か)
               ->  Sort  (cost=2491633.72..2497080.76 rows=2178816 width=34) (actual time=190245.013..190771.003 rows=2443186 loops=1)
                     Sort Key: 商品.出品者id
 Total runtime: 209154.339 ms

(実際は他にも遅くなる原因として、不要なJOINがあったり、バッチ処理のファイルIOが多かったりなどがありましたが、今回はOFFSETについてのみ取り上げます。)

対応

以下のようにOFFSETを取り除き、ORDER BYで指定したカラムをWHEREで指定します。
繰り返し取得するときに1つ前に取得したレコード群から最後のレコードのidを指定することによって、レコードを取得することなく開始位置をスキップできます。

  SELECT
    商品.id
  FROM
    商品
    JOIN 出品者 ON 出品者.id = 商品.出品者id
  WHERE
    商品.表示するか
    AND 出品者.有効か
+   AND 商品.id > ?
  ORDER BY
    商品.id
- OFFSET ?
  LIMIT 10000

 Limit  (cost=0.00..126136.40 rows=10000 width=53) (actual time=0.271..214.575 rows=10000 loops=1)
   ->  Nested Loop  (cost=0.00..697988.40 rows=55336 width=53) (actual time=0.271..212.684 rows=10000 loops=1)
         ->  Index Scan using 商品.pkey on 商品 (cost=0.00..326942.58 rows=64890 width=64) (actual time=0.118..113.837 rows=10052 loops=1)
               Index Cond: (id >= 18000000::numeric)
               Filter: (商品.表示するか)
         ->  Index Scan using 出品者.pkey on 出品者 (cost=0.00..3.08 rows=1 width=11) (actual time=0.005..0.005 rows=1 loops=10000)
               Index Cond: ("outer".出品者id = 出品者.id)
               Filter: (出品者.有効か)
 Total runtime: 1221.397 ms

おわりに

これが実装された当時は処理速度を気にするほどレコード数があったわけではないのでしょう。
動作が遅い、何年も手を入れていないということであれば、当時とは状況が異なっているのかもしれません。
当時の状況を思い描きつつ改善していきましょう。