2

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

The 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).

2

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?

Yes it will in as much as without that change the query does not work. So that's a positive effect, however rank will not be computed twice so there is no penalty for that.

Nevertheless the whole table needs to be scanned to generate the rank column before the order-by can be done, so efficiency is about the same as offset.

A second question: how can I make keyset pagination work if I wanted to sort id ASC instead of id DESC

If it's a number type, use -id instead. else you'd have to expand the tuple comparison out into a big messy expression.

  • > efficiency is about the same as OFFSET Ok, it makes sense. So is this the best one can do with full-text search? – rubik Jun 11 '18 at 10:07
  • yeah, keyset pagination is only a win where there's an index on the (first) term(s) of the keyset. with full text search that's not going to happen. – Jasen Jun 11 '18 at 10:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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