過剰なテーブル スキャンを避けるために、Postgres でグループごとの最大クエリ数を最適化するにはどうすればよいでしょうか?

グループごとの最大クエリの最適化

問題のクエリは、レコード テーブル内の各一意の option_id の最大 ID 値を持つ行を取得することを目的としています。 。ただし、現在の実装では、過度のテーブル スキャンによる非効率性が見られます。

現在のクエリが非効率である理由

問題は、行を識別するために使用されるネストされたループ結合にあります。最大 ID 値。この結合では、Postgres がレコード テーブル全体を数回スキャンする必要があり、実行時間とリソース消費量が増加します。

ルックアップ テーブルを使用した代替アプローチ

このクエリを最適化するにはの場合は、別のアプローチをお勧めします。それは、オプション ID をレコード テーブル内の最大 ID にマップするオプションと呼ばれる別のルックアップ テーブルを作成することです。 records.option_id と options.option_id の間に外部キー制約を導入すると、参照整合性が保証されます。

CREATE TABLE options (
  option_id int PRIMARY KEY,
  option text UNIQUE NOT NULL
);

INSERT INTO options (option_id, option)
SELECT DISTINCT option_id, 'option' || option_id
FROM records;

相関サブクエリを使用した最適化されたクエリ

オプション テーブルが設定されている場合、元のクエリは、option_id に基づいて 2 つのテーブルを効率的に結合する相関サブクエリを使用して書き換えることができます。

SELECT o.option_id, (SELECT MAX(id) FROM records WHERE option_id = o.option_id) AS max_id
FROM options o
ORDER BY o.option_id;

代替アプローチの利点

この代替アプローチには、いくつかの利点があります。

• アクセスするだけでテーブル スキャンが削減されます。オプション テーブル内の関連する行。
• 効率的な相関関係を利用します。レコードから最大 ID 値を取得するためのサブクエリ。
• 外部キー制約により参照整合性を維持します。

追加の最適化

(option_id, id DESC NULLS LAST) のレコード テーブルへのインデックスを許可することで、パフォーマンスをさらに向上させることができます。 Postgres はサブクエリのインデックスのみのスキャンを実行します。

以上が過剰なテーブル スキャンを避けるために、Postgres でグループごとの最大クエリ数を最適化するにはどうすればよいでしょうか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。