PostgreSQL の複合主キーを持つテーブルで SELECT DISTINCT が遅いのはなぜですか? 最適化するにはどうすればよいですか?

PostgreSQL の複合主キーを使用するテーブルについて SELECT DISTINCT クエリ速度が遅い理由と最適化方法

PostgreSQL データベースでは、SELECT DISTINCT クエリの実行速度はテーブル構造とデータ分散に依存します。 tickers テーブルの product_id 列は複合主キーの一部であるため、その列にインデックスが付けられますが、SELECT DISTINCT product_id FROM tickers を使用して一意の product_id を取得するクエリは、デフォルトで順次スキャンを実行します。

パフォーマンスが遅い理由

のパフォーマンスが遅い主な理由は、テーブル内に product_id の重複した値があることです。これは、PostgreSQL が取得した一意の product_id ごとにテーブル全体をスキャンして、重複がないことを確認する必要があることを意味します。

解決策: インデックス スキップ スキャンをシミュレートします

PostgreSQL はまだインデックス スキップ スキャンをネイティブにサポートしていないため、再帰 CTE (共通テーブル式) を使用してこの動作をシミュレートできます。この CTE は重複の取得と破棄を繰り返し、一意の各 product_id の 1 つを除くすべてのインスタンスを効果的に除外します。

改善されたソリューション

<code class="language-sql">WITH RECURSIVE cte AS (
   (   -- 括号必需
   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
   )
TABLE  cte;</code>

このクエリは水平結合を使用してソートされたテーブルを横断し、 orderBy を使用して一意の product_id 値を取得します。

結論

SELECT DISTINCT product_id クエリの実行時間は、CTE メソッドを使用してインデックス スキップ スキャンをシミュレートすることで大幅に改善され、それによって tickers テーブルから一意の product_id を取得するのに必要な時間を短縮できます。

以上がPostgreSQL の複合主キーを持つテーブルで SELECT DISTINCT が遅いのはなぜですか? 最適化するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。