導入
みなさん、RDBのテーブル設計はしていますでしょうか。
テーブル設計時の大きな関心事の1つとして、主キー設計があります。
主キー設計では、「自然キー vs サロゲートキー」や「連番 vs 乱数」が主題になることが多いですが、今回はカラムサイズに注目して、主キーのサイズが検索性能に与える影響について調査してみたいと思います。
インデックスを使った検索が高速であることはRDBの常識中の常識ですが、その時もディスクやメモリからインデックスの値を読み取ってCPUを使って比較を行う操作が発生するわけで、値のサイズが小さいことは理屈上ではCPUキャッシュの利用やその他IO処理などにおいて有利に働くはずです。
今回は主キーを指定した単体取得のクエリにおいて、その影響がどの程度なのかを実際に計測して確認してみたいと思います。
実行環境
今回の比較は手元のPCを使って、以下の環境で実行しています。
PostgreSQLはコンテナ等を使わず、brewで直接インストールしたものを使用しています。 コンフィグは全てデフォルト値そのままです。
- マシン: MacBook Air
- チップ: AppleM3
- メモリ: 16GB
- OS: Sonoma 14.6.1
- RDBMS: PostgreSQL
- バージョン: 16.4 (Homebrew)
- shared_buffers: 128MB
テーブル定義
今回の比較対象としてint, bigint, uuid のカラムを主キーにしたテーブルとそれらを文字列変換したテーブル、計6つのテーブルを定義します。
主キーサイズ以外の条件をなるべく揃えるため、各テーブル異なる長さの txt_column を定義して、レコード全体のサイズを揃えています。
idの採番方法は乱数方式に統一しています。*1
/* 主キーサイズ: 4 Byte */ CREATE TABLE int_table ( id INT NOT NULL PRIMARY KEY DEFAULT round(random() * 2147483647), txt_column VARCHAR(60) NOT NULL DEFAULT repeat('a', 60) ); /* 主キーサイズ: 8 Byte */ CREATE TABLE long_table ( /* random()では精度が足りないので、デフォルト値は上位桁と下位桁で半分ずつ生成する */ id BIGINT NOT NULL PRIMARY KEY DEFAULT (random() * 2147483648)::int8 * 4294967296 + (random() * 4294967296)::int8, txt_column VARCHAR(56) NOT NULL DEFAULT repeat('a', 56) ); /* 主キーサイズ: 16 Byte */ CREATE TABLE uuid_table ( id UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(), txt_column VARCHAR(48) NOT NULL DEFAULT repeat('a', 48) ); /* 主キーサイズ: (10 + 1) Byte */ CREATE TABLE int_str_table ( id VARCHAR(10) NOT NULL PRIMARY KEY DEFAULT round(random() * 2147483647)::varchar, txt_column VARCHAR(53) NOT NULL DEFAULT repeat('a', 53) ); /* 主キーサイズ: (19 + 1) Byte */ CREATE TABLE long_str_table ( /* random()では精度が足りないので、デフォルト値は上位桁と下位桁で半分ずつ生成する */ id VARCHAR(19) NOT NULL PRIMARY KEY DEFAULT ((random() * 2147483648)::int8 * 4294967296 + (random() * 4294967296)::int8)::varchar, txt_column VARCHAR(44) NOT NULL DEFAULT repeat('a', 44) ); /* 主キーサイズ: (36 + 1) Byte */ CREATE TABLE uuid_str_table ( id VARCHAR(36) NOT NULL PRIMARY KEY DEFAULT gen_random_uuid()::varchar, txt_column VARCHAR(27) NOT NULL DEFAULT repeat('a', 27) );
レコード作成
各テーブル、以下の手順で約1600万件のレコードを作成します。 主キーに使用する乱数の重複で実際に保存されるレコード数には多少ばらつきがあります。
/* 最初の1件 */ INSERT INTO int_table DEFAULT VALUES; /* ×2^5 で32件 */ INSERT INTO int_table(txt_column) SELECT repeat('a', 60) FROM int_table ON CONFLICT DO NOTHING; INSERT INTO int_table(txt_column) SELECT repeat('a', 60) FROM int_table ON CONFLICT DO NOTHING; INSERT INTO int_table(txt_column) SELECT repeat('a', 60) FROM int_table ON CONFLICT DO NOTHING; INSERT INTO int_table(txt_column) SELECT repeat('a', 60) FROM int_table ON CONFLICT DO NOTHING; INSERT INTO int_table(txt_column) SELECT repeat('a', 60) FROM int_table ON CONFLICT DO NOTHING; /* 32 × 32 で1024件、1024×1024 で約100万件 */ INSERT INTO int_table(txt_column) SELECT repeat('b', 60) FROM int_table cross join int_table AS t2 ON CONFLICT DO NOTHING; INSERT INTO int_table(txt_column) SELECT repeat('b', 60) FROM int_table cross join int_table AS t2 ON CONFLICT DO NOTHING; /* ×2^4 で約1600万件 */ INSERT INTO int_table(txt_column) SELECT repeat('c', 60) FROM int_table ON CONFLICT DO NOTHING; INSERT INTO int_table(txt_column) SELECT repeat('c', 60) FROM int_table ON CONFLICT DO NOTHING; INSERT INTO int_table(txt_column) SELECT repeat('c', 60) FROM int_table ON CONFLICT DO NOTHING; INSERT INTO int_table(txt_column) SELECT repeat('c', 60) FROM int_table ON CONFLICT DO NOTHING;
今回の主題からは少し外れますが、レコード作成時間は概ね主キーのサイズに応じて長くなることが明確に感じ取れました。
例えば、最後の約800万件のINSERTにおいて、int_table は1分程度で処理が完了しましたが、uuid_str_table では30分以上かかりました。
テーブルサイズ
計測を行う前に、pg_class テーブルから各テーブルと主キーのインデックスのサイズを確認すると以下の通りになりました。
relname がテーブル名 or インデックス名、reltuples がレコード数、relpage がページファイルの数です。
1ページは8KBの設定なので、relpages をバイト数に直すと8192倍の値になります。
postgresql=# SELECT relname, reltuples, relpages FROM pg_class ORDER BY relpages DESC;
relname | reltuples | relpages
------------------------------------------------+---------------+----------
uuid_table | 1.7859124e+07 | 220483
long_table | 1.7859124e+07 | 220483
uuid_str_table | 1.7859124e+07 | 220483
int_table | 1.7750908e+07 | 219147
long_str_table | 1.7859316e+07 | 217823
int_str_table | 1.7750088e+07 | 210497
uuid_str_table_pkey | 1.7859124e+07 | 167161
long_str_table_pkey | 1.7859316e+07 | 116718
uuid_table_pkey | 1.7859124e+07 | 86911
int_str_table_pkey | 1.7750088e+07 | 86009
long_table_pkey | 1.7859124e+07 | 65637
int_table_pkey | 1.7750908e+07 | 65440
...
テーブル定義で指定した通り、テーブル全体は全て概ね同じサイズになっていますが、各インデックスのサイズはカラムのサイズと同じ順序になっています。
ただし、int_table_pkey と long_table_pkey を比べて分かる通り、カラムサイズが2倍になるとインデックスサイズも2倍になるような単純な関係ではないようです。
検索速度
PostgreSQL上で以下のような関数を定義して、単体取得を1000件実行したときの時間を計測します。
CREATE OR REPLACE FUNCTION select_int() RETURNS numeric AS $$ DECLARE random_condition VARCHAR; DECLARE target_ids INTEGER[]; DECLARE id_x INTEGER; DECLARE start_time TIMESTAMPTZ; DECLARE end_time TIMESTAMPTZ; BEGIN /* 計測開始前にランダムな3桁の数字を取得して、末尾がそれに一致するidを全て取得する*/ random_condition := '%' || (random() * 1000)::int; SELECT array_agg(id) INTO target_ids FROM int_table WHERE id::text LIKE random_condition; start_time := clock_timestamp(); /** 取得したIDを1000件使って、それぞれ単体取得のクエリを実行する */ FOREACH id_x IN ARRAY target_ids[:1000] LOOP PERFORM * FROM int_table WHERE id = id_x; END LOOP; end_time := clock_timestamp(); /** ミリ秒単位の実行時間を返す */ RETURN extract(milliseconds FROM end_time - start_time); END; $$ LANGUAGE plpgsql;
各テーブルに対して定義した関数を、以下のクエリでまとめて実行し、測定を行います。
各テーブル、計測の前にID取得のための全件スキャンが走るので、全体の実行時間は20秒程度です。
postgresql=# SELECT select_int(), select_long(), select_uuid(), select_int_str(), select_long_str(), select_uuid_str();
select_int | select_long | select_uuid | select_int_str | select_long_str | select_uuid_str
------------+-------------+-------------+----------------+-----------------+-----------------
203.019 | 201.413 | 234.970 | 140.201 | 237.758 | 279.484
(1 行)
他プロセスの状態などによって結果には誤差があるので、20回実行して集計した結果が以下になります。
| テーブル名 | int_table | long_table | uuid_table | int_str_table | long_str_table | uuid_str_table |
|---|---|---|---|---|---|---|
| 平均 (ms) | 222.4 | 216.2 | 257.9 | 167.7 | 236.2 | 316.5 |
| 標準誤差 | 7.1 | 5.5 | 7.3 | 6.0 | 5.9 | 9.3 |
文字列型同士の比較では主キーのサイズと検索速度にきれいな相関が見られますが、異なる型の間では主キーサイズは検索速度の大きな決定要因にはならないようです。
特に int_table の検索が想像よりもずっと遅く、long_table よりも int_str_table よりも遅くなっています。
int_str_table の結果は主キーのサイズがより小さい long_table の結果よりも早いので、文字列比較が早くなる原因 (or 数値比較が遅くなる原因) が何かしら存在していそうです。
ただ、UUIDに限って言えば、「UUIDを文字列型で保存すると速度的に不利」という言説は、顕著な違いが確認できました。
結論
「主キーのカラムサイズが大きいほど検索性能が下がる」という常識的な仮説は、実際に計測してみたら、異なる型の間では成立しませんでした。
「推測するな、計測せよ」という格言の大切さを実感させられます。
ただ、クエリの実行速度はRDBMSの実装だけではなくCPUアーキテクチャなどにも依存しているはずなので、クラウドサービス上などの別の環境で計測したら別の結果が出るかもしれません。
例えば、1つの比較実験として同じ環境で shared_buffer のサイズを最小値の128kBまで下げた状態でも計測を実行してみましたが、テーブルごとの傾向も実際の計測時間も大きな変化は見られませんでした。
今回の計測は全てPostgreSQLのコンソール上だけで完結するようになっているので、この結果を不思議に思った人はそれぞれの環境でも再現するかどうか、試してみてください。
We are hiring!
株式会社ドワンゴの教育事業では、一緒に未来の当たり前の教育をつくるメンバーを募集しています。
私の所属するチームではさまざまなバックグラウンドを持つ企画職とエンジニア、データサイエンティストが一丸となってプロジェクトを進めています。得意なこと不得意なことを補い合いつつお互いの領域に染みだすことのできる退屈しないチームです！
カジュアル面談も行っています。お気軽にご連絡ください！
開発チームの取り組み、教育事業の今後については、他の記事や採用資料をご覧ください。
*1:乱数IDは読み取り時にキャッシュ効率で不利だという主張をよく見ますが、その根拠はMySQLのクラスタインデックス方式を前提としているように見えます。また、クラスタインデックスを利用する場合でもキャッシュ効率に実際に不利に働くかどうかはページファイルあたりのレコード数や実際のアクセス傾向などに大きく依存するでしょう。例えば、毎日大量のユーザーがレコードを作成するがアクセスは一部の人気ユーザーに集中するブログサービスなどでは時系列順の主キーの効果は小さく、ユーザーID含めた複合主キーにしたほうが読み取り時のキャッシュ効率は高いと考えられます。
