【PostgreSQL】PostgreSQLのチューニング(SQLその1~基礎編~)

皆さん、こんにちは。がーしらです。

PostgreSQLを使ってシステム運用してますが、データ量やアクセスが増えると速度の懸念が出てきますよね。そんな時に見直す検討にあげる観点を簡単にまとめました。今回はSQLその1~基礎編~です。DB様の機嫌を伺いつつこちらの意図通りに結果を取得するコツを書いてます。ご参考にどうぞ。

1. seq_scanは避ける

データ量が増えると通常のselectでも速度が低下してきます。そんな時に活躍するのがインデックスです。インデックスの説明は割愛しますが、簡単に言うと「取得する条件で既に並びかわっているデータの位置を保存した入れ物」といったところでしょうか。良い本屋では当たり前の光景です。
インデックスが取得対象のSQLとマッチしていなければデータ量が増えたときにデータを1件ずつ条件に一致するか精査し、その後並び替えて結果を返すという動きをします。このような動きを実行計画で見ると「seq_scan」という方法でとっているように見えます。漫画喫茶や大雑把な友達の部屋とかに行くと漫画の巻数バラバラで次の巻を取る時に探しませんか?要はあれです。並び替えも、同じように整ってた方が並び替える必要が無かったりで取り出しやすいですよね。
データ量が多くないテーブルに対してあえてseq_scanをDB様が選択することもありますが、データ量が多い場合は速度低下の一因になりますので、EXPLAIN ANALYZE SELECT~と続けて実行計画を確認してみてください。
実行計画に「seq_scan」が含まれる場合はSQLの条件や並び順とインデックスがマッチしているか一度見直してみると幸せになれるかもしれません。

2. 最初に取得するテーブル(駆動表)の件数が少なくなるように

結合を使うSQLでは駆動表と内部表という考え方があります。こちらについても詳細は割愛しますが、簡単に言うと最初に条件式で絞り込む表を駆動表、そこに結合してくっつける表を内部表といいます。
SQLを作成するにあたって、意識しておいてほしいのが、上記の駆動表の部分です。DB様は人間と同じようにまずは駆動表から作成していきます。その時、駆動表の件数が少ないと結合するデータ量も少ないので機嫌よくサクサクと返却してくれます。それが、駆動表では全然絞り込まず内部表と結合したうえでデータ数を絞り込む場合、DB様には件数が多く見えて不機嫌になってレスポンスが悪くなります。まずは対象件数が最小限となるような駆動表となるように条件を考えましょう。

3. 内部表を並び順に指定しない

上記駆動表と内部表の考え方でもう一つ大事な視点です。表題のように、内部表で並び替えをするとLIMIT等を指定していても一度全件のデータを整えて並び替えをしたうえで、LIMITをかけるので速度が低下します。ちょっとうまく説明できないので、上記駆動表と内部表の2つについて非常に参考になる記事が以下にあります。以下を見ていただければ私の説明よりも数倍分かりやすいかと思います。けっして疲れたからではありません。

簡単にですが、PostgreSQLのSQLで見直す観点を書いてみました。どなたかの参考になって快適なPostgreライフを過ごしていただければと思います。次はSQLその2~実行計画・統計情報編~でお会いしましょう。

コメントを残す

%d人のブロガーが「いいね」をつけました。