各ユーザーの最新の行を効率的に取得するには、GROUP BY クエリを最適化するにはどうすればよいですか?

ユーザーごとに最新の行を取得するための GROUP BY クエリの最適化戦略

log_date、user_id、および payload として構造化されたユーザー メッセージを含むテーブルが与えられたとします。 、タスクは、特定のユーザーの前に各ユーザーの最新レコードを効率的に取得することです。 date.

複数列インデックス

読み取りパフォーマンスを向上させるには、user_id と log_date に複数列インデックスを作成します。

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

Index-カバーインデックスを使用してスキャンのみ

インデックスのみのスキャンでは、ペイロード列を含むカバー インデックスを定義します。

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

SELECT DISTINCT ON()

小さいテーブルまたは user_id ごとに数行の場合、 SELECT DISTINCT ON() を使用すると、効率的:

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

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

user_id ごとに多くの行がある大規模なテーブルの場合は、LATERAL 結合を使用した再帰 CTE を使用してインデックス スキップ スキャンをエミュレートすることを検討してください。

WITH RECURSIVE cte AS (
   (
   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;

ユーザーを分けるテーブル

別の users テーブルが存在する場合は、次のような単純化されたソリューションが可能です。

LATERAL Join

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;

相関サブクエリ

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;

これら最適化では、インデックスの利用、スキップ スキャンのエミュレーション、ユーザー情報用の別のテーブルの利用により、クエリのパフォーマンスが向上します。

以上が各ユーザーの最新の行を効率的に取得するには、GROUP BY クエリを最適化するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。