5 ms·

Optimizing Postgres text search with trigrams

139 points by petercooper 5y ago

fabian2k 5y ago
That was a very interesting article, and I have not seen such a thorough text on this specific kind of searches in Postgres so far. What I found interesting are the comments on using GiST instead of GIN, turns out I had some misconceptions here. For the text search feature the Postgres documentation recommends GIN indexes, I somehow missed that for Trigram indexes the GiST version is more powerful. I'll have to play around with this at some time, but I am wondering how GiST and GIN compare if I don't order by similarity. I was also surprised I never heard about the siglen option, but it turns out this is new since Postgres 13. It looks like this has a big effect and is a useful knob to tune this.
- kleebeesh 5y ago
  Thanks for the feedback -- sorry I missed this a couple days ago! Had no clue it got posted to HN. FWIW, I've found GIN is a bit faster if you're just looking to filter. IIRC it was maybe 10-15% faster for the particular use-case I was looking at. So, worth a try, but don't expect a 10x improvement.
aaronmu 5y ago
Learned a bunch, thanks!
radiowave 5y ago
I went through much of this a few weeks ago, and this article would have saved me quite a bit of time. Like others here, siglen is new to me, so I'll have to have a play around with that. One thing that bothered me was how cumbersome the queries become once you start having to concatenate and coalesce a whole bunch of columns together. Postgres has a variadic function called "concat_ws" which ought to be useful here, especially as it implicitly coalesces NULL inputs to empty string. However, it's not declared to be immutable, so can't be used in an expression index. The solution (from Erwin Brandstetter's answer on Stack Overflow) is to use an immutable variant of this, constrained to only handle data of type "text". https://stackoverflow.com/questions/54372666/create-an-immutable-clone-of-concat-ws https://stackoverflow.com/questions/54372666/create-an-immut... With this immutable concat function in place, and a trigram expression index making use of it, the WHERE clauses become a lot more concise, for example, the clause in the final query of the article reduces to just: WHERE immutable_concat_ws(' ', asin, reviewer_id, reviewer_name, summary) ilike '%' || input.q || '%'
- kleebeesh 5y ago
  Yeah it's a shame the concat_ws function doesn't quite work here. I've had success using Slick, an ORM-ish Scala library, to abstract away this tedious concatenation in app code.
jfbaro 5y ago
Thanks for sharing this! Great stuff. Also, there is this presentation that might be complementary to the one shared here. http://matheusoliveira.s3-website-us-east-1.amazonaws.com/presentations/pgconfeu-20191018/#/2/4 http://matheusoliveira.s3-website-us-east-1.amazonaws.com/pr... The presentation above is really good for people interested in FTS on PostgreSQL. The company behind this presentation does tens of thousands of sophisticated searches per second. And it's 100% powered by PostgreSQL. Thanks
deleted 5y ago
[deleted]
truth_seeker 5y ago
Kudos to author .... Extremely well written article.
thom 5y ago
This is great, hadn’t heard about siglen! For the solution at the end, you’ll often find yourself wanting to query across multiple tables instead of multiple columns in a single table, in which case creating a materialised view with a single search term column and a GIST index is the best I’ve come up with. Obviously you then have a refresh to do instead of the expression being maintained as part of the index. But no matter what, if your search isn’t fast, you’re probably doing something wrong. Postgres is great for this stuff (obviously for longer documents and fuzzier searches we can argue about quality).
- Grollicus 5y ago
  I'm currently thinking about the multi table search problem and lean towards updating the index table in the ORM (save method of the corresponding Django models in this case). Postgres doesn't seem to support incremental updates of materialized views yet, and always recomputing the whole table just because some few rows changed seems very much overkill to me. That it integrates better into the rest of the app and makes configuring the indexed columns easier is just a bonus. Not that Postgres probably wouldn't handle the full table refresh effortlessly as it generally does - at least at my scale. But that feels kind of icky to me and I'm surprised there doesn't seem to be a better solution for this yet - everyone building a search function for their site must run into this and I'd expect it to scale terribly at dimensions startups generally aim at.
  - crgwbr 5y ago
    I’d advise just using materialized views. Yes, they don’t support incremental updates. But anything homemade will eventually be buggy somewhere and cause the index to get out of date in unexpected and probably unmonitored ways.
  - SigmundA 5y ago
    Triggers would be another less fragile way to do this.
  - thom 5y ago
    As long as there's a key on the materialized view you can rebuild it concurrently with no locks, and if you remember to vacuum there's no particular overhead to doing this regularly. Smaller more targeted updates seem good to me though if you want things instantly in the index.

139 points by petercooper 5y ago

That was a very interesting article, and I have not seen such a thorough text on this specific kind of searches in Postgres so far. What I found interesting are the comments on using GiST instead of GIN, turns out I had some misconceptions here. For the text search feature the Postgres documentation recommends GIN indexes, I somehow missed that for Trigram indexes the GiST version is more powerful. I'll have to play around with this at some time, but I am wondering how GiST and GIN compare if I don't order by similarity. I was also surprised I never heard about the siglen option, but it turns out this is new since Postgres 13. It looks like this has a big effect and is a useful knob to tune this.

kleebeesh 5y ago
Thanks for the feedback -- sorry I missed this a couple days ago! Had no clue it got posted to HN. FWIW, I've found GIN is a bit faster if you're just looking to filter. IIRC it was maybe 10-15% faster for the particular use-case I was looking at. So, worth a try, but don't expect a 10x improvement.

aaronmu 5y ago

Learned a bunch, thanks!

radiowave 5y ago

I went through much of this a few weeks ago, and this article would have saved me quite a bit of time. Like others here, siglen is new to me, so I'll have to have a play around with that. One thing that bothered me was how cumbersome the queries become once you start having to concatenate and coalesce a whole bunch of columns together. Postgres has a variadic function called "concat_ws" which ought to be useful here, especially as it implicitly coalesces NULL inputs to empty string. However, it's not declared to be immutable, so can't be used in an expression index. The solution (from Erwin Brandstetter's answer on Stack Overflow) is to use an immutable variant of this, constrained to only handle data of type "text". https://stackoverflow.com/questions/54372666/create-an-immutable-clone-of-concat-ws https://stackoverflow.com/questions/54372666/create-an-immut... With this immutable concat function in place, and a trigram expression index making use of it, the WHERE clauses become a lot more concise, for example, the clause in the final query of the article reduces to just: WHERE immutable_concat_ws(' ', asin, reviewer_id, reviewer_name, summary) ilike '%' || input.q || '%'

kleebeesh 5y ago
Yeah it's a shame the concat_ws function doesn't quite work here. I've had success using Slick, an ORM-ish Scala library, to abstract away this tedious concatenation in app code.

jfbaro 5y ago

Thanks for sharing this! Great stuff. Also, there is this presentation that might be complementary to the one shared here. http://matheusoliveira.s3-website-us-east-1.amazonaws.com/presentations/pgconfeu-20191018/#/2/4 http://matheusoliveira.s3-website-us-east-1.amazonaws.com/pr... The presentation above is really good for people interested in FTS on PostgreSQL. The company behind this presentation does tens of thousands of sophisticated searches per second. And it's 100% powered by PostgreSQL. Thanks

deleted 5y ago

[deleted]

truth_seeker 5y ago

Kudos to author .... Extremely well written article.

thom 5y ago

This is great, hadn’t heard about siglen! For the solution at the end, you’ll often find yourself wanting to query across multiple tables instead of multiple columns in a single table, in which case creating a materialised view with a single search term column and a GIST index is the best I’ve come up with. Obviously you then have a refresh to do instead of the expression being maintained as part of the index. But no matter what, if your search isn’t fast, you’re probably doing something wrong. Postgres is great for this stuff (obviously for longer documents and fuzzier searches we can argue about quality).

Grollicus 5y ago
I'm currently thinking about the multi table search problem and lean towards updating the index table in the ORM (save method of the corresponding Django models in this case). Postgres doesn't seem to support incremental updates of materialized views yet, and always recomputing the whole table just because some few rows changed seems very much overkill to me. That it integrates better into the rest of the app and makes configuring the indexed columns easier is just a bonus. Not that Postgres probably wouldn't handle the full table refresh effortlessly as it generally does - at least at my scale. But that feels kind of icky to me and I'm surprised there doesn't seem to be a better solution for this yet - everyone building a search function for their site must run into this and I'd expect it to scale terribly at dimensions startups generally aim at.
- crgwbr 5y ago
  I’d advise just using materialized views. Yes, they don’t support incremental updates. But anything homemade will eventually be buggy somewhere and cause the index to get out of date in unexpected and probably unmonitored ways.
- SigmundA 5y ago
  Triggers would be another less fragile way to do this.
- thom 5y ago
  As long as there's a key on the materialized view you can rebuild it concurrently with no locks, and if you remember to vacuum there's no particular overhead to doing this regularly. Smaller more targeted updates seem good to me though if you want things instantly in the index.