GROUP BY クエリを最適化し、ユーザーごとの最新行を取得する

質問

Postgres 9.2において、ユーザメッセージ（簡略化された形式）に対して以下のようなログテーブルがあります。

CREATE TABLE log (
    log_date DATE,
    user_id  INTEGER,
    payload  INTEGER
);

ユーザーごと、1日ごとに最大1つのレコードが含まれています。300日間、1日あたり約500Kのレコードがあります。ペイロードはユーザーごとに増え続けます（それが重要であれば）。

私は、特定の日付以前の各ユーザーの最新レコードを効率的に取得したいと思います。私のクエリーは次のとおりです。

SELECT user_id, max(log_date), max(payload) 
FROM log 
WHERE log_date <= :mydate 
GROUP BY user_id

となっており、非常に遅いです。私も試しました。

SELECT DISTINCT ON(user_id), log_date, payload
FROM log
WHERE log_date <= :mydate
ORDER BY user_id, log_date DESC;

は同じプランで同じように遅いです。

今のところ、私は単一のインデックスを log(log_date) に一つのインデックスがありますが、あまり役に立ちません。

そして、私は users テーブルがあり、すべてのユーザが含まれています。また、一部のユーザー( payload > :value ).

これを高速化するために使用すべき他のインデックス、または私が望むものを達成するための他の方法はありますか？

どのように解決するのですか？

最高の読み込みパフォーマンスを得るためには マルチカラムインデックス :

CREATE INDEX log_combo_idx
ON log (user_id, log_date DESC NULLS LAST);

を作るには のみをスキャンするようにします。 可能であれば、不要なカラムを追加します。 payload の中に インデックスをカバーする と共に INCLUDE 節を使用します（Postgres 11以降）。

CREATE INDEX log_combo_covering_idx
ON log (user_id, log_date DESC NULLS LAST) INCLUDE (payload);

ご覧ください。

• PostgreSQLのカバーリング・インデックスはJOINカラムの助けになりますか？

古いバージョンのためのフォールバック。

CREATE INDEX log_combo_covering_idx
ON log (user_id, log_date DESC NULLS LAST, payload);

なぜ DESC NULLS LAST ?

• 日付クエリの範囲内の未使用のインデックス

について 少数 列あたり user_id または小さなテーブル DISTINCT ON が一般的に最も速く、最もシンプルです。

• 各 GROUP BY グループで最初の行を選択しますか？

については 多くの 列あたり user_id となります。 インデックススキップスキャン (または ルースインデックススキャン ) の方が（ずっと）効率的です。これはPostgres 12まで実装されていません。 は Postgres 14 のために進行中です。 . しかし、それを効率的にエミュレートする方法はあります。

一般的なテーブル式 require Postgres 8.4+ .

LATERAL は Postgres を必要とします。 9.3+ .

以下のソリューションは Postgres Wiki .

1. ユニークユーザーを持つ別のテーブルがない

個別の users テーブルで 2. の解答は通常よりシンプルで高速です。スキップしてください。

1a. による再帰的CTE LATERAL と結合する

WITH RECURSIVE cte AS (
   (                                -- parentheses required
   SELECT user_id, log_date, payload
   FROM   log
   WHERE  log_date <= :mydate
   ORDER  BY user_id, log_date DESC NULLS LAST
   LIMIT  1
   )
   UNION ALL
   SELECT l.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT l.user_id, l.log_date, l.payload
      FROM   log l
      WHERE  l.user_id > c.user_id  -- lateral reference
      AND    log_date <= :mydate    -- repeat condition
      ORDER  BY l.user_id, l.log_date DESC NULLS LAST
      LIMIT  1
      ) l
   )
TABLE  cte
ORDER  BY user_id;

これは任意のカラムを取得するためのシンプルな方法で、おそらく現在のPostgresでは最適な方法です。より詳しい説明は 2a. で説明します。

1b. 相関サブクエリを使用した再帰的CTE

WITH RECURSIVE cte AS (
   (                                           -- parentheses required
   SELECT l AS my_row                          -- whole row
   FROM   log l
   WHERE  log_date <= :mydate
   ORDER  BY user_id, log_date DESC NULLS LAST
   LIMIT  1
   )
   UNION ALL
   SELECT (SELECT l                            -- whole row
           FROM   log l
           WHERE  l.user_id > (c.my_row).user_id
           AND    l.log_date <= :mydate        -- repeat condition
           ORDER  BY l.user_id, l.log_date DESC NULLS LAST
           LIMIT  1)
   FROM   cte c
   WHERE  (c.my_row).user_id IS NOT NULL       -- note parentheses
   )
SELECT (my_row).*                              -- decompose row
FROM   cte
WHERE  (my_row).user_id IS NOT NULL
ORDER  BY (my_row).user_id;

を取得するのに便利な 単一カラム または 行全体 . この例では、テーブルの行全体の型を使用しています。他のバリアントも可能である。

前の反復で行が見つかったと主張するために、単一のNOT NULLカラム（主キーのような）をテストします。

このクエリに関するより詳しい説明は、以下の2b.章にあります。

関連する

• 行ごとに最後のN個の関連行を問い合わせる
• PostgreSQLで、あるカラムでソートしながら、別のカラムでGROUP BYする。

2. セパレートで users テーブル

テーブルのレイアウトは、関連する1つの行が正確に存在する限り、ほとんど重要ではありません。 user_id が保証されている限り、テーブルのレイアウトはほとんど問題になりません。例

CREATE TABLE users (
   user_id  serial PRIMARY KEY
 , username text NOT NULL
);

理想的には、テーブルが物理的に同期してソートされ log テーブルと同期していることが理想的です。参照してください。

• Postgresのタイムスタンプクエリの範囲を最適化する

あるいは、ほとんど問題にならないほど小さい（低カーディナリティ）。そうでない場合は、クエリで行を並べ替えると、パフォーマンスをさらに最適化することができます。 Gang Liangの追加を参照してください。 もし物理的なソート順が users テーブルのインデックスと一致する場合 log のように、これは無関係かもしれません。

2a. LATERAL 加わる

SELECT u.user_id, l.log_date, l.payload
FROM   users u
CROSS  JOIN LATERAL (
   SELECT l.log_date, l.payload
   FROM   log l
   WHERE  l.user_id = u.user_id         -- lateral reference
   AND    l.log_date <= :mydate
   ORDER  BY l.log_date DESC NULLS LAST
   LIMIT  1
   ) l;

JOIN LATERAL は、前の FROM の項目を参照することができます。参照してください。

• PostgreSQLにおけるLATERAL JOINとサブクエリの違いは何ですか？

ユーザーごとに1つのインデックス（-のみ）ルックアップの結果になります。

で見つからないユーザーについては、行を返さない。 users テーブルにない行を返します。通常 外部キー 制約によって参照整合性が排除されます。

また、ユーザーに関する行は log - に一致しないユーザーの行もありません。これらのユーザーを結果に残すには LEFT JOIN LATERAL ... ON true の代わりに CROSS JOIN LATERAL :

• 配列の引数を持つセットリターン関数を複数回呼び出す

使用方法 LIMIT n の代わりに LIMIT 1 を取得するために 行以上 (全てではありません)。

事実上、これらはすべて同じことをします。

JOIN LATERAL ... ON true
CROSS JOIN LATERAL ...
, LATERAL ...

最後のものは優先順位が低いですが 明示的な JOIN はカンマの前に結合します。この微妙な違いは、結合テーブルが多くなると問題になることがあります。参照してください。

• PostgresクエリのテーブルのFROM句エントリへの参照が無効です" 。

2b. 相関するサブクエリ

を取得するのに良い選択です。 単一カラム から 単一行 . コード例です。

• グループ単位の最大クエリを最適化する

同じことが 複数カラム も可能ですが、よりスマートさが必要です。

CREATE TEMP TABLE combo (log_date date, payload int);

SELECT user_id, (combo1).*              -- note parentheses
FROM (
   SELECT u.user_id
        , (SELECT (l.log_date, l.payload)::combo
           FROM   log l
           WHERE  l.user_id = u.user_id
           AND    l.log_date <= :mydate
           ORDER  BY l.log_date DESC NULLS LAST
           LIMIT  1) AS combo1
   FROM   users u
   ) sub;

のように LEFT JOIN LATERAL 上記のように、このバリアントには すべて のエントリがなくても log . この場合 NULL に対して combo1 で簡単にフィルタリングできます。 WHERE 節で簡単にフィルタリングできます。

_{補足: 外側のクエリでは、サブクエリが行を見つけられなかったのか、それともすべてのカラムの値が NULL だったのかを区別することはできません - 同じ結果です。同じ結果になります。 NOT NULL カラムが必要です。}

相関のあるサブクエリは 単一の値 . 複数のカラムを複合型にラップすることができます。しかし、後でそれを分解するために、Postgresはよく知られた複合型を要求します。匿名レコードは列定義リストを提供することでのみ分解することができます。

既存のテーブルの行型のような登録された型を使用します。または、複合型を明示的に（そして永久に）登録するために CREATE TYPE . または、一時的に行型を登録するために一時テーブルを作成します（セッションの終了時に自動的に削除されます）。キャスト構文。 (log_date, payload)::combo

最後に、私たちは combo1 を同じ問い合わせレベルで分解したくないのです。クエリプランナの弱点により、これは各列に対して一度だけ副問い合わせを評価することになります（Postgres 12ではまだそうなっています）。その代わりに、副問い合わせを作成し、外側の問い合わせの中で分解してください。

関連する

• グループごとの最初と最後の行から値を取得する

100kのログエントリーと1kのユーザーで4つのクエリすべてをデモしています。

db<>fiddle ここで - 11ページ

<サブ 古い sqlfiddle