2

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

2 Answers 2

1

Floating point numbers are subject to representation/rounding errors. When translated to the client and back again, it is unlikely to be exactly equal to the original.

2
  • That's what I was suspecting. How could I get around the problem? Translating to strings or using more precision are alternatives that come to mind. But I don't think I can do the latter since ts_rank_cd is a built-in that returns a real number.
    – rubik
    Jul 10, 2018 at 1:44
  • I tried casting ranks to decimals (they are transmitted as strings -- see the edited question for the query) but the problem persists.
    – rubik
    Jul 10, 2018 at 2:12
0

I solved the issue by explicitly excluding the id corresponding to the last seen id (the addition is highlighted in bold):

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
    id <> $3
    ((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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.