PostgreSQL SELECT DISTINCT クエリが非常に遅いのはなぜですか? パフォーマンスを向上させるにはどうすればよいですか?

PostgreSQL SELECT DISTINCT パフォーマンスのボトルネックと最適化戦略

200 万近くのレコードを含む PostgreSQL テーブルに対する SELECT DISTINCT クエリは、予想外にパフォーマンスが低下します (500 ～ 600 ミリ秒)。 クエリ プランナーは不可解なことに、使用可能なインデックスを利用する代わりに順次スキャンをデフォルトにしており、インデックスを強制しても実行時間は大幅に改善されません。

PostgreSQL でのインデックス スキップ スキャンのエミュレーション

PostgreSQL にはネイティブのインデックス スキップ スキャン機能がないため、再帰共通テーブル式 (CTE) を使用する回避策でその動作を模倣できます。 この CTE は、効率を高めるために product_id のインデックスを利用して、個別の製品 ID を昇順で繰り返し取得します。

<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
)
SELECT * FROM cte;</code>

このアプローチでは、テーブル全体のスキャンと比較してパフォーマンスが大幅に向上します。

代替アプローチ: DISTINCT および DISTINCT ON

一意の製品 ID ごとに行がより均等に分散されているテーブルの場合、標準の DISTINCT または DISTINCT ON キーワードの方が、エミュレートされたインデックス スキップ スキャンより高​​速であることが判明する可能性があります。 そのパフォーマンスはデータの分散に大きく依存します。

今後の改善点: ネイティブ インデックス スキップ スキャン

PostgreSQL の開発には、ネイティブのインデックス スキップ スキャン機能を統合するための継続的な作業が含まれています。 この将来の機能強化により、SELECT DISTINCT クエリのパフォーマンスがさらに最適化されることが約束されています。

以上がPostgreSQL SELECT DISTINCT クエリが非常に遅いのはなぜですか? パフォーマンスを向上させるにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。