https://soudai.hatenablog.com/entry/2024/12/10/115848

履歴テーブルから各ユーザの最新のデータを1件ずつ取ってきたい場合、PostgreSQLの場合は次のように書いた方が高速な場合がある。

SELECT
  latest.*
FROM
  users,
  LATERAL (
    SELECT
      *
    FROM
      history
    WHERE
      history.user_id = users.user_id
    ORDER BY
      created_at DESC
    LIMIT
      1
  ) AS latest;

ウィンドウ関数を使う場合やDISTINCT ONを使う場合、インデックスがあっても線形スキャンとなる。

一方、前記のクエリの場合(あるいは入れ子ループでインデックスを引くような同等のクエリの場合)、インデックスを使って最新の行のみにアクセスするため、最新以外の行が多い場合は高速になる。

さらに複雑な条件が必要な場合は再帰CTEを使った方法が使える: https://wiki.postgresql.org/wiki/Loose_indexscan

多くの場合は最新データを別テーブルにする方法でよいんだけど、任意の時点における最新データを取得したい場合や、何らかの理由で別テーブルを作りたくない場合はこの手法が使える。

インデックスは(user_id DESC, created_at DESC)で作っておくとよい。

いずれにしろ実行計画を見て、さらに実データに近いデータでベンチマークを取ってちゃんと速くなっているか確認する必要がある。