select * from entry where user = ? and entry_date <= ? and entry_text matches ? order by entry_date desc limit ?;
(eg. page through the list of financial transactions with description matching some criteria, latest first)
So far only GIST with pg_trgm handles that without the need to sort large result sets.
izietto 1 hours ago [-]
And why isn't it a good solution?
mkleczek 5 minutes ago [-]
Because it is not capable of ordered index scan that handles both filtering (by multiple conditions) and "order by" - text search returns potentially a very large set of results that must be sorted and then truncated (to satisfy limit).
Rendered at 09:12:51 GMT+0000 (Coordinated Universal Time) with Vercel.
create table entry (id, user, entry_date, entry_text);
select * from entry where user = ? and entry_date <= ? and entry_text matches ? order by entry_date desc limit ?;
(eg. page through the list of financial transactions with description matching some criteria, latest first)
So far only GIST with pg_trgm handles that without the need to sort large result sets.