LoginSignup
2
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@jri-matsuda(松田 卓久)

大規模システムにおけるPostgreSQLの実行計画の確認と型変換ではまりやすい点

Last updated at Posted at 2022-09-21

はじめに

大規模システムでPostgreSQLを使用するにあたって、気になるところの一つが性能かと思います。

PostgreSQLでも、Oracle DBやSQL Serverと同様にSQLの実行計画(Explain Plan)があります。
この実行計画をコントロールすることによって、ディスクからの読込量を抑え、より短い時間で結果を取得することができます。

1. PostgreSQLの実行計画

(1) 実行計画とは

実行計画とは、SQL（主にSELECTによるクエリ）を実行するにあたって
データへどのような経路・順番でアクセスするかを事前に検討したもののことです。

例えば、マッチングアプリを使っているときに、その人にはマストの条件があって、

  • 25〜40歳の男性
  • 趣味がF1観戦の人

という2点に当てはまる人を探すとしたときに、どっちが短い時間で洗い出せるでしょうか？

おそらく、マッチングアプリに登録される方の年齢層は25〜40歳がボリュームゾーンで、8割以上居るかも知れません。ただ、趣味が多様化している現代において、F1観戦が趣味な方で、毎週テレビで見ている人は少ないかもしれません。

となると、まずF1観戦が趣味な人を探して、その後、その人が何歳か？を調べる形の方が効率良いのではないでしょうか？

なるべく絞り込める条件を使って絞り込んでいく、というのがDB検索時間を短縮するアプローチです。

実行計画を作る中でキーになる、データを最初に絞り込む際に重要なオブジェクトが、索引です。本や辞書に書かれている「索引」と同じ意味です。

本の場合は、出ている用語が五十音順やアルファベット順で並んでいるかと思います。テーブルの索引も同様で、特定列の中身を五十音順1に並べているものになります。

本稿は性能改善がメインではなくて、実行計画の取得方法・見方などが主題ですので、索引の正しい張り方については別途とさせていただければと思いますが、索引こそがRDBMSにおいて性能を上げる一番の重要な要素です。

例えば、索引を検索項目ということで、あまり絞り込まれない項目（先ほどの例だと、例えば、性別とか）に付けてしまっても、性能向上効果が見込めません。

(2) 取得方法（SQL毎)

EXPLAINコマンドにより実行計画を取得することが可能です。
また、オプションにANALYZEやBUFFERSを指定することで、実行計画に加えて、実際の実行時間やループ件数などを出力可能です。

EXPLAIN ANALYZE 【SQL文】;

(3) 取得方法(サーバ全体)

アプリ実行時の実行計画を確認する際、Oracle等では、v$sqlplanなどの動的ビューがありますが、PostgreSQLでも似たようなことが可能です。

①サーバログへの出力

auto_explainというモジュールを読み込むことで、閾値を超えて時間がかかったSQLの実行計画情報をサーバログ(postgresql.log)へ出力できます。

DB全体で実行するときには、下記設定をすることで、特定閾値（●●秒）以上かかったSQLの場合に、その実行計画が出力されます。

設定方法は下記の通りです（設定後、インスタンスの再起動が必要になります）

①postgresql.confのshared_preload_librariesにauto_explainを含めて設定

shared_preload_libraries = 'auto_explain'

②postgresql.confで閾値を設定

auto_explain.log_min_duration = '●●s'

②PostgreSQL内部テーブルへの格納

pg_store_plansというモジュールを組み込むことで閾値を超えて時間がかかったSQLがDBに格納されます。
加えて、pg_stat_statmentで出力できるレポートに、SQL＋実行計画がセットで表示されるようになります。

導入方法は下記の通りです

①postgresql.confのshared_preload_librariesにauto_explainを含めて設定

shared_preload_libraries = 'auto_explain'

②postgresql.confで閾値を設定

auto_explain.log_min_duration = '●●s'

③psql等で対象DBにログインして、下記SQLを実行

CREATE EXTENTION pg_store_plans;

(4) スキャン方法

PostgreSQLでの主なスキャン方法は下記の通りです。2

演算子 概要
Seq Scan テーブルのフルスキャン
Parallel Seq Scan 並列化してフルスキャン
Index Scan B-tree索引を利用したスキャン
Bitmap Index Scan Bitmap索引によるスキャン
Function Scan 関数を適用した結果データによるスキャン
Nested Loop 一方のテーブルを駆動表として、駆動表からの抽出結果に対して１行ずつループを実行する結合方法
Merge Join 双方のテーブルを結合キーでソートして結合する方法
Hash Join 結合キーのハッシュを事前に作成しメモリ上に展開して、もう一方のテーブルを１行ずつハッシュを計算して突合する方法
Sort ORDER BY句利用時のソート操作
Unique 重複を排除する操作

2. ヒント句

Oracle DBにはヒント句という考え方があり、演算子を強制することで実行計画をある程度コントロールできる仕組みがあります。

PostgreSQLでもpg_hint_planというモジュールがあり、ほぼ同じ記法で使用することができます。

(1) 環境設定方法

ヒント句を使用する場合には、pg_store_plans利用時と同様に、外部モジュールを読み込む必要があります。

①導入したいDBに対してpsql等でログインし、下記SQLを実行

CREATE EXTENTION pg_hint_plan;

②PostgreSQLの設定ファイルであるpostgresql.confのshared_preload_librariesパラメータで指定して、プレロードする。
（修正後、PostgreSQLプロセスの再起動が必要）

shared_preload_librarires = 'pg_hint_plan'

(2) SQLでの書き方

SELECT、UPDATE等の直後に/*+ 【ヒント句】 */を書くことで、実行計画をある程度コントロールできます。
例えば、

SELECT /*+ IndexScan(test_tbl test_idx) */ FROM test_tbl WHERE test_id = '1'

と書くことで、索引test_idxを強制的に使ってスキャンするように指示することができます。

注意点として、このヒント句が効いているのか？の確認ですが、ヒント句が効かないときにエラーが出るわけではないので、EXPLAIN FOR を使ってSQLを実際に実行してみて、実行計画を確認する必要があります。

3. JDBC経由での実行計画

psqlでの処理時間と、Javaアプリでの処理時間に乖離がある場合は、Java経由でのアクセス時に項目の「型」が違う場合があります。
MyBatisを使ってテストしてみました。

テーブル定義 : bigint_test テーブル

カラム名 型 主キー
id bigint ●
content_varchar character varying(50)
content_numeric numeric(15)
content_timstamp timestamp without time zone
content_date date

検証プログラム

BigIntTestMapper.java
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.List;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface BigIntTestMapper {

	@Select("EXPLAIN ANALYZE SELECT * FROM bigint_test WHERE id = #{id}")
	public List<String> getExplainPlanByBigInteger(BigInteger id);

	@Select("EXPLAIN ANALYZE SELECT * FROM bigint_test WHERE id = #{id}")
	public List<String> getExplainPlanByLong(Long id);

	@Select("EXPLAIN ANALYZE SELECT * FROM bigint_test WHERE id = #{id}")
	public List<String> getExplainPlanByInteger(Integer id);
}
BigIntTypeTester.java
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.stereotype.Component;

@Component
public class BigIntTypeTester implements CommandLineRunner {
	
	@Autowired
	private BigIntTestMapper _mapper;


	public void testBigIntTypeExplainPlanByBigInteger() {
		System.out.println("*** Big Integer  EXPLAIN ***");

		BigInteger id = new BigInteger("1");
		List<String> explainPlanList = this._mapper.getExplainPlanByBigInteger(id);
		for(String tmp : explainPlanList) {
			System.out.println(tmp);
		}
		System.out.println();
    }

	public void testBigIntTypeExplainPlanByLong() {
		System.out.println("*** Long  EXPLAIN ***");
		Long id = 1L;
		List<String> explainPlanList = this._mapper.getExplainPlanByLong(id);
		for(String tmp : explainPlanList) {
			System.out.println(tmp);
		}
		System.out.println();

	}
	
	public void testBigIntTypeExplainPlanByInteger() {
		System.out.println("*** Integer EXPLAIN ***");
		Integer id = 1;
		List<String> explainPlanList = this._mapper.getExplainPlanByInteger(id);
		for(String tmp : explainPlanList) {
			System.out.println(tmp);
		}
		System.out.println();
	}

	@Override
	public void run(String... args) throws Exception {
		this.testBigIntTypeExplainPlanByBigInteger();
		this.testBigIntTypeExplainPlanByLong();
		this.testBigIntTypeExplainPlanByInteger();
	}
	
}

主キー検索

SELECT * FROM bigint_test WHERE id = ?

に対して、EXPLAIN ANALYZEコマンドを実行して実行計画を取得しています。
結果は下記のようになりました。

java型 PostgreSQLでの型解釈 選択された実行計画
Long bigint Index Scan
BigInteger numeric Parallel Seq Scan
Integer int Index Scan

PostgreSQLのbigint型に対して、JavaのLong型（同じ範囲をカバー）やInteger型でアクセスすると索引が使用されますが、JavaのBigInteger型でアクセスすると、PostgreSQL内部ではnumeric型の変数と扱われ、索引が使用できませんでした。3

恐らく、他のRDBMSでも同様の事象が起きるので、アプリを通した時の挙動は注意が必要です。

【ご参考】debugログ

*** Big Integer  EXPLAIN ***
[           main] j.c.j.t.m.B.getExplainPlanByBigInteger   : ==>  Preparing: EXPLAIN ANALYZE SELECT * FROM bigint_test WHERE id = ?
[           main] j.c.j.t.m.B.getExplainPlanByBigInteger   : ==> Parameters: 1(BigDecimal)
[           main] j.c.j.t.m.B.getExplainPlanByBigInteger   : <==      Total: 8
Gather  (cost=1000.00..17954.99 rows=5000 width=44) (actual time=0.448..85.320 rows=1 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on bigint_test  (cost=0.00..16454.99 rows=2083 width=44) (actual time=48.090..74.856 rows=0 loops=3)
        Filter: ((id)::numeric = '1'::numeric)
        Rows Removed by Filter: 333333
Planning Time: 1.481 ms
Execution Time: 85.390 ms

*** Long  EXPLAIN ***
[           main] j.c.j.t.m.B.getExplainPlanByLong         : ==>  Preparing: EXPLAIN ANALYZE SELECT * FROM bigint_test WHERE id = ?
[           main] j.c.j.t.m.B.getExplainPlanByLong         : ==> Parameters: 1(Long)
[           main] j.c.j.t.m.B.getExplainPlanByLong         : <==      Total: 4
Index Scan using bigint_test_pkey on bigint_test  (cost=0.42..8.44 rows=1 width=44) (actual time=0.034..0.035 rows=1 loops=1)
  Index Cond: (id = '1'::bigint)
Planning Time: 0.126 ms
Execution Time: 0.105 ms

*** Integer EXPLAIN ***
[           main] j.c.j.t.m.B.getExplainPlanByInteger      : ==>  Preparing: EXPLAIN ANALYZE SELECT * FROM bigint_test WHERE id = ?
[           main] j.c.j.t.m.B.getExplainPlanByInteger      : ==> Parameters: 1(Integer)
[           main] j.c.j.t.m.B.getExplainPlanByInteger      : <==      Total: 4
Index Scan using bigint_test_pkey on bigint_test  (cost=0.42..8.44 rows=1 width=44) (actual time=0.067..0.068 rows=1 loops=1)
  Index Cond: (id = 1)
Planning Time: 0.179 ms
Execution Time: 0.084 ms

おわりに

本稿では、PostgreSQLにおけるSQLチューニングについてご紹介しました。

Oracle DB等の商用製品のような機能を、PostgreSQLでもオプションにはなりますが利用可能です。

筆者も20年近く前はOracle DBAを使用していましたが、PostgreSQLになってもチューニングする際に同じアプローチで分析・改善できることを実感しました。性能要件があるシステムのテスト・運用時のヒントになれば幸甚です。

  1. 逆順（降順）や関数を使った結果の順序で索引を作ることも可能です。 ↩

  2. https://lets.postgresql.jp/sites/default/files/2016-11/Explaining_Explain_ja.pdf ↩

  3. BigIntegerでアクセスした場合、BigDecimalに変換されてDBサーバに送られていますが、こちらはMyBatisの仕様です（org.apache.ibatis.type.BigIntegerTypeHandlerによる制御） ↩

2
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?