複合主キーを持つ Postgres テーブルで SELECT DISTINCT クエリが遅いのはなぜですか?そのパフォーマンスを改善するにはどうすればよいですか?

Postgres での遅い SELECT DISTINCT クエリの最適化

この記事では、複合主キーを持つ大規模な Postgres テーブルで SELECT DISTINCT クエリを実行するときに発生するパフォーマンスの問題について説明します。 約 200 万行と複合主キー (product_id、 trade_id) を持つテーブルを含む特定のシナリオを検討します。 SELECT DISTINCT product_id クエリは主キー インデックスにより高速であることが理想的ですが、予想外にパフォーマンスが低下することが観察されました。

根本原因分析:

クエリ プランナがインデックスを使用せずに順次スキャンを選択したことがボトルネックであることが判明しました。これはテーブルのデータ分散に起因します。一意の製品 ID は 40 個しか存在しないため、インデックス値の繰り返しが多くなります。 これにより、多数のインデックス プローブが発生し、非効率な連続アクセスが発生します。

効果的な解決策: 再帰的 CTE

この制限を回避し、インデックス作成を効率的に活用するために、SELECT DISTINCT:

の優れた代替手段として再帰共通テーブル式 (CTE) が提案されています。

<code class="language-sql">WITH RECURSIVE cte AS (
   (   -- parentheses required
   SELECT product_id
   FROM   tickers
   ORDER  BY 1
   LIMIT  1
   )
   UNION ALL
   SELECT l.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT product_id
      FROM   tickers t
      WHERE  t.product_id > c.product_id  -- lateral reference
      ORDER  BY 1
      LIMIT  1
      ) l
   )
TABLE  cte;</code>

この再帰的 CTE は、インデックス スキップ スキャンを効果的に模倣します。個別の product_id 値をソート順に繰り返し取得することで、非効率な順次スキャンに伴うパフォーマンスの低下を回避します。 このアプローチで最適なパフォーマンスを得るには、product_id 列でインデックスを使用することが重要です。

重要な注意: Postgres のインデックス スキップ スキャン機能は開発中ですが、この CTE ベースの回避策は、説明されているシナリオに対して堅牢で効率的なソリューションを提供し、クエリのパフォーマンスを大幅に向上させます。

以上が複合主キーを持つ Postgres テーブルで SELECT DISTINCT クエリが遅いのはなぜですか?そのパフォーマンスを改善するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。