postgresql - 在 postgresql 上使用全文搜索进行键集分页

Question

我有一个包含 +100,000 条记录的“用户”表。我想开始使用键集分页来加快获取记录的过程。

以下查询有效。此查询获取 第二个记录集的页面(从 user_id: 1001 开始，直到 user_id: 2000 为止)。

SELECT
    user_id,
    username
FROM
    users
WHERE
    user_id > 1000
ORDER BY
    user_id ASC
LIMIT
    1000

问题是:我不想对 user_id 上的记录进行排序。我有一个名为“tokens”的列，它是一个 to_tsvector 列。我想对记录集执行全文搜索并按排名对用户进行排序。新查询:

SELECT
    user_id,
    username,
    to_tsrank(tokens, plainto_tsquery('search query')) AS rank
FROM
    users
WHERE
    tokens @@ plainto_tsquery('search query')

如何在第二个查询上应用键集分页，以便按排名而不是 user_id 对结果进行排序？

重要提示:

我试过这个，但这不起作用!

SELECT
    user_id,
    username,
    to_tsrank(tokens, plainto_tsquery('search query')) AS rank
FROM
    users
WHERE
    tokens @@ plainto_tsquery('search query')
AND
    to_tsrank(tokens, plainto_tsquery('search query')) < $1 // $1 = last fetched rank
ORDER BY
    rank DESC
LIMIT
    1000

假设，当结果按“等级”排序时，第 1,000 个结果的等级为 0.5。 $1(最后获取的排名)将是 0.5，所以我会选择排名 < 0.5 的所有结果。问题是:有些结果可能有 相同 秩。因此，如果第 1,001 条记录也是 rank = 0.5，则不会获取它，因为我在查询中说 rank < 0.5 .我也不能说 rank <= 0.5因为这将再次获取 rank = 0.5 的先前结果。

有谁知道这个问题的解决方案？

Answer 1

您必须提供完全确定的 ORDER BY。假设 user_id 是唯一的:

ORDER BY rank desc, user_id

那么你的 WHERE 将包括:

AND (rank < :last_rank or (rank = :last_rank and user_id > :last_user_id))

但这不会有效率，所以你不妨只做 OFFSET。

更好的是，根本不要这样做。没有人会现实地阅读 1000 个结果，并认为“你知道，我还想再做几次”。唯一会这样做的是网络刮板，而网络刮板会这样做的唯一原因是因为它是您提供的唯一方法。只需让他们设置一个任意高的 LIMIT，并且不提供分页。