Postgresql – Infinite scrolling with full text search in PostgreSQL

full-text-searchpagingpostgresql

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

Best Answer

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.

Related Question