mkleczek
a day ago
I am still looking for a good solution to the following use case:
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.
izietto
21 hours ago
And why isn't it a good solution?
mkleczek
20 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.
izietto
20 hours ago
I see, that's interesting, thanks