I am building a service that allows full-text searches and I would like to paginate the results.
I don't want to use
OFFSET, so I opted for infinite scrolling with the keyset pagination, as outlined here.
My search query looks like this:
SELECT *, ts_rank_cd(t.tsv, plainto_tsquery($1)) AS rank FROM profiles AS t, plainto_tsquery($1) AS q WHERE (tsv @@ q) AND (rank, n, id) < ($2, $3, $4) ORDER BY rank DESC, n DESC, id DESC LIMIT 50
tsv column contains
tsvector objects and it is indexed with GIN. The search results are first ordered by
rank, which is computed by PostgreSQL, and then by another column which is not relevant here and finally by id.
It seems correct to me, but I get this error:
column "rank" does not exist. It works if I replace
rank with its expression in the
WHERE clause. Will that impact performance?
A second question: how can I make keyset pagination work if I wanted to sort
id ASC instead of
id DESC? I cannot split the expression in the
WHERE clause like this
(rank, n) < ($2, $3) AND id > $4 because then I lose the ordering priority (id must be used only to break ties).