こちらを拝見して、衝撃を受けました 💦
複合インデックスで先頭にマッチしなくてもインデックスが効くと...
特に気になったのは、複合カラムインデックスと単一カラムインデックス(先頭にマッチ)でどの程度差がでるのかという点でした。
とても気になったので、検証してみました。
🖥️ 検証環境のセットアップ
データベース構成
今回の検証では、PostgreSQL 18の新機能「スキップスキャン」に焦点を当てるため、PostgreSQL 18のみを使用しました。
postgres@0.0.0.0:benchmark> select * From version();
+-----------------------------------------------------------------------------------------+
| version |
|-----------------------------------------------------------------------------------------|
| PostgreSQL 18.0 on x86_64-pc-linux-musl, compiled by gcc (Alpine 14.2.0) 14.2.0, 64-bit |
+-----------------------------------------------------------------------------------------+
SELECT 1
Time: 0.005s
テーブル設計
検証用に3つのテーブルを用意しました：
-- 1. 複合インデックス（スキップスキャン検証用）
CREATE TABLE events_composite (
id BIGSERIAL PRIMARY KEY,
tenant_id INTEGER NOT NULL,
event_type TEXT NOT NULL,
event_id BIGINT NOT NULL,
payload JSONB,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX idx_composite ON events_composite(tenant_id, event_type, event_id);
-- 2. 単一カラムインデックス（ベストケース比較用）
CREATE TABLE events_single (
id BIGSERIAL PRIMARY KEY,
tenant_id INTEGER NOT NULL,
event_type TEXT NOT NULL,
event_id BIGINT NOT NULL,
payload JSONB,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
CREATE INDEX idx_single ON events_single(event_type);
-- 3. インデックスなし（ベースライン）
CREATE TABLE events_noindex (
id BIGSERIAL PRIMARY KEY,
tenant_id INTEGER NOT NULL,
event_type TEXT NOT NULL,
event_id BIGINT NOT NULL,
payload JSONB,
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);
テストデータ
スキップスキャンの効果を測定するため、以下のようなデータを投入しました：
データ生成SQL:
-- 1. ベースとなるeventsテーブルにテストデータを生成
-- generate_benchmark_data(行数, テナント数, event_type数, payload_size)
SELECT generate_benchmark_data(10000000, 10, 10000000, 256);
-- 2. 生成されたデータを各比較テーブルにコピー
-- 複合インデックス用テーブル
INSERT INTO events_composite SELECT * FROM events;
-- 単一カラムインデックス用テーブル
INSERT INTO events_single SELECT * FROM events;
-- インデックスなしテーブル
INSERT INTO events_noindex SELECT * FROM events;
-- 3. 統計情報を更新（クエリプランナーが最適な実行計画を選択するため）
ANALYZE events_composite;
ANALYZE events_single;
ANALYZE events_noindex;
generate_benchmark_data関数の詳細:
この関数は、ランダムなevent_typeとpayloadを生成するカスタム関数です。
-- Function to generate random event data
CREATE OR REPLACE FUNCTION generate_benchmark_data(
p_row_count INTEGER DEFAULT 1000000,
p_tenant_count INTEGER DEFAULT 10,
p_event_type_count INTEGER DEFAULT 5,
p_payload_size_bytes INTEGER DEFAULT 256
) RETURNS TABLE (
inserted_rows BIGINT,
execution_time_ms NUMERIC
) AS $$
DECLARE
v_start_time TIMESTAMP;
v_end_time TIMESTAMP;
v_row_count BIGINT;
v_payload TEXT;
BEGIN
v_start_time := clock_timestamp();
-- Generate a sample payload of approximately the requested size
v_payload := repeat('x', p_payload_size_bytes - 50); -- Reserve space for JSON structure
-- Truncate existing data
TRUNCATE TABLE events;
-- Insert benchmark data in batches for better performance
INSERT INTO events (tenant_id, event_type, event_id, payload, created_at)
SELECT
(random() * (p_tenant_count - 1) + 1)::INTEGER as tenant_id,
'event_type_' || (random() * (p_event_type_count - 1) + 1)::INTEGER as event_type,
generate_series as event_id,
jsonb_build_object(
'data', v_payload,
'timestamp', NOW() - (random() * interval '30 days'),
'sequence', generate_series
) as payload,
NOW() - (random() * interval '30 days') as created_at
FROM generate_series(1, p_row_count);
GET DIAGNOSTICS v_row_count = ROW_COUNT;
v_end_time := clock_timestamp();
RETURN QUERY SELECT
v_row_count,
EXTRACT(EPOCH FROM (v_end_time - v_start_time)) * 1000;
END;
データ生成ロジック:
- tenant_id: 1からp_tenant_countまでのランダムな整数
-
event_type:
'event_type_1'から'event_type_N'までのランダムな文字列 - event_id: 1からp_row_countまでの連番
- payload: 約256バイトのJSONBオブジェクト（ランダムな日時とシーケンス番号を含む）
- created_at: 過去30日間のランダムな日時
今回の検証では、「10テナント、1000万行、1000万種類のevent_type」で生成し、
各event_typeがランダムに分散され、
実際には 6,321,498種類 のevent_typeが生成されたため、
平均1.58行/event_type という状態になっています。
-
総行数:
1000万行 -
event_type数:
6,321,498種類
📊 ベンチマーク設計
実装したベンチマーク
RustのTokioとsqlxを使って、非同期ベンチマークツールを実装しました。
検証クエリ:
SELECT * FROM {table} WHERE event_type = $1
先頭カラムをスキップして event_type で検索することで、スキップスキャンの効果を測定しました。
ベンチマーク条件:
- 実行時間: 30秒間
- 並行度: 10 (Tokioで10個の並行タスク)
- event_typeの選択: 1000万種類からランダムに選択
- メトリクス: QPS (Queries Per Second)、レイテンシ百分位数 (p50, p90, p95, p99)
実装コード（抜粋）:
async fn query_by_event_type(&self, pool: &PgPool) -> QueryResult {
let table_name = self.get_table_name();
let (event_type,) = {
let mut rng = rand::thread_rng();
let event_type = format!("event_type_{}", rng.gen_range(1..=10_000_000));
(event_type,)
};
let start = Instant::now();
let query = format!("SELECT * FROM {} WHERE event_type = $1", table_name);
let result = sqlx::query(&query)
.bind(&event_type)
.fetch_all(pool)
.await;
let duration = start.elapsed();
match result {
Ok(_) => QueryResult::Success(duration),
Err(e) => QueryResult::Error(e.to_string()),
}
}
🔥 ベンチマーク結果
PostgreSQL 18での結果
実際にベンチマークを実行した結果がこちらです：
| シナリオ | クエリ数 | スループット (QPS) | p50 | p90 | p95 | p99 | 単一インデックスとの比較 |
|---|---|---|---|---|---|---|---|
| 単一カラムインデックス | 605,566 | 20,185.53 | 0.45ms | 0.56ms | 0.60ms | 0.72ms | ベースライン（最速） |
| 複合インデックス (スキップスキャン) | 507,577 | 16,919.23 | 0.54ms | 0.66ms | 0.72ms | 0.85ms | -16.2% |
| インデックスなし | 74 | 2.47 | 3,570ms | 6,120ms | 6,210ms | 6,290ms | -99.99% (8,160倍遅い) |
結果:
- ✅ スキップスキャンは単一インデックスの約84%の性能を発揮（16%の低下のみ）
- ✅ 30秒間の処理量
- 単一インデックス
60万クエリ - 複合インデックス
50万クエリ
- 単一インデックス
- ❌ インデックスなしだと30秒で74クエリしか処理できない（レイテンシは3.6秒）
複合インデックスのみでも、単一カラムインデックスの約84%の性能が出るなら、
インデックス数を削減できるケースはそれなりにありそう。
💡 EXPLAIN ANALYZEで実行計画を確認
ベンチマーク結果の背景を理解するため、実際の実行計画を確認しました。
PostgreSQL 18での実行計画:
-- 実際に3行存在するevent_typeを使用して検証
-- 1. 単一カラムインデックス (idx_single)
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events_single WHERE event_type = 'event_type_1379645';
Index Scan using idx_single on events_single
(cost=0.56..12.59 rows=2 width=347)
(actual time=0.062..0.083 rows=3 loops=1)
Index Cond: (event_type = 'event_type_1379645'::text)
Index Searches: 1
Buffers: shared read=7
Execution Time: 0.206 ms
-- 2. 複合インデックス (idx_composite) - スキップスキャン
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events_composite WHERE event_type = 'event_type_1379645';
Index Scan using idx_composite on events_composite
(cost=0.56..58.24 rows=2 width=347)
(actual time=0.280..0.669 rows=3 loops=1)
Index Cond: (event_type = 'event_type_1379645'::text)
Index Searches: 12
Buffers: shared hit=38 read=22
Execution Time: 0.899 ms
-- 3. インデックスなし
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events_noindex WHERE event_type = 'event_type_1379645';
Gather
(cost=1000.00..529274.59 rows=2 width=347)
(actual time=530.796..541.021 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared read=476191
-> Parallel Seq Scan on events_noindex
Filter: (event_type = 'event_type_1379645'::text)
Rows Removed by Filter: 3333332
Buffers: shared read=476191
Execution Time: 558.545 ms
重要なポイント:
-
単一カラムインデックス:
- 1回のインデックス検索で完了（0.206ms、3行取得）
-
複合インデックス (スキップスキャン):
- 12回のインデックス検索が必要（0.899ms、3行取得）※これがスキップスキャンの動作
- 複合インデックスの先頭カラム（tenant_id）の各値に対してインデックス検索を実行
- 10テナント存在するため、約12回の検索が必要
- スキップスキャンは単一インデックスの約4.4倍の時間がかかる
-
インデックスなし:
- 並列シーケンシャルスキャン（558.545ms、3行取得）
- スキップスキャンの約620倍、単一インデックスの約2,710倍遅い
スキップスキャンは複数回のインデックス検索を行うため単一インデックスより遅くなりますが、それでもフルスキャンに比べて圧倒的に高速です。
キャッシュの影響
初回実行時と2回目以降でキャッシュの違いがあるので、性能が大きく異なります。
スキップスキャンも例外ではありません：
-- PostgreSQL 18: 複合インデックス (スキップスキャン) での性能差
-- 1回目の実行（コールドキャッシュ）
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM events_composite WHERE event_type = 'event_type_1379645';
Index Scan using idx_composite on events_composite
(cost=0.56..58.24 rows=2 width=347)
(actual time=0.280..0.669 rows=3 loops=1)
Index Cond: (event_type = 'event_type_1379645'::text)
Index Searches: 12
Buffers: shared hit=38 read=22 ← 22ブロックをディスクから読み込み
Execution Time: 0.899 ms
-- 2回目の実行（ホットキャッシュ）
Index Scan using idx_composite on events_composite
(cost=0.56..58.24 rows=2 width=347)
(actual time=0.092..0.157 rows=3 loops=1)
Index Cond: (event_type = 'event_type_1379645'::text)
Index Searches: 12
Buffers: shared hit=60 ← すべてメモリキャッシュから取得
Execution Time: 0.257 ms
性能差:
- 1回目: 0.899ms（ディスクI/Oあり）
- 2回目: 0.257ms（キャッシュのみ）
- 高速化率: 約3.5倍（71%削減）
実用上、頻繁にアクセスされるクエリは2回目以降の性能（0.257ms）が基準になります。
ただし、キャッシュが効いた状態でも単一インデックスとの性能差は約16%（0.206ms vs 0.257ms = 約1.2倍）存在します。
これはベンチマーク結果（約16%の差）とも一致しています。
🤔 考察
性能差と実用性
単一インデックス vs 複合インデックスでのスキップスキャン:
- 単一インデックス: インデックスの先頭カラムに完全マッチするため、1回の検索で完了（0.206ms）
- 複合インデックス（スキップスキャン）: 先頭カラムをスキップして検索するため、複数回の検索が必要（0.899ms、約4.4倍遅い）
この結果から、やはり性能差は存在します。
やはり、さすがに、すべて複合インデックスで良いわけではないですね。
スキップスキャンが救えるケース
ただし、スキップスキャンで救えるケースもあると思いました。
最悪のケースを回避できる:
- インデックスを忘れていた場合: 今まではフルスキャン（558ms、約620倍遅い）になり、負荷がかかって障害になるケースも考えられます
- PostgreSQL 18では: 複合インデックスがあれば、スキップスキャンが自動的に使われ、パフォーマンス劣化を多少救えます
つまり、スキップスキャンは「最適な性能」を出すための機能ではなく、「インデックス設計のミスをカバーする」安全網のようにも機能するかも、と思いました。
実用上の判断基準
理想的なアプローチ（PostgreSQL 18でも）:
- 頻繁に検索されるカラムには、専用のインデックスを作成
- 最高のパフォーマンスが必要な場合は、妥協しない
スキップスキャンに頼れるケース:
- インデックス数を極力減らしたい（書き込み性能重視）
- クエリパターンが予測しにくく、複合インデックスだけで対応したい
- 多少の性能劣化（16%程度）は許容できる
注意点:
- カーディナリティに依存: event_typeの種類が少ない場合、効果は限定的
- PostgreSQL 18限定: PG17以前では使えない
📈 まとめ
PostgreSQL 18のスキップスキャン機能を実際に検証してみました。
結果のサマリー:
- ✅ スキップスキャンは単一インデックスの約84%の性能を発揮（約16%遅い）
- ✅ フルスキャンと比較すると約620倍高速（最悪のケースを回避）
- ⚠️ やはり性能差は存在する - すべて複合インデックスで良いわけではない
スキップスキャンの効果:
- インデックス設計のミスをカバーする安全網: インデックスを忘れていてフルスキャンになるケースを救えます
-
OR条件を使ったクエリの最適化: PostgreSQL 18のリリースノートにも記載されているように、WHERE句でOR条件を使うクエリをインデックスで高速化できます
-
It can also optimize queries that use OR conditions in a WHERE to use an index, leading to significantly faster execution.
-
- 書き込み性能の向上: インデックス数を削減することで、INSERT/UPDATE/DELETE時のインデックス更新コストを減らせます
パフォーマンス的な理想としては、頻繁に検索されるカラムには専用のインデックスを作成するほうがいいかと思いますが、
インデックス数を削減したい場合（書き込み性能重視）や、クエリパターンが予測しにくい場合には、スキップスキャンに頼る選択肢もありえますね。
Discussion