複合主キーを使用した PostgreSQL SELECT DISTINCT クエリが非常に遅いのはなぜですか?

PostgreSQL SELECT DISTINCT 複合キーのパフォーマンスの問題

複合主キー (SELECT DISTINCT など) を含む PostgreSQL テーブルで (product_id, trade_id) を使用すると、驚くほど遅くなる可能性があります。 クエリ プランナーは、インデックスを効率的に利用する代わりに、順次スキャンを選択することがよくあります。

なぜ遅いのですか?

• シーケンシャル スキャンの設定: プランナは、インデックスを使用する場合でもテーブル全体のスキャンを優先する場合があり、これによりクエリ時間が大幅に長くなります。
• インデックス スキップ スキャンの欠落: PostgreSQL は、インデックス内の重複をスキップして一意の値を効率的に取得する最適化であるインデックス スキップ スキャンをネイティブにサポートしていません。

解決策: CTE を使用したインデックス スキップ スキャンのシミュレーション

真のインデックス スキップ スキャンは利用できませんが、再帰的な Common Table Expression (CTE) を使用してその動作を効果的に模倣できます。

WITH RECURSIVE cte AS (
   (   -- parentheses are crucial
   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
      ORDER  BY 1
      LIMIT  1
      ) l
   )
SELECT * FROM cte;

この CTE は、効率化のために product_id のインデックスを利用して、一意の (product_id) 値をソートされた順序で繰り返し処理します。

このアプローチの利点

• 速度の向上: この方法により、クエリの実行時間が大幅に短縮されます。 テストでは、225 万行のテーブルで 0.75 ミリ秒まで短縮されることがわかりました。
• インデックス使用率: 複合主キー インデックス (product_id, trade_id) と (product_id) のインデックスの両方を効果的に利用します。
• データ分散に依存しない: テーブル内のデータ分散に関係なく、パフォーマンスは一貫したままになります。

以上が複合主キーを使用した PostgreSQL SELECT DISTINCT クエリが非常に遅いのはなぜですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。