【PostgreSQL】クエリのパフォーマンス改善の記録と「UNNEST」構文の使い方

はじめに

こんにちは、@rs_tukkiです。
この記事は、ラクス Advent Calendar 2024の25日目の記事です。

今回は、開発中に見つけた重いクエリを改善するための記録と、改善のために使用した見慣れない構文の紹介をしようと思います。

開発中の出来事

ある日、私はWebアプリに新しいAPIを追加するための実装をしていました。
既存のテーブルからデータを取得するための新しいクエリを作成して、そのクエリを呼び出した結果を返すだけの単純なAPIです。

実装はつつがなく完了し、その後のテストでも特に問題は見られませんでした。
ただ、新規にクエリを作ったため、最後にJMeterを使ったパフォーマンス検証を行うことに。

【図解】はじめてでもわかるJMeterの使い方 - RAKUS Developers Blog | ラクス エンジニアブログ

DBにデータを大量に入れて、顧客の利用状況を元にアクセス数の閾値を検討し、
その数だけリクエストを投げる操作を3回繰り返し、それぞれの平均値を取ります。

そして結果がこちら。

	平均値
1回目	1,881ms
2回目	1,886ms
3回目	1,929ms

1,900ms = 1.9秒。
...流石にちょっと時間がかかりすぎですね。

パフォーマンスチューニング

負荷検証は通常の運用で想定した分をはるかに超えたデータ量と呼び出し回数で行ってはいましたが、
とはいえ一度のAPIの実行で2秒弱もかかることがあるのではとてもリリースできません。

そのため、ボトルネックとなっているSQLを特定した上でそのチューニングを行うことにします。
実装したコードから呼び出しているクエリを1つずつコメントアウトしてはパフォーマンス検証、という操作を繰り返し、極端に処理が軽くなったタイミングがあれば、その時にコメントアウトしているクエリがボトルネックということになります。

そして、それを何度も繰り返している内に、とうとう原因となる一つのクエリに辿り着きました。 それがこちら。

SELECT count(1) AS count
FROM (
    SELECT a.column1
    FROM tableA a
    WHERE EXISTS (
            SELECT 1
            FROM tableB B
            WHERE a.column1 = b.column1
              AND a.column2 = b.column3 - 1
              AND b.column4 = 0
              AND b.column5 = 0
              AND b.column6 IN (
                    SELECT column7
                    FROM tableC
                    WHERE column8 = 'AAA'
                       OR column9 = 'AAA'
                       OR column10 = 'AAA'
                )
        )
      AND a.column11 = 0
      AND a.column12 >= 100
    LIMIT 100
) AS "result";

このクエリの実行が極端に重いせいで、リクエスト全体が遅延していたというわけです。
このクエリ……何か変……

見てもらえれば分かるとおり、このクエリでは欲しい情報を一度に取ろうとするあまり、
サブクエリにサブクエリを重ねて極端にパフォーマンスが悪いクエリになってしまっています。

そのため、サブクエリの中の最も深い部分、tableCからcolumn7を抽出している部分を先に実行しておき、
後から条件に加えるようにしました。

SELECT column7 FROM tableC WHERE column8 = 'AAA' OR column9 = 'AAA' OR column10 = 'AAA'; -- 「test1」と「test2」が抽出される
---
SELECT count(1) AS count
FROM (
    SELECT a.column1
    FROM tableA a
    WHERE EXISTS (
            SELECT 1
            FROM tableB B
            WHERE a.column1 = b.column1
              AND a.column2 = b.column3 - 1
              AND b.column4 = 0
              AND b.column5 = 0
              AND EXISTS (SELECT column7 FROM UNNEST(ARRAY['test1','test2']) AS C(column7) WHERE b.column6 = C.column7)
        )
      AND a.column11 = 0
      AND a.column12 >= 100
    LIMIT 100
) AS "result";

UNNESTとは？

さて、改善後のクエリにUNNESTという見慣れない構文があります。
これはPostgreSQLで使用できる構文で、配列を引数として渡すと、その各要素を単一列のテーブルとして出力する、という構文になります。

www.postgresql.jp

つまり、SELECT column7 FROM UNNEST(ARRAY['test1','test2']) AS C(column7)を実行すると、

column7
test1
test2

という列を含むCテーブルが結果として得られます。
この結果はそのままEXISTSに引き渡すことが出来るため、事前に抽出しておいた複数件の値を1つずつIN句に入れるよりも若干パフォーマンスが改善するというわけです。

チューニング結果

さて、改善後のクエリを、改善前と同条件のもとで負荷検証にかけてみたところ...

	平均値_改善前	平均値_改善後
1回目	1,881ms	507ms
2回目	1,886ms	518ms
3回目	1,929ms	516ms

APIリクエストの実行時間が1/3にまで改善されました！
クエリを分割するだけで速度が3倍近くにもなるので、やはりチューニングは大事だと気付かされた一件でした...

まとめ

今回は、開発時に発見したボトルネックとそれを改修するまでの記録を解説しました。
テストのタイミングでは問題なく見えても、大量のデータを投入し大量のリクエストを投げるとパフォーマンスが落ちることは往々にしてありますので、しっかり検証は行うようにしましょう。

ここまで読んでいただき、ありがとうございました！