Tin: full-text search for Postgres

Discussion (5 Comments)Read Original on HackerNews

mkleczek•about 3 hours 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•about 2 hours ago

And why isn't it a good solution?

mkleczek•about 1 hour 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•32 minutes ago

I see, that's interesting, thanks

rickette•about 1 hour ago

Will this become open source?

Tin: full-text search for Postgres

⚡ Community Insights

Discussion (5 Comments)Read Original on HackerNews