ぱと隊長日誌

ブログ運用もエンジニアとしての生き方も模索中

PostgreSQL の TEMPORARY TABLE と UNLOGGED TABLE にパフォーマンスの差異はあるか？

データベース

はじめに

PostgreSQL にはテーブルの種類として TEMPORARY と UNLOGGED があります。この2つはワークテーブルとしてしばしば使われます。

一時テーブルまたはログに記録されないテーブルをデータのロードに使用する
非常に大量のデータをロードする場合、WAL に書き込まれないテーブルにデータを “ステージング” すると役立つ場合があります。これらはクラッシュに対して安全なテーブルではないため、一定のリスクはありますが、Extract-Transform-Load プロセスの一環として有用なツールになる可能性があります。

一時テーブルは CREATE TEMPORARY TABLE​ を使用して、ログに記録されないテーブルは CREATE UNLOGGED TABLE​ を使用して作成できます。

Postgres のログ先行書き込みの使用法 | Heroku Dev Center

TEMPORARY TABLE に注目して、その挙動を詳細に追った記事があります。
TEMPORARY TABLE（一時テーブル）を探る - Qiita

この記事によれば TEMPORARY と UNLOGGED は以下の特徴があります。

  • TEMPORARY と UNLOGGED ともにログ (WAL) に書き出さない。
  • TEMPORARY はテーブルの内容がローカルバッファからあふれるとディスクに書き出される。

別の資料（スライド）では UNLOGGED もテーブルの内容をディスクに書き出すとしています。

UNLOGGED TABLE

  • トランザクションログを書かないテーブル
    • CREATE UNLOGGED TABLE ...
    • テーブルのページブロックだけディスクに（非同期に）書き出される。
    • クラッシュした場合、リスタートのリカバリの際、TRUNCATEされる。
PostgreSQLアーキテクチャ入門（INSIGHT OUT 2011）

ここまでの情報を踏まえると、ディスクへの書き出しが起こる（テーブルの内容がバッファに収まらない）程度に大きいデータサイズの場合、パフォーマンスにはそこまで差が出ないようにも見えます。そこで、今回の記事では TEMPORARY と UNLOGGED のパフォーマンス面に注目して検証を行いました。

結論

シンプルな条件下では UNLOGGED より TEMPORARY のパフォーマンスが良い結果となりました。ただし、今回の調査では内部動作の違いを解明できなかったため、必ずしも TEMPORARY が有利とは言い切れませんでした。

以下に挙げるような基準で考えるのが良いかもしれません。

  • セッション若しくはトランザクション単位でテーブルを分離したいのであれば TEMPORARY、共有したいのであれば UNLOGGED を選択する。
  • セッション若しくはトランザクションの開始時点でテーブルを空にしたいなら TEMPORARY を選択する。
  • システム実装の要件が UNLOGGED / TEMPORARY のいずれでも満たせるのであれば、TEMPORARY を採用する。

検証環境

検証方法

テーブルの種類ごとに INSERT / SELECT / UPDATE / DELETE を行い、その処理時間を計測しました。組み合わせごとに5回測定し、その中央値を採用しました。

対象としたテーブルの種類は以下の通りです。

  • 通常
  • UNLOGGED
  • TEMPORARY

クエリの内容を以下に示します。

-- いずれかの CREATE TABLE を実行する
CREATE TABLE tab(c1 INTEGER);
CREATE UNLOGGED TABLE tab(c1 INTEGER);
CREATE TEMPORARY TABLE tab(c1 INTEGER);

INSERT INTO tab SELECT generate_series(1, 1e+8);

VACUUM ANALYZE;

SELECT COUNT(*) FROM tab;

UPDATE tab SET c1 = (c1 + 1);

VACUUM ANALYZE;

DELETE FROM tab;

DROP TABLE tab;

検証結果

[INSERT]

種類 処理時間[ms] NORMAL比[%]
NORMAL 79554.635 100.0
UNLOGGED 40842.508 51.3
TEMPORARY 35110.147 44.1

[SELECT]

種類 処理時間[ms] NORMAL比[%]
NORMAL 7814.027 100.0
UNLOGGED 7838.642 100.3
TEMPORARY 7260.491 92.9

[UPDATE]

種類 処理時間[ms] NORMAL比[%]
NORMAL 165388.554 100.0
UNLOGGED 64857.803 39.2
TEMPORARY 56086.556 33.9

[DELETE]

種類 処理時間[ms] NORMAL比[%]
NORMAL 85761.934 100.0
UNLOGGED 34065.439 39.7
TEMPORARY 30771.315 35.9

考察

パフォーマンス（処理時間）の観点で結果を確認します。

SELECT で NORMAL と比較して UNLOGGED はほぼ変わらない結果でしたが、TEMPORARY は若干良い結果となりました。
また、INSERT/UPDATE/DELETE の全てで NORMAL と比較して UNLOGGED, TEMPORARY 共に良い結果となっており、TEMPORARY は最も良い結果となりました。

今回の計測で UNLOGGED よりも TEMPORARY は常に良い結果となっていました。これがテーブルサイズの違いによるものではないかと考え、テーブルの種類ごとに INSERT 直後のページ数を確認しました。

-- いずれかの CREATE TABLE を実行する
CREATE TABLE tab(c1 INTEGER);
CREATE UNLOGGED TABLE tab(c1 INTEGER);
CREATE TEMPORARY TABLE tab(c1 INTEGER);

INSERT INTO tab SELECT generate_series(1, 1e+8);

VACUUM ANALYZE;

SELECT relpages FROM pg_class WHERE relname = 'tab';
種類 ページ数
NORMAL 442,478
UNLOGGED 442,478
TEMPORARY 442,478

テーブルの種類に関わらず同じサイズとなりました。ページサイズが 8KB なので、テーブルサイズは約 3.38GB となります。

一時バッファは、一時テーブルにアクセスする時にのみ使用されるセッションローカルのバッファです。

19.4. 資源の消費

一時バッファ (temp_buffers) はデフォルトの 8MB なので、テーブルサイズはこれより十分に大きく、これが良い結果と関係しているとは考えにくいです。

ソースコードから UNLOGGED と TEMPORARY の差分を見つけようとしましたが、手掛かりを見つけることができませんでした。

UNLOGGED と TEMPORARY のパフォーマンスに差が出たということは、内部動作が異なると推測できます。ただし、今回の調査ではその内部動作の違いを完全に解明できておらず、TEMPORARY が常に優位とは言い切れませんでした。

追加情報

記事を公開後、お寄せいただいた情報を紹介いたします。

id:interdb さんからはコメントで情報をお寄せいただきました。記事下部のコメント欄を参照ください。主に以下の観点で情報を提示いただきました。

@tatsuo_ishii さんからは Twitter で情報を提供いただきました。

tweet 内のリンク先の議論（Tom Lane さん）から引用します。

Temp tables *are* unlogged. They also live in session-private buffers, which eliminates a great deal of synchronization overhead; at the cost that any writing that does happen has to be done by the backend process itself, without help from the background writer. It's possible that there's something about your specific test case that makes that scenario look bad. Another likely source of bogus results is if you were testing a tiny temp_buffers setting versus a more appropriately sized shared_buffers setting.

PostgreSQL: Re: UNLOGGED TEMPORARY tables?

ここでのポイントを以下に挙げます。

  • TEMPORARY テーブルは排他制御のオーバーヘッドが少ないこと。
  • バッファサイズが性能の測定結果に影響を与えた可能性。

id:interdb さんがコメントで指摘されているように、『存在意義の違いを無視したとしても、性能的にTEMPORARYテーブルが有利かどうかは、実際のワークロードに深く依存すると思います』という結論になるかもしれません。

PostgreSQL の実行計画で Hash のコストは Hash Join に含まれる

データベース

PostgreSQL の実行計画で Hash ノードのコストは 0 と計算されています。

PostgreSQL 13.3 での実行計画例を示します。

testdb=# CREATE TABLE tab1(c1 integer);
testdb=# CREATE TABLE tab2(c1 integer);

testdb=# INSERT INTO tab1(c1) SELECT generate_series(1, 1000000);
testdb=# INSERT INTO tab2(c1) SELECT generate_series(1, 1000000);

testdb=# VACUUM ANALYZE;

testdb=# EXPLAIN ANALYZE SELECT * FROM tab1, tab2 WHERE tab1.c1 = tab2.c1;
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=30832.00..70728.00 rows=1000000 width=8) (actual time=179.725..660.696 rows=1000000 loops=1)
   Hash Cond: (tab1.c1 = tab2.c1)
   ->  Seq Scan on tab1  (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.006..53.789 rows=1000000 loops=1)
   ->  Hash  (cost=14425.00..14425.00 rows=1000000 width=4) (actual time=179.167..179.167 rows=1000000 loops=1)
         Buckets: 131072  Batches: 16  Memory Usage: 3227kB
         ->  Seq Scan on tab2  (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.007..55.604 rows=1000000 loops=1)
 Planning Time: 0.192 ms
 Execution Time: 687.420 ms

Hash の初期推定コスト及び全体推定コストが子ノードの Seq Scan の全体推定コストと等しくなっています。上位ノードのコストは全ての子ノードのコストを含めることを踏まえると、Hash ノードのコストは加算されていないことが分かります。

Hash の処理にも時間がかかっていることは Seq Scan と Hash の actual time を比較して増えていることからも明らかです。このコストを実行計画に組み込む必要は無いのでしょうか？

実はハッシュ処理のコストは Hash Join のコストとして組み込まれています。

PostgreSQL 13.3 のソースコードから該当部分を抜粋します。

src/backend/optimizer/path/costsize.c
initial_cost_hashjoin(PlannerInfo *root, JoinCostWorkspace *workspace, JoinType jointype, List *hashclauses, Path *outer_path, Path *inner_path, JoinPathExtraData *extra, bool parallel_hash)

/*
 * Cost of computing hash function: must do it once per input tuple. We
 * charge one cpu_operator_cost for each column's hash function.  Also,
 * tack on one cpu_tuple_cost per inner row, to model the costs of
 * inserting the row into the hashtable.
 *
 * XXX when a hashclause is more complex than a single operator, we really
 * should charge the extra eval costs of the left or right side, as
 * appropriate, here.  This seems more work than it's worth at the moment.
 */
startup_cost += (cpu_operator_cost * num_hashclauses + cpu_tuple_cost)
	* inner_path_rows;
run_cost += cpu_operator_cost * num_hashclauses * outer_path_rows;

Hash のコストは Hash Join に含まれており、全体としてはハッシュ処理のコストも踏まえた実行計画になっていることが分かりました。

PostgreSQL のインデックス肥大化と実行計画のコストへの影響

データベース

お知らせ

本記事をベースに新しい記事を公開しました。
PostgreSQL インデックス肥大化とインデックスコストへの影響（再モデル化） - ぱと隊長日誌

新しい記事ではインデックスコストモデルの正確性を向上させました。
新しい記事を参照いただけますと幸いです。

概要

PostgreSQL のインデックスサイズは一度大きくなると、その後小さくなるタイミングが限られています。

「［改訂新版］内部構造から学ぶPostgreSQL-設計・運用計画の鉄則」でインデックスファイルサイズが小さくなるのは以下のタイミングとしています。

  • DROP INDEX でインデックス自体を削除した場合
  • TRUNCATE TABLE でテーブル全体を空にした場合
  • REINDEX でインデックスを再構成した場合

[改訂新版]内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 (Software Design plus)

[改訂新版]内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 (Software Design plus)

Amazon

テーブルのデータの追加・更新・削除を繰り返しているうちに、インデックスファイルサイズが必要以上に大きくなった状態を、本記事では「インデックスの肥大化」としました。

インデックスの肥大化した状況では実行計画のコスト計算に影響することがあります。これは適切な実行計画を選択する妨げとなるかもしれません。

本記事ではインデックスの肥大化が実行計画のコスト計算に影響を与えた例を示します。

検証環境

なお、PostgreSQL の日本語版マニュアルの最新版は現時点で 12 を対象としているため、記事の中ではこれを参照しています。

調査方法・結果

以下の状況を作ります。
(1) テーブルに行を 10^5 追加する。
(2) テーブルの行を 10^5 ⇒ 10^6 まで増やす。
(3) テーブルの行を 10^6 ⇒ 10^5 まで減らす（※インデックス肥大化の発生）。
(4) テーブルを REINDEX する（※インデックス肥大化の解消）。

各状況において、以下の項目を確認します。

  • インデックスの行数
  • インデックスのページ数
  • SELECT で 5000 行を Index Only Scan した時のコスト
testdb=# CREATE TABLE tab1(c1 INTEGER PRIMARY KEY);
CREATE TABLE

testdb=# \d tab1;
                Table "public.tab1"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 c1     | integer |           | not null |
Indexes:
    "tab1_pkey" PRIMARY KEY, btree (c1)

(1) テーブルに行を 10^5 追加する。

testdb=# INSERT INTO tab1 SELECT generate_series(1, 1e+5);
INSERT 0 100000

testdb=# VACUUM ANALYZE;
VACUUM

testdb=# SELECT reltuples, relpages FROM pg_class WHERE relname = 'tab1_pkey';
 reltuples | relpages
-----------+----------
    100000 |      276
(1 row)

testdb=# EXPLAIN SELECT * FROM tab1 WHERE c1 <= 5000;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Index Only Scan using tab1_pkey on tab1  (cost=0.29..143.27 rows=4970 width=4)
   Index Cond: (c1 <= 5000)
(2 rows)

(2) テーブルの行を 10^5 ⇒ 10^6 まで増やす。

testdb=# INSERT INTO tab1 SELECT generate_series((1e+5)+1, 1e+6);
INSERT 0 900000

testdb=# VACUUM ANALYZE;
VACUUM

testdb=# SELECT reltuples, relpages FROM pg_class WHERE relname = 'tab1_pkey';
 reltuples | relpages
-----------+----------
     1e+06 |     2745
(1 row)

testdb=# EXPLAIN SELECT * FROM tab1 WHERE c1 <= 5000;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Index Only Scan using tab1_pkey on tab1  (cost=0.42..139.92 rows=4771 width=4)
   Index Cond: (c1 <= 5000)
(2 rows)

インデックスの行数が10倍になると、インデックスのページ数もほぼ10倍になりました。ただ、Index Only Scan のコストはほとんど変わりません。Index Only Scan が必要な範囲のインデックスのページだけ読み込むと想定すれば、これは妥当な結果といえそうです。

次にインデックスの行数を1/10にして、Index Only Scan へのコスト影響を確認します。

(3) テーブルの行を 10^6 ⇒ 10^5 まで減らす（※インデックス肥大化の発生）。

testdb=# DELETE FROM tab1 WHERE c1 > 1e+5;
DELETE 900000

testdb=# VACUUM ANALYZE;
VACUUM

testdb=# SELECT reltuples, relpages FROM pg_class WHERE relname = 'tab1_pkey';
 reltuples | relpages
-----------+----------
    100000 |     2745
(1 row)

testdb=# EXPLAIN SELECT * FROM tab1 WHERE c1 <= 5000;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Index Only Scan using tab1_pkey on tab1  (cost=0.42..621.35 rows=4853 width=4)
   Index Cond: (c1 <= 5000)
(2 rows)

インデックスの行数は1/10に変わりましたが、インデックスのページ数は変わりません。そして全体推定コストは4.4倍に増えました。

(4) テーブルを REINDEX する（※インデックス肥大化の解消）。

testdb=# REINDEX TABLE tab1;
REINDEX

testdb=# SELECT reltuples, relpages FROM pg_class WHERE relname = 'tab1_pkey';
 reltuples | relpages
-----------+----------
    100000 |      276
(1 row)

testdb=# EXPLAIN SELECT * FROM tab1 WHERE c1 <= 5000;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Index Only Scan using tab1_pkey on tab1  (cost=0.29..141.22 rows=4853 width=4)
   Index Cond: (c1 <= 5000)
(2 rows)

REINDEX でインデックスを再生成することで、インデックスのページ数は縮小しました。
全体推定コストも当初とほぼ変わらない数字になりました。

全体推定コストのモデル化

インデックスが肥大化した時の全体推定コストのモデル化にチャレンジしました。結論として今回はうまく導けなかったのですが、今後の再挑戦に向けて記録を残しておきます。

インデックスのコスト計算は下記のソースコード・関数で実装されていると目星を付けました。
src/backend/optimizer/path/costsize.c
cost_index(IndexPath *path, PlannerInfo *root, double loop_count, bool partial_path)

この関数の実装に amcostestimate 関数を呼び出す箇所があり、この実装を探し当てることはできませんでしたが（C言語の知識が無さ過ぎて辛い…）、PostgreSQL のマニュアルに「インデックスコスト推定関数」として説明されているページを見つけました。
61.6. インデックスコスト推定関数

この説明ページには「典型的なコスト概算」の例が記載されていました。インデックスのコスト計算がこれに従うと仮定し、インデックス肥大化の影響をモデル化できないか試みました。

1. 与えられた制約条件に基づいて訪れられるメインテーブルの行の割合を概算して返します。

「対象の行数 / 全体の行数」が indexSelectivity になると仮定します。
対象の行数は 5000 です。

全体の行数が 10^5：
5*10^3 / 10^5 = 0.05

全体の行数が 10^6：
5*10^3 / 10^6 = 0.005

2. スキャン中に訪れられるインデックスの行数を概算します。多くのインデックス種類では、これは indexSelectivity とインデックスの中にある行数を掛けたものと等しいですが、それより多い場合もあります。

全てのケースで 5000 とします。

3. スキャン中に抽出されるインデックスページ数を概算します。これは単に indexSelectivity にページ内のインデックスのサイズを掛けたものになるでしょう。

「ページ内のインデックスのサイズ」は pg_class の relpages に等しいと仮定します。

テーブルに行を 10^5 追加した後：
0.05 * 276 = 13.8

テーブルの行を 10^5 ⇒ 10^6 まで増やした後：
0.005 * 2745 = 13.725

テーブルの行を 10^6 ⇒ 10^5 まで減らした後：
0.05 * 2745 = 137.25

テーブルを REINDEX した後：
0.05 * 276 = 13.8

4. インデックスアクセスコストを計算します。 汎用的な概算においては以下のように行うでしょう。
＜中略＞
*indexTotalCost = seq_page_cost * numIndexPages + (cpu_index_tuple_cost + index_qual_cost.per_tuple) * numIndexTuples;

seq_page_cost = 1.0
cpu_index_tuple_cost = 0.005
index_qual_cost.per_tuple = 不明だったので cpu_operator_cost の 0.0025 と仮定します。
numIndexPages = 「スキャン中に抽出されるインデックスページ数」に等しいと仮定します。
numIndexTuples = 「スキャン中に訪れられるインデックスの行数」に等しいと仮定します。

テーブルに行を 10^5 追加した後：
1.0 * 13.8 + (0.005 + 0.0025) * 5000
=51.3

テーブルの行を 10^5 ⇒ 10^6 まで増やした後：
1.0 * 13.725 + (0.005 + 0.0025) * 5000
=51.225

テーブルの行を 10^6 ⇒ 10^5 まで減らした後：
1.0 * 137.25 + (0.005 + 0.0025) * 5000
=174.75

テーブルを REINDEX した後：
1.0 * 13.8 + (0.005 + 0.0025) * 5000
=51.3

調査で出力された全体推定コストの値とはかけ離れていますが、インデックスの肥大化でコストが大きく計算される傾向は同じでした。

モデルの妥当性の検証は不十分ですが、今後の再調査に向けた布石とはなりそうです。

まとめ

インデックスの肥大化は実行計画のコスト計算に影響を与えることがあるとわかりました。

ただ、インデックスの肥大化がコスト計算にどれほど影響を及ぼすのかについて示すことはできなかったため、今後の課題といたします。