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 12 hours ago [-]
And why isn't it a good solution?
mkleczek 11 hours 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).
EDIT: or I misunderstood the question. GIST+trgm works but its search capabilities are limited, it does not handle "order by column" so requires weird workarounds, and it is not very efficient.
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.
EDIT: or I misunderstood the question. GIST+trgm works but its search capabilities are limited, it does not handle "order by column" so requires weird workarounds, and it is not very efficient.