postgresql - 如何优化从 JOIN 获取的集合中选择一个随机行

Question

英文查询:

• 从 stuff 中随机检索一行。
• 行未在完成中提及。
• 行属于得分最高*的 friend 。
  *如果没有找到属于得分最高的 friend 的行，则取下一个 friend ，依此类推。

我当前的查询需要很长时间才能完成，因为它随机排序所有东西，而它应该随机排序一批又一批。

这是一个 sqlfiddle带有表格和数据。

我的查询:

WITH ordered_friends AS (SELECT *
                         FROM friends
                         ORDER BY score DESC)
SELECT s.stuff_id
FROM ordered_friends
  INNER JOIN (SELECT *
              FROM stuff
              ORDER BY random()) AS s ON s.owner = ordered_friends.friend
WHERE NOT EXISTS(
    SELECT 1
    FROM done
    WHERE done.me = 42
          AND done.friend = s.owner
          AND done.stuff_id = s.stuff_id
)
-- but it should keep the order of ordered_friends (score)
-- it does not have to reorder all stuff
-- one batch for each friend is enough until a satisfying row is found.    
LIMIT 1;

Answer 1

这个怎么样？

SELECT s.stuff_id
FROM friends
   CROSS JOIN LATERAL (SELECT stuff_id
                       FROM stuff
                       WHERE stuff.owner = friends.friend
                         AND NOT EXISTS(SELECT 1
                                        FROM done
                                        WHERE done.me = 42
                                        AND done.friend = stuff.owner
                                        AND done.stuff_id = stuff.stuff_id
                                       )
                       ORDER BY random()
                       LIMIT 1
                      ) s
ORDER BY friends.score DESC
LIMIT 1;

以下索引会使它变快:

CREATE INDEX ON friends(score);          -- for sorting
CREATE INDEX ON stuff(owner);            -- for the nested loop
CREATE INDEX ON done(stuff_id, friend);  -- for NOT EXISTS