Postgres Full-Text Search: A search engine in a database

Re:Postgres Full-Text Search: A search engine in a database by ezekg on Tuesday July 27, 2021 @12:05PM (#27974048)

I have a table with over a billion rows and most full-text searches still respond in around a few milliseconds. I think this will depend on a lot of factors, such as proper indexing, and filtering down the dataset as much as possible before performing the full-text ops. I've spent a considerable amount of time on optimizing these queries, thanks to tools like PgMustard [0]. Granted, I do still have a couple slow queries (1-10s query time), but that's likely due to very infrequent access i.e. cold cache.

I will say, if you use open source libraries like pg_search, you are unlikely to ever have performant full-text search. Most full-text queries need to be written by hand to actually utilize indexes, instead of the query-soup that these types of libraries output. (No offense to the maintainers -- it's just how it be when you create a "general" solution.)

[0]: https://pgmustard.com

[ Parent | on HN ]

Re:Postgres Full-Text Search: A search engine in a database by snack-boye on Tuesday July 27, 2021 @01:27PM (#27975066)

Silly question, I'm using pg right now and most of my queries are something like this (in english)

Find me some results in my area that contain these categoryIds and are slotted to start between now and next 10 days.

Since its already quite a filtered set of data, would that mean I should have little issues adding pg text search because with correct indexing and all, it will usually be applied to a small set of data?

Thanks

[ Parent | on HN ]

Re:Postgres Full-Text Search: A search engine in a database by ezekg on Tuesday July 27, 2021 @02:52PM (#27976085)

I'm not a DBA, so I can't say for certain simply due to a gap in my knowledge. But in my experience, it depends on a lot of factors. Sometimes pg will use an index before performing the search ops, other times a subquery is needed. Check out pgmustard and dig into your slow query plans. :)

[ Parent | on HN ]

Re:Postgres Full-Text Search: A search engine in a database by john-shaffer on Tuesday July 27, 2021 @06:24PM (#27978407)

You might be just fine adding an unindexed tsvector column, since you've already filtered down the results.

The GIN indexes for FTS don't really work in conjunction with other indices, which is why https://github.com/postgrespro/rum exists. Luckily, it sounds like you can use your existing indices to filter and let postgres scan for matches on the tsvector. The GIN tsvector indices are quite expensive to build, so don't add one if postgres can't make use of it!

[ Parent | on HN ]

Re:Postgres Full-Text Search: A search engine in a database by ezekg on Wednesday July 28, 2021 @03:50PM (#27988876)

Great read on the pitfalls of GIN indexes: https://iamsafts.com/posts/postgres-gin-performance/ (I'm not the author, but this did happen to me.)

[ Parent | on HN ]

Re:Postgres Full-Text Search: A search engine in a database by kayodelycaon on Tuesday July 27, 2021 @12:53PM (#27974642)

Oh cool. I was right and wrong. Thanks!

[ Parent | on HN ]