COUNTにおけるDISTINCT

目次 スキーマ 2つのCOUNT、2つの異なるプラン プランナーが分割できない理由 1つのDISTINCTがステートメント全体を台無しにする リライト：DISTINCTをGROUP BYに押し込む ORDER BY 集計は同じ壁にぶつかる より難しいケース：グループごとのDISTINCTカウント 実際に気にするべきとき 分析ワークロードで必ず現れるクエリがあります： SELECT count(DISTINCT user_id) FROM events; これは最も安価なもののように見えます：ユニークなユーザー数をカウントする。 コアに余裕のあるマシンでは、Postgresがそれに並列ワーカーをいくつか割り当てることを期待するでしょう。ほとんどの大きなスキャンに対してそうするように。 しかし、それはしません。 その1つのキーワード、DISTINCTは、ステートメント全体で並列クエリを無効にし、テーブルが大きければ大きいほどコストがかかります。 設定やインデックスの変更ではどうにもなりません。理由は集計がどのように実行されなければならないかにあります。 スキーマ 1000万件のイベント、約5万人のユニークユーザー、少数の国。 特に変わったところはありません。 CREATE TABLE events ( id bigint GENERATED ALWAYS AS IDENTITY, user_id int NOT NULL, country text NOT NULL, amount numeric(10,2) NOT NULL ); INSERT INTO events (user_id, country, amount) SELECT (random()*50000)::int + 1, (ARRAY['US','DE','GB','FR','JP','BR','IN','CA'])[(random()*7)::int + 1], (random()*500)::numeric(10,2) FROM generate_series(1, 10000000); ANALYZE events; max_parallel_workers_per_gather は、新規クラスタではデフォルトの2に設定されています。 これらの例では、それを4に、work_memを64MBに引き上げました。そのため、以下のプランの blames になるようなリソース不足はありません。 2つのCOUNT、2つの異なるプラン 単純なcount(*)から始めましょう。重複排除するものは何もありません： EXPLAIN (ANALYZE, COSTS OFF) SELECT count(*) FROM events; Finalize Aggregate (actual rows=1.00 loops=1) -> Gather (actual rows=5.00 loops=1) Workers Planned: 4 Workers Launched: 4 -> Partial Aggregate (actual rows=1.00 loops=5) -> Parallel Seq Scan on events (actual rows=2000000.00 loops=5) 4つのワーカーとリーダー（loops=5）がそれぞれスライスをスキャンし、実行中のカウントを維持します。リーダーは5つの部分的なカウントを最後に合計します。 今、1つの単語を追加します： EXPLAIN (ANALYZE, COSTS OFF, BUFFERS) SELECT count(DISTINCT user_id) FROM events; Aggregate (actual rows=1.00 loops=1) Buffers: shared hit=15915 read=47783, temp read=14681 written=14684 -> Sort (actual rows=10000000.00 loops=1) Sort Key: user_id Sort Method: external merge Disk: 117448kB Buffers: shared hit=15915 read=47783, temp read=14681 written=14684 -> Seq Scan on events (actual rows=10000000.00 loops=1) Buffers: shared hit=15912 read=47783 Gatherはありません。Partial Aggregateはありません。Parallelスキャンもありません。 1つのプロセスが1000万行すべてを読み込み、すべての行をuser_idでソートして重複が隣接するようにし、ソートされた出力を歩いてカウントします。 ソートは64MBのwork_memに収まらないため、115MBをディスク上のテンポラリファイルにスピルします。 1つのコア、テーブル全体、さらに並列count(*)が触れなかったディスクIO。 プランナーが分割できない理由 ソートは、Postgresが集計内でDISTINCTを計算する方法です：値を順序付け、隣接する同じ値を折りたたみます。 ハッシュテーブルも選択肢ですが、古典的なDISTINCT-aggregateパスはソートします。 どちらの方法でも、すべての値を1か所で見なければならず、それが問題全体です。 Postgresでの並列集計は2つの部分で機能します。各ワーカーは、Partial Aggregateを実行し、それが処理した行の小さな実行中の概要であるトランジション状態を構築します。 countの場合、その状態は単なる数値です。 リーダーは次に、集計のcombine関数でこれらの部分状態をマージするFinalize Aggregateを実行します。これは、2つの部分状態を1つに折りたたむ方法を知っているものです。 countのcombine関数は部分カウントを加算します。sum、avg、min、maxはすべてそれを持っています。 この分割、並列スキャン、最後に結合は、集計のための並列クエリの基盤全体です。 count(DISTINCT user_id) には、使用可能なcombineステップがありません。それは、誰もそれを書かなかったからではありません。 ワーカーが何を返せるか考えてみてください。 2つのワーカーの結果を正しいグローバルなユニークカウントにマージするには、リーダーは各ワーカーがどのユーザーを見たかを知る必要があります。なぜなら、ワーカー1のスライスに現れ、再びワーカー2のスライスに現れるユーザーは、2回ではなく1回カウントされなければならないからです。 ユニーク値の部分カウントは結合できません。すべてのワーカーからユニーク値のセット全体を送信し、それらを結合する必要があります。 その時点で、すでにすべてのデータを1か所に移動させていることになり、それは並列集計が存在する理由そのものを回避しています。 DISTINCT（または内部ORDER BY）を持つ集計は、Partialモードで実行できないため、プランナーはGatherの下にPartial Aggregateを配置できません。また、供給するPartial Aggregateがないため、並列スキャンは何も得られません。 プラン全体がシリアルに崩壊します。 PostgreSQL 17.10、18.4、19beta1でこれをチェックしました。Partial集計は、それらのいずれでも、まだDISTINCTおよびORDERED集計をカバーしていません。 debug_parallel_query は、これがシリアル実行を優先するコスト見積もりではないことを確認する方法です。 オンに設定すると、オプティマイザがシリアルの方が安いと考えている場合でも、プランナーは並列プランを可能な限り（合法的な場合）取得します： SET debug_parallel_query = on; EXPLAIN (COSTS OFF) SELECT count(DISTINCT user_id) FROM events; Gather Workers Planned: 1 Single Copy: true -> Aggregate -> Sort Sort Key: user_id -> Seq Scan on events Gatherが表示されますが、Workers Planned: 1とSingle Copy: trueです。1つのプロセスがプラン全体（ソートを含む）を実行し、Gatherノードは出力を行実行者の並列メカニズムにルーティングするためだけに存在します。 集計、ソート、またはスキャンのいずれも実際にワーカーに分割されません。 それは、debug_parallel_queryが並列インフラストラクチャを、作業を分割するPartial Aggregateがないプランに強制し、2番目のワーカーが実行できるものを見つけられなかったためです。 FILTER句にはこの問題はありません。 EXPLAIN (COSTS OFF) SELECT count(*) FILTER (WHERE country='US') FROM events; Finalize Aggregate -> Gather Workers Planned: 4 -> Partial Aggregate -> Parallel Seq Scan on events 単純なcount(*)と同じ並列形状です。FILTERは、各ワーカーがその部分カウントにどの行を折り込むかを決定するだけです。 1つのDISTINCTがステートメント全体を台無しにする コストはDISTINCT集計に限定されません。それは、それが共有するクエリブロックの集計ノードにスコープされます。 同じSELECTに完全に並列化可能な集計とDISTINCTなものを並べて配置すると、両方とも並列化を失います。これは、1つのAggregateノードが両方を計算し、1つの方法でしか実行できないという事実に起因します。 別のサブクエリまたはCTE内の集計は異なるノードであり、影響を受けません： EXPLAIN (COSTS OFF) SELECT sum(amount), count(DISTINCT user_id) FROM events; Aggregate -> Sort Sort Key: user_id -> Seq Scan on events sum(amount)はそれ自体で4つのワーカーで実行されたでしょう。1つのcount(DISTINCT)とSELECTを共有すると、それは同じシリアルソートに引きずり込まれます。 リライト：DISTINCTをGROUP BYに押し込む Postgresが並列化できる唯一の操作、GROUP BYで重複排除を行い、その後でグループ数をカウントします： SELECT count(*) FROM (SELECT user_id FROM events GROUP BY user_id) s; GROUP BY user_id はまさに「ユニークなuser_ids」であり、グループ化にはPartialモードがあります：各ワーカーは、見たグループのPartialハッシュを構築し、リーダーはそれらのハッシュをマージします。 出てきたグループの数をカウントするのは簡単です。 EXPLAIN (ANALYZE, COSTS OFF) SELECT count(*) FROM (SELECT user_id FROM events GROUP BY user_id) s; Aggregate (actual rows=1.00 loops=1) -> Finalize HashAggregate (actual rows=50001.00 loops=1) Group Key: events.user_id Batches: 1 Memory Usage: 3097kB -> Gather (actual rows=250005.00 loops=1) Workers Planned: 4 Workers Launched: 4 -> Partial HashAggregate (actual rows=50001.00 loops=5) Group Key: events.user_id Batches: 1 Memory Usage: 3097kB Worker 0: Batches: 1 Memory Usage: 3097kB Worker 1: Batches: 1 Memory Usage: 3097kB Worker 2: Batches: 1 Memory Usage: 3097kB Worker 3: Batches: 1 Memory Usage: 3097kB -> Parallel Seq Scan on events (actual rows=2000000.00 loops=5) リライトは再び並列です：4つのワーカーがそれぞれスライスをハッシュして、l