I implemented keyset pagination on these tables
CREATE TABLE profiles (
id serial primary key,
username varchar(255),
followers bigint,
tsv tsvector
);
CREATE TABLE profile_categories (
username varchar(255),
category varchar(255),
primary key (username, category)
);
with the following query:
SELECT profiles.*, categories,
ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers) AS rank
FROM profiles
JOIN plainto_tsquery($1) AS q ON tsv @@ q
LEFT OUTER JOIN profile_categories ON profiles.username = profiles_categories.username
WHERE followers IS NOT NULL AND
followers > 0 AND
(ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers), -id) < ($2, $3)
ORDER BY (
ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers),
-id
) DESC
LIMIT 50;
The idea is to use ts_rank_cd
to rank documents with full-text search queries, and to scale the rank by the logarithm of the followers.
The problem is that when I query for the second page by supplying the last rank and id ($2
and $3
) of the previous page, I still get the last result from the previous page as the first result (so I effectively get only 49 new results)! I have no idea how this is possible since I'm using strict inequality operators.
EDIT Even if I cast the ranks to decimal
the same problem persists -- I get duplicates results at the end of one page and at the start of the next:
SELECT profiles.*, categories,
(ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers))::decimal AS rank
FROM profiles
JOIN plainto_tsquery($1) AS q ON tsv @@ q
WHERE followers IS NOT NULL AND
followers > 0 AND
((ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers))::decimal, -id) < ($2, $3)
ORDER BY (
(ts_rank_cd(tsv, plainto_tsquery($1)) * log(followers))::decimal,
-id
) DESC
LIMIT 50