5 ms·

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 categoryI

• ezekg 5y ago
  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. :)
• john-shaffer 5y ago
  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 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!
  • ezekg 5y ago
    Great read on the pitfalls of GIN indexes: https://iamsafts.com/posts/postgres-gin-performance/ https://iamsafts.com/posts/postgres-gin-performance/ (I'm not the author, but this did happen to me.)