これさえあれば安心！ PostgreSQL COUNT(DISTINCT ...) のトラブルシューティングガイド
2024-07-27
PostgreSQL COUNT(DISTINCT ...) のパフォーマンス
原因
COUNT(DISTINCT ...) は、以下の理由により遅くなる可能性があります。
- データ量が多い
- DISTINCT に指定された列に重複が多い
- インデックスがない
解決策
以下の対策により、パフォーマンスを改善することができます。
- インデックスの作成
- DISTINCT に使用する列の選択
- 代替クエリを使用する
DISTINCT に使用する列にインデックスを作成することで、クエリのパフォーマンスを大幅に向上させることができます。
CREATE INDEX index_name ON table_name (column_name);
DISTINCT に使用する列は、重複が少ない列を選択する必要があります。
場合によっては、COUNT(DISTINCT ...) の代わりに別のクエリを使用することで、パフォーマンスを改善することができます。
- GROUP BY
- CASE WHEN
- 上記は一般的な解決策であり、具体的な状況によって最適な方法は異なる場合があります。
- クエリのパフォーマンスを改善するには、他の要因も考慮する必要があります。
例
以下の例では、customers テーブルの country 列の個別の値の数をカウントしています。
SELECT COUNT(DISTINCT country)
FROM customers;
このクエリが遅い場合は、以下の対策を試すことができます。
country列にインデックスを作成するCOUNT(DISTINCT country)の代わりにGROUP BY countryを使用する
SELECT country, COUNT(*)
FROM customers
GROUP BY country;
注意
COUNT(DISTINCT ...)は、NULL 値をカウントしません。
CREATE TABLE customers (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
country VARCHAR(2) NOT NULL,
PRIMARY KEY (id)
);
データ
INSERT INTO customers (name, country)
VALUES
('John Doe', 'US'),
('Jane Doe', 'US'),
('John Smith', 'UK'),
('Jane Smith', 'UK'),
('John Doe', 'CA'),
('Jane Doe', 'CA');
クエリ
-- インデックスなし
SELECT COUNT(DISTINCT country)
FROM customers;
-- インデックスあり
CREATE INDEX index_country ON customers (country);
SELECT COUNT(DISTINCT country)
FROM customers;
-- GROUP BY
SELECT country, COUNT(*)
FROM customers
GROUP BY country;
結果
-- インデックスなし
| count |
|-------|
| 3 |
-- インデックスあり
| count |
|-------|
| 3 |
-- GROUP BY
| country | count |
|---------|-------|
| CA | 2 |
| UK | 2 |
| US | 2 |
解説
- 最初のクエリはインデックスなしで実行されるため、パフォーマンスが遅くなります。
- 2番目のクエリは
country列にインデックスを作成してから実行されるため、パフォーマンスが向上します。 - 3番目のクエリは
GROUP BYを使用して、country列ごとに個別の値の数をカウントしています。
実行方法
- PostgreSQL に接続します。
- 上記のコードをコピーして、新しいクエリウィンドウに貼り付けます。
- コードを実行します。
- 結果を確認します。
CREATE BITMAP INDEX index_name ON table_name (column_name);
テーブル全体ではなく、部分集に対してクエリを実行することで、パフォーマンスを改善することができます。
SELECT COUNT(DISTINCT country)
FROM customers
WHERE country IN ('US', 'UK');
サンプリングを使用する
SELECT COUNT(DISTINCT country)
FROM (
SELECT *
FROM customers
ORDER BY RANDOM()
LIMIT 1000
) AS sample;
APPROXIMATE COUNT DISTINCT を使用する
PostgreSQL 9.2 以降では、APPROXIMATE COUNT DISTINCT を使用して、DISTINCT クエリのパフォーマンスを向上させることができます。
SELECT APPROXIMATE COUNT(DISTINCT country)
FROM customers;
- 上記の方法は、すべてのパフォーマンス問題を解決するわけではありません。
- 具体的な状況によって最適な方法は異なる場合があります。
- インデックスのサイズとパフォーマンスのトレードオフを考慮する必要があります。
- クエリプランを分析して、パフォーマンスのボトルネックを見つけることができます。
performance postgresql count
