1月21日、Haki Benita氏が「Unconventional PostgreSQL Optimizations」と題した記事を公開した。この記事では、PostgreSQLにおける独創的で型破りな最適化テクニックについて詳しく紹介されている。
以下に、その内容を紹介する。
データベースの最適化において、開発者はクエリの書き換えやインデックスの追加、非正規化といった定石に頼りがちだ。しかし、時には独創的なアプローチが大きな成果をもたらすことがある。本記事では、PostgreSQLにおける3つの型破りな最適化手法が提示されている。
1. CHECK制約に基づくフルテーブルスキャンの排除
PostgreSQLは、クエリの条件が制約によって「絶対に真になり得ない」と判断できる場合、テーブルスキャンをスキップする能力を持っている。
制約の例と課題
例えば、以下のテーブル定義があるとする。
CREATE TABLE users (
id INT PRIMARY KEY,
username TEXT NOT NULL,
plan TEXT NOT NULL,
CONSTRAINT plan_check CHECK (plan IN ('free', 'pro'))
);
ここで、アナリストが誤って plan = 'Pro'（大文字のP）で検索した場合、PostgreSQLはデフォルトでフルテーブルスキャンを実行する。制約上、値は 'free' か 'pro' しか存在し得ないにもかかわらずだ。
constraint_exclusion の活用
この挙動を最適化するには、constraint_exclusion パラメータを on に設定する。
SET constraint_exclusion to 'on';
EXPLAIN ANALYZE SELECT * FROM users WHERE plan = 'Pro';
-- 結果: Result (cost=0.00..0.00 rows=0 width=0)
-- One-Time Filter: false
これにより、PostgreSQLは制約を評価し、スキャンを完全にスキップする。この設定は、複雑なアドホッククエリが発行されるBI環境やデータウェアハウスで特に有効である。
2. 関数ベースインデックスと仮想生成列による低カーディナリティ最適化
日次レポートのようなクエリにおいて、タイムスタンプ全体にB-Treeインデックスを貼ることは、ストレージコストの面で非効率な場合がある。
関数ベースインデックスのサイズ削減
日時の精度がミリ秒単位まで不要な場合、日付部分のみをインデックス化することで、サイズを劇的に削減できる。
CREATE INDEX sale_sold_at_date_ix ON sale((date_trunc('day', sold_at AT TIME ZONE 'UTC'))::date);
元のB-Treeインデックスが214MBだったのに対し、この関数ベースインデックスは66MBまで縮小される。これは、値の重複（低カーディナリティ）により、PostgreSQLの「重複排除（Deduplication）」機能が効果的に働くためだ。
PostgreSQL 18の仮想生成列（Virtual Generated Columns）
関数ベースインデックスを確実に利用させるには、クエリ側で正確に同じ式を記述する必要がある。これを強制するために、PostgreSQL 18で導入された「仮想生成列」が利用できる。
ALTER TABLE sale ADD sold_at_date DATE
GENERATED ALWAYS AS (date_trunc('day', sold_at AT TIME ZONE 'UTC'));
これにより、ユーザーは複雑な式を書くことなく、シンプルに sold_at_date 列を参照するだけで、最適化されたインデックスの恩恵を受けられる。
3. ハッシュインデックスによる一意性の強制
長いURLなどの巨大な値を一意に保つ場合、通常のB-Treeインデックスは肥大化し、テーブルサイズと同等になることすらある。
排他制約（Exclusion Constraint）の応用
PostgreSQLのハッシュインデックスは値そのものではなくハッシュ値を格納するため非常にコンパクトだが、標準では一意制約（UNIQUE）をサポートしていない。しかし、「排他制約」を用いることで、ハッシュインデックスによる一意性の強制が可能となる。
ALTER TABLE urls ADD CONSTRAINT urls_url_unique_hash EXCLUDE USING HASH (url WITH =);
この手法によるメリットと制限は以下の通りだ。
メリット:
- インデックスサイズの大幅な削減（例：154MBから32MBへ）。
- 巨大な値に対する検索速度の向上。
制限事項:
- 外部キーの参照先として指定できない。
INSERT ... ON CONFLICT DO UPDATEがサポートされない（MERGE文での代用を推奨）。
紹介されたノウハウのまとめ
- constraint_exclusion の有効化: 制約に反するクエリによる無駄なフルテーブルスキャンを防止する。
- 関数ベースインデックス + 仮想生成列: インデックスの重複排除を促進し、ストレージを節約しつつクエリを高速化する。
- ハッシュインデックスによる一意性強制: 排他制約を利用し、巨大な文字列に対するインデックスサイズを劇的に削減する。
詳細はUnconventional PostgreSQL Optimizationsを参照していただきたい。
