「テーブルに何件のデータが入っているか知りたい」
これは、データベースを使う上で最も基本的な要求の一つです。でも実は、行数のカウントには意外な落とし穴や、知っておくと便利なテクニックがたくさんあるんです。
1億件のテーブルでCOUNT(*)を実行して、返ってこない…なんて経験はありませんか？
この記事では、PostgreSQLでの行数カウントについて、基本的な使い方から、大規模データでも高速に動作するテクニックまで、実例を交えながら徹底的に解説していきます。
COUNT関数の基本
最もシンプルな行数カウント
-- テーブル全体の行数を取得
SELECT COUNT(*) FROM users;
-- 結果
-- count
-- ------
-- 10234
これが最も基本的な使い方です。usersテーブルの全行数が返ってきます。
COUNT(*)、COUNT(1)、COUNT(列名)の違い
よく議論になる3つの書き方。実は微妙な違いがあります。
-- サンプルテーブルを作成
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2),
description TEXT
);
-- テストデータを挿入
INSERT INTO products (product_name, price, description) VALUES
('商品A', 1000, '説明文A'),
('商品B', 2000, NULL), -- descriptionがNULL
('商品C', NULL, '説明文C'), -- priceがNULL
('商品D', 3000, '説明文D');
COUNT(*) – すべての行をカウント
SELECT COUNT(*) FROM products;
-- 結果: 4（すべての行）
特徴：
- NULLを含むすべての行をカウント
- 最も一般的で分かりやすい
- PostgreSQLでは内部的に最適化される
COUNT(1) – 定数でカウント
SELECT COUNT(1) FROM products;
-- 結果: 4（すべての行）
特徴：
- COUNT(*)と同じ結果
- 各行に対して定数1を評価
- PostgreSQLでは COUNT(*) と同じ処理になる
COUNT(列名) – NULL以外をカウント
SELECT COUNT(price) FROM products;
-- 結果: 3（priceがNULLでない行のみ）
SELECT COUNT(description) FROM products;
-- 結果: 3（descriptionがNULLでない行のみ）
特徴：
- NULLを除外してカウント
- 特定の列に値がある行数を知りたいときに便利
- NULL チェックが入るため、若干遅い
COUNT(DISTINCT 列名) – 重複を除いた数
-- カテゴリーテーブル
CREATE TABLE product_categories (
product_id INTEGER,
category VARCHAR(50)
);
INSERT INTO product_categories VALUES
(1, '家電'),
(2, '家電'),
(3, '家具'),
(4, '家電'),
(5, '食品');
-- 通常のCOUNT
SELECT COUNT(category) FROM product_categories;
-- 結果: 5
-- DISTINCTを使ったCOUNT
SELECT COUNT(DISTINCT category) FROM product_categories;
-- 結果: 3（家電、家具、食品）
条件付きカウント
WHERE句を使った基本的な条件指定
-- 価格が1000円以上の商品数
SELECT COUNT(*) FROM products
WHERE price >= 1000;
-- 今月登録されたユーザー数
SELECT COUNT(*) FROM users
WHERE created_at >= DATE_TRUNC('month', CURRENT_DATE)
AND created_at < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month';
-- 複数条件の組み合わせ
SELECT COUNT(*) FROM orders
WHERE status = 'completed'
AND total_amount > 10000
AND order_date >= '2024-01-01';
CASE文を使った条件別カウント
複数の条件を一度にカウントしたい場合に便利です。
-- 注文ステータス別のカウントを一度に取得
SELECT
COUNT(*) AS total_orders,
COUNT(CASE WHEN status = 'pending' THEN 1 END) AS pending_orders,
COUNT(CASE WHEN status = 'processing' THEN 1 END) AS processing_orders,
COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_orders,
COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_orders
FROM orders;
-- 結果
-- total_orders | pending_orders | processing_orders | completed_orders | cancelled_orders
-- -------------+----------------+-------------------+------------------+-----------------
-- 1000 | 230 | 150 | 580 | 40
FILTER句を使った条件付きカウント（PostgreSQL 9.4以降）
CASE文より読みやすい書き方です。
-- FILTER句を使った条件別カウント
SELECT
COUNT(*) AS total_users,
COUNT(*) FILTER (WHERE age >= 20 AND age < 30) AS users_20s,
COUNT(*) FILTER (WHERE age >= 30 AND age < 40) AS users_30s,
COUNT(*) FILTER (WHERE age >= 40 AND age < 50) AS users_40s,
COUNT(*) FILTER (WHERE is_premium = true) AS premium_users
FROM users;
-- 日付範囲別のカウント
SELECT
COUNT(*) FILTER (WHERE created_at >= CURRENT_DATE - INTERVAL '1 day') AS today,
COUNT(*) FILTER (WHERE created_at >= CURRENT_DATE - INTERVAL '7 days') AS this_week,
COUNT(*) FILTER (WHERE created_at >= CURRENT_DATE - INTERVAL '30 days') AS this_month
FROM activities;
GROUP BYを使ったグループ別カウント
基本的なグループ化
-- カテゴリー別の商品数
SELECT
category,
COUNT(*) AS product_count
FROM products
GROUP BY category
ORDER BY product_count DESC;
-- 結果
-- category | product_count
-- ------------+--------------
-- Electronics | 245
-- Clothing | 189
-- Books | 156
-- Food | 134
複数列でのグループ化
-- 年月別、ステータス別の注文数
SELECT
DATE_TRUNC('month', order_date) AS month,
status,
COUNT(*) AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', order_date), status
ORDER BY month, status;
-- 結果
-- month | status | order_count
-- ------------+-----------+------------
-- 2024-01-01 | completed | 450
-- 2024-01-01 | pending | 120
-- 2024-02-01 | completed | 520
-- 2024-02-01 | pending | 95
HAVINGを使った集計結果のフィルタリング
-- 10件以上の注文があるユーザーだけを表示
SELECT
user_id,
COUNT(*) AS order_count
FROM orders
GROUP BY user_id
HAVING COUNT(*) >= 10
ORDER BY order_count DESC;
-- 平均価格が5000円以上のカテゴリーと商品数
SELECT
category,
COUNT(*) AS product_count,
AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) >= 5000
ORDER BY avg_price DESC;
ウィンドウ関数を使った高度なカウント
累積カウント
-- 日別の登録者数と累積登録者数
SELECT
DATE(created_at) AS registration_date,
COUNT(*) AS daily_count,
SUM(COUNT(*)) OVER (ORDER BY DATE(created_at)) AS cumulative_count
FROM users
GROUP BY DATE(created_at)
ORDER BY registration_date;
-- 結果
-- registration_date | daily_count | cumulative_count
-- ------------------+-------------+-----------------
-- 2024-01-01 | 45 | 45
-- 2024-01-02 | 52 | 97
-- 2024-01-03 | 38 | 135
ランキング付きカウント
-- カテゴリー別商品数のランキング
SELECT
category,
COUNT(*) AS product_count,
RANK() OVER (ORDER BY COUNT(*) DESC) AS rank,
ROUND(
100.0 * COUNT(*) / SUM(COUNT(*)) OVER (),
2
) AS percentage
FROM products
GROUP BY category;
-- 結果
-- category | product_count | rank | percentage
-- ------------+---------------+------+-----------
-- Electronics | 245 | 1 | 30.95
-- Clothing | 189 | 2 | 23.86
-- Books | 156 | 3 | 19.70
移動平均のカウント
-- 7日間の移動平均注文数
SELECT
order_date,
COUNT(*) AS daily_orders,
ROUND(
AVG(COUNT(*)) OVER (
ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
), 2
) AS moving_avg_7days
FROM orders
GROUP BY order_date
ORDER BY order_date DESC
LIMIT 30;
パフォーマンス最適化
なぜCOUNT(*)は遅いのか？
PostgreSQLのMVCC（Multi-Version Concurrency Control）の仕組みにより、COUNT(*)は実際にすべての行を読む必要があります。
-- 実行計画を確認
EXPLAIN ANALYZE
SELECT COUNT(*) FROM large_table;
-- 結果例
-- Aggregate (cost=123456.78..123456.79 rows=1 width=8)
-- (actual time=5234.567..5234.568 rows=1 loops=1)
-- -> Seq Scan on large_table (cost=0.00..98765.43 rows=9876543 width=0)
-- (actual time=0.123..4567.890 rows=9876543 loops=1)
高速化テクニック1：概算値を使う
完全に正確な数字が不要な場合は、統計情報を使います。
-- pg_stat_user_tablesから概算値を取得（超高速）
SELECT
schemaname,
tablename,
n_live_tup AS estimated_count
FROM pg_stat_user_tables
WHERE tablename = 'large_table';
-- より正確な概算値（それでも高速）
SELECT
reltuples::BIGINT AS estimated_count
FROM pg_class
WHERE relname = 'large_table';
-- 関数化して使いやすく
CREATE OR REPLACE FUNCTION estimate_row_count(table_name text)
RETURNS BIGINT AS $$
DECLARE
row_count BIGINT;
BEGIN
EXECUTE format('
SELECT reltuples::BIGINT
FROM pg_class
WHERE oid = %L::regclass',
table_name
) INTO row_count;
RETURN row_count;
END;
$$ LANGUAGE plpgsql;
-- 使用例
SELECT estimate_row_count('users');
高速化テクニック2：カウンター用テーブル
頻繁にカウントが必要な場合は、専用のカウンターテーブルを作ります。
-- カウンターテーブル
CREATE TABLE table_counters (
table_name VARCHAR(100) PRIMARY KEY,
row_count BIGINT DEFAULT 0,
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- トリガー関数：INSERT時
CREATE OR REPLACE FUNCTION increment_counter()
RETURNS TRIGGER AS $$
BEGIN
UPDATE table_counters
SET row_count = row_count + 1,
last_updated = CURRENT_TIMESTAMP
WHERE table_name = TG_TABLE_NAME;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- トリガー関数：DELETE時
CREATE OR REPLACE FUNCTION decrement_counter()
RETURNS TRIGGER AS $$
BEGIN
UPDATE table_counters
SET row_count = row_count - 1,
last_updated = CURRENT_TIMESTAMP
WHERE table_name = TG_TABLE_NAME;
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
-- トリガーの設定
CREATE TRIGGER users_insert_counter
AFTER INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION increment_counter();
CREATE TRIGGER users_delete_counter
AFTER DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION decrement_counter();
-- カウンターの初期化
INSERT INTO table_counters (table_name, row_count)
SELECT 'users', COUNT(*) FROM users;
-- 使用（超高速）
SELECT row_count FROM table_counters WHERE table_name = 'users';
高速化テクニック3：部分インデックスの活用
特定条件の行数を頻繁にカウントする場合：
-- アクティブユーザーだけのインデックス
CREATE INDEX idx_active_users ON users(user_id)
WHERE is_active = true;
-- このカウントが高速化される
SELECT COUNT(*) FROM users WHERE is_active = true;
-- 今月の注文だけのインデックス
CREATE INDEX idx_current_month_orders ON orders(order_id)
WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE);
-- 実行計画で確認
EXPLAIN ANALYZE
SELECT COUNT(*) FROM orders
WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE);
高速化テクニック4：並列クエリの活用
PostgreSQL 9.6以降では、並列処理でCOUNTを高速化できます。
-- 並列処理の設定を確認
SHOW max_parallel_workers_per_gather;
-- セッションレベルで並列度を上げる
SET max_parallel_workers_per_gather = 4;
-- 大きなテーブルでのカウント（自動的に並列化）
SELECT COUNT(*) FROM very_large_table;
-- 実行計画で並列処理を確認
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) FROM very_large_table;
-- Parallel Seq Scan が表示されれば並列処理されている
実践的な使用例
ECサイトのダッシュボード用カウント
-- ダッシュボード用の各種カウントを一度に取得
WITH date_ranges AS (
SELECT
CURRENT_DATE AS today,
CURRENT_DATE - INTERVAL '1 day' AS yesterday,
DATE_TRUNC('week', CURRENT_DATE) AS this_week,
DATE_TRUNC('month', CURRENT_DATE) AS this_month
)
SELECT
-- ユーザー関連
(SELECT COUNT(*) FROM users) AS total_users,
(SELECT COUNT(*) FROM users u, date_ranges d
WHERE u.created_at >= d.today) AS new_users_today,
-- 注文関連
(SELECT COUNT(*) FROM orders o, date_ranges d
WHERE o.order_date >= d.this_month) AS orders_this_month,
(SELECT COUNT(*) FROM orders
WHERE status = 'pending') AS pending_orders,
-- 商品関連
(SELECT COUNT(*) FROM products
WHERE stock_quantity > 0) AS products_in_stock,
(SELECT COUNT(*) FROM products
WHERE stock_quantity = 0) AS products_out_of_stock,
-- 売上関連
(SELECT COUNT(DISTINCT user_id) FROM orders o, date_ranges d
WHERE o.order_date >= d.this_month) AS unique_customers_this_month;
ページネーション用のカウント
-- 検索結果の総件数とページデータを同時に取得
WITH search_results AS (
SELECT
product_id,
product_name,
price,
COUNT(*) OVER() AS total_count -- ウィンドウ関数で総件数を取得
FROM products
WHERE
product_name ILIKE '%検索キーワード%'
AND price BETWEEN 1000 AND 5000
AND is_active = true
ORDER BY created_at DESC
LIMIT 20 OFFSET 40 -- 3ページ目（20件ずつ）
)
SELECT
product_id,
product_name,
price,
total_count
FROM search_results;
-- より効率的な方法（別々にクエリ）
-- 1. まず総件数を取得
SELECT COUNT(*) AS total_count
FROM products
WHERE
product_name ILIKE '%検索キーワード%'
AND price BETWEEN 1000 AND 5000
AND is_active = true;
-- 2. 次にページデータを取得
SELECT
product_id,
product_name,
price
FROM products
WHERE
product_name ILIKE '%検索キーワード%'
AND price BETWEEN 1000 AND 5000
AND is_active = true
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;
時系列データのカウント
-- 時間帯別のアクセス数
SELECT
EXTRACT(HOUR FROM access_time) AS hour,
COUNT(*) AS access_count,
ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS percentage
FROM access_logs
WHERE access_time >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY EXTRACT(HOUR FROM access_time)
ORDER BY hour;
-- 日別・週別・月別を同時に集計
SELECT
DATE_TRUNC('day', created_at) AS date,
COUNT(*) AS daily_count,
SUM(COUNT(*)) OVER (
PARTITION BY DATE_TRUNC('week', created_at)
ORDER BY DATE_TRUNC('day', created_at)
) AS weekly_cumulative,
SUM(COUNT(*)) OVER (
PARTITION BY DATE_TRUNC('month', created_at)
ORDER BY DATE_TRUNC('day', created_at)
) AS monthly_cumulative
FROM events
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY DATE_TRUNC('day', created_at)
ORDER BY date DESC;
トラブルシューティング
問題1：COUNT(*)が永遠に終わらない
原因： 巨大テーブルで全件スキャンが発生
解決策：
-- 1. タイムアウトを設定
SET statement_timeout = '30s';
-- 2. 概算値で代用
SELECT reltuples::BIGINT FROM pg_class WHERE relname = 'table_name';
-- 3. サンプリングで推定
SELECT
COUNT(*) * 100 AS estimated_total
FROM large_table TABLESAMPLE SYSTEM (1); -- 1%のサンプリング
問題2：GROUP BYのカウントが遅い
原因： インデックスがない、または不適切
解決策：
-- 複合インデックスを作成
CREATE INDEX idx_orders_user_status
ON orders(user_id, status)
INCLUDE (order_date);
-- パーティションテーブルの検討
CREATE TABLE orders_2024 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');
問題3：COUNT(DISTINCT)が遅い
原因： 重複排除のためのソートが発生
解決策：
-- 方法1：事前に集約
WITH unique_users AS (
SELECT DISTINCT user_id FROM orders
)
SELECT COUNT(*) FROM unique_users;
-- 方法2：近似値アルゴリズムを使用
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- HyperLogLogを使った近似カウント
SELECT COUNT(DISTINCT user_id) FROM orders; -- 正確だが遅い
-- かわりに
SELECT
ROUND(
COUNT(DISTINCT user_id)::NUMERIC *
(1.0 + 0.01 * (RANDOM() - 0.5)) -- ±0.5%の誤差で高速化
) AS approximate_count
FROM (
SELECT user_id
FROM orders
TABLESAMPLE BERNOULLI (10) -- 10%サンプリング
) sampled;
ベストプラクティス
1. 適切なCOUNT関数を選ぶ
-- 全行数が必要 → COUNT(*)
SELECT COUNT(*) FROM users;
-- NULL以外の値の数が必要 → COUNT(column)
SELECT COUNT(email) FROM users; -- メールアドレスを持つユーザー数
-- ユニークな値の数が必要 → COUNT(DISTINCT column)
SELECT COUNT(DISTINCT category) FROM products;
2. インデックスを活用する
-- よくカウントする条件にはインデックスを作成
CREATE INDEX idx_orders_status ON orders(status)
WHERE status IN ('pending', 'processing');
-- カバリングインデックスでIndex Only Scanを狙う
CREATE INDEX idx_users_active_created
ON users(is_active, created_at)
INCLUDE (email);
3. 定期的な統計情報の更新
-- 自動バキュームの設定を確認
SELECT
schemaname,
tablename,
last_vacuum,
last_autovacuum,
last_analyze,
last_autoanalyze,
n_live_tup,
n_dead_tup
FROM pg_stat_user_tables
WHERE tablename = 'your_table';
-- 手動で統計を更新
ANALYZE your_table;
-- より詳細な統計
ALTER TABLE your_table SET (autovacuum_analyze_scale_factor = 0.01);
4. 監視とアラート
-- カウントクエリの実行時間を監視
CREATE OR REPLACE FUNCTION monitored_count(
table_name TEXT,
condition TEXT DEFAULT NULL
) RETURNS TABLE(
count BIGINT,
execution_time INTERVAL
) AS $$
DECLARE
start_time TIMESTAMP;
end_time TIMESTAMP;
query TEXT;
result BIGINT;
BEGIN
start_time := clock_timestamp();
query := format('SELECT COUNT(*) FROM %I', table_name);
IF condition IS NOT NULL THEN
query := query || ' WHERE ' || condition;
END IF;
EXECUTE query INTO result;
end_time := clock_timestamp();
-- 実行時間が長い場合はログに記録
IF (end_time - start_time) > INTERVAL '5 seconds' THEN
RAISE NOTICE 'Slow count query on %: %',
table_name, end_time - start_time;
END IF;
RETURN QUERY SELECT result, end_time - start_time;
END;
$$ LANGUAGE plpgsql;
-- 使用例
SELECT * FROM monitored_count('large_table', 'status = ''active''');
まとめ：効率的な行数カウントのために
PostgreSQLでの行数カウントについて、基本から応用まで解説してきました。
重要なポイント：
- COUNT(*)とCOUNT(列名)の違いを理解する（NULLの扱い）
- 大規模テーブルでは概算値の活用を検討
- 頻繁なカウントにはカウンターテーブルやキャッシュを使用
- 適切なインデックスでパフォーマンスを改善
- 並列処理やサンプリングなど、PostgreSQLの機能を活用
単純に見えるCOUNT(*)も、データ量が増えると大きな課題になります。しかし、適切な手法を選択することで、パフォーマンスを大幅に改善できます。
状況別の推奨手法：
- 正確な値が必要 → インデックスを活用したCOUNT
- 概算で十分 → pg_classやサンプリング
- リアルタイム性が重要 → カウンターテーブル
- 分析用途 → ウィンドウ関数の活用
この記事で紹介したテクニックを組み合わせて、あなたのアプリケーションに最適な行数カウントを実装してください！
コメント