JdbcItemReaderでsortKeysを設定する際の注意点

March 6, 2023 · 6分の読み時間

Owner, Software Engineer at 42dot

PostgreSQLで大量のデータを取得する際に遭遇した問題について共有したいと思います。

問題

Spring BatchのJdbcPagingItemReaderを使用している際、以下のようにsortKeysを設定しました：

...
  .selectClause("SELECT *")
  .fromClause("FROM big_partitioned_table_" + yearMonth)
  .sortKeys(Map.of(
          "timestamp", Order.ASCENDING,
          "mmsi", Order.ASCENDING,
          "imo_no", Order.ASCENDING
      )
  )
...

現在のテーブルのインデックスはtimestamp、mmsi、imo_noの複合インデックスとして設定されているため、データ取得時にインデックススキャンが行われると期待していました。しかし、実際にはSeqスキャンが発生しました。対象のテーブルには約2億件のレコードが含まれており、バッチ処理が完了する兆しが見えなかったため、最終的にバッチを強制終了する必要がありました。なぜインデックス条件でクエリを実行しているのにSeqスキャンが発生したのでしょうか？🤔

PostgreSQLでは、以下のような場合にSeqスキャンが発生します：

テーブルのデータ量が少ないため、オプティマイザがSeqスキャンの方が速いと判断した場合
クエリ対象のデータ量が多すぎる（テーブルの10%以上）場合、オプティマイザがインデックススキャンよりもSeqスキャンの方が効率的だと判断した場合
- このような場合、limitを使用してデータ量を調整し、インデックススキャンを実行することができます

このケースでは、select *を使用していたため、大量のデータをクエリすることでSeqスキャンが発生する可能性がありました。しかし、chunk sizeのためにクエリはlimit付きで実行されていたため、インデックススキャンが連続して発生すると考えていました。

デバッグ

正確な原因を特定するために、実際に実行されているクエリを確認しましょう。YAML設定を少し変更することで、JdbcPagingItemReaderが実行するクエリを観察できます。

logging:
  level.org.springframework.jdbc.core.JdbcTemplate: DEBUG

バッチプロセスを再実行して、クエリを直接観察しました。

SELECT * FROM big_partitioned_table_202301 ORDER BY imo_no ASC, mmsi ASC, timestamp ASC LIMIT 1000

order by句の順序が奇妙に見えたので、再度実行しました。

SELECT * FROM big_partitioned_table_202301 ORDER BY timestamp ASC, mmsi ASC, imo_no ASC LIMIT 1000

実行ごとにorder by条件の順序が変わっていることが明らかでした。

インデックススキャンを確実に行うためには、ソート条件を正しい順序で指定する必要があります。一般的なMapをsortKeysに渡すと順序が保証されないため、SQLクエリが意図した通りに実行されません。

順序を維持するためには、LinkedHashMapを使用してsortKeysを作成することができます。

Map<String, Order> sortKeys = new LinkedHashMap<>();
sortKeys.put("timestamp", Order.ASCENDING);
sortKeys.put("mmsi", Order.ASCENDING);
sortKeys.put("imo_no", Order.ASCENDING);

この調整を行い、バッチを再実行したところ、ソート条件が正しい順序で指定されていることを確認できました。

SELECT * FROM big_partitioned_table_202301 ORDER BY timestamp ASC, mmsi ASC, imo_no ASC LIMIT 1000

結論

Seqスキャンがインデックススキャンの代わりに発生する問題は、アプリケーションのテストコードでは検証できないため、潜在的なバグに気づくことができませんでした。実際の運用環境でバッチ処理の大幅な遅延を観察して初めて、何かが間違っていることに気づきました。開発中には、Mapデータ構造によってソート条件の順序が変わる可能性を予想していませんでした。

幸いにも、大量のデータをクエリするためにインデックススキャンが発生しない場合、LIMITクエリでバッチ処理が大幅に遅くなるため、問題に気づきやすくなります。しかし、データ量が少なく、インデックススキャンとSeqスキャンの実行速度が似ている場合、問題に気づくまでに時間がかかるかもしれません。

この問題を事前に予測し対処する方法について、さらに検討が必要です。order by条件の順序が重要な場合が多いため、可能な限りHashMapではなくLinkedHashMapを使用することをお勧めします。

問題​

デバッグ​

結論​

問題

デバッグ

結論