PostgreSQLでブール値列のパフォーマンスを向上させる：インデックスと代替手段
2024-07-27
PostgreSQL におけるブール値列のインデックス作成
PostgreSQL におけるブール値列（true または false のみ許容）に対してインデックスを作成することは、特定の条件に基づいたデータ検索のパフォーマンスを向上させる有効な手段となりえます。しかし、インデックスは必ずしも全ての状況において高速化をもたらすわけではなく、適切な状況でのみ利用することが重要です。
インデックスが有効な状況
以下の状況において、ブール値列にインデックスを作成することでパフォーマンス向上が期待できます。
- DISTINCT 句にブール値列を含むクエリ
- GROUP BY 句にブール値列を含むクエリ
- WHERE 句にブール値列を含む等価比較演算子 (
=,!=) を使用するクエリ
以下の状況では、ブール値列にインデックスを作成してもパフォーマンス向上が見込めない可能性があります。
- インデックス付きのブール値列を含むクエリが頻繁に実行されない場合
- ブール値列の値が偏っている場合
- WHERE 句にブール値列を含む非等価比較演算子 (
<,>,<=,>=,LIKE,ILIKE) を使用するクエリ
インデックス作成方法
以下の SQL ステートメントを使用して、ブール値列に対してインデックスを作成できます。
CREATE INDEX index_name ON table_name (boolean_column);
例
CREATE INDEX active_users_index ON users (is_active);
注意点
- PostgreSQL は、インデックスの使用状況を自動的に分析し、必要に応じてインデックスの追加や削除を提案する機能を提供しています。
- 不要なインデックスは削除することで、ディスク領域の使用量を削減し、書き込み操作のパフォーマンスを向上させることができます。
- インデックスを作成する前に、クエリの実行計画を確認し、実際にインデックスがパフォーマンス向上に貢献するかどうかを確認することが重要です。
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(255) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
is_active BOOLEAN NOT NULL DEFAULT TRUE
);
CREATE INDEX idx_users_is_active ON users (is_active);
例2：部分インデックス作成
CREATE INDEX idx_users_is_active_true ON users (is_active) WHERE is_active;
この例では、orders テーブルの order_status 列が 'shipped' の場合のみ、is_shipped という名前のインデックスを作成します。
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id),
order_status VARCHAR(255) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_orders_is_shipped ON orders (order_status = 'shipped') WHERE order_status = 'shipped';
説明
- 式インデックスを作成するには、
=演算子を使用して条件を指定します。 WHERE句を使用して、部分インデックスを作成する場合があります。ON句を使用して、インデックスを作成する列を指定します。CREATE INDEXステートメントを使用して、インデックスを作成します。
- これらの例は、PostgreSQL 14.0 を使用しています。
インデックスの代わりに、以下の代替手段を検討することで、パフォーマンスとストレージ使用量のバランスを最適化することができます。
代替手段
クエリの最適化
集計表
マテリアライズドビュー
パーティショニング
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id),
order_status VARCHAR(255) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) PARTITION BY order_status;
CREATE INDEX idx_orders_shipped_partition_shipped ON orders (order_status = 'shipped') WHERE order_status = 'shipped';
CREATE INDEX idx_orders_shipped_partition_pending ON orders (order_status = 'pending') WHERE order_status = 'pending';
CREATE MATERIALIZED VIEW active_users AS
SELECT id, username, email, is_active
FROM users
WHERE is_active;
CREATE TABLE orders_summary AS
SELECT order_status, COUNT(*) AS order_count
FROM orders
GROUP BY order_status;
-- 非効率的なクエリ
SELECT * FROM users WHERE is_active = TRUE;
-- 効率的なクエリ
CREATE INDEX idx_users_is_active ON users (is_active);
SELECT * FROM users WHERE is_active = TRUE;
PostgreSQL におけるブール値列のインデックスは、適切な状況で使用することで有効な手段となりますが、必ずしも万能ではありません。代替手段を検討することで、パフォーマンスとストレージ使用量のバランスを最適化することができます。
postgresql boolean database-indexes
