Fast Search Using PostgreSQL Trigram Indexes
about.gitlab.com
Fast Search Using PostgreSQL Trigram Indexes
1–10 of 33 posts
Re: Fast Search Using PostgreSQL Trigram Indexes
#2Hi! Author in question here, if anybody would like to know more about this particular feature I'll be happy to answer any questions. The changes discussed in this article are currently available on GitLab.com, 8.6 will be released on the 22nd as usual.
Re: Fast Search Using PostgreSQL Trigram Indexes
#3A project I worked on used trigram indexes a few years ago to solve the autocomplete problem. They are AMAZING - they basically allow you to run LIKE queries with wildcards anywhere in the string (as opposed to suffix-only-wildcards) against an index.
An autocomplete search for e.g. "rub rai" becomes the following SQL query:
select * from topics where name ilike "%rub%rai%";
create extension pg_trgm;
create index topic_name_gin on topics using gin (name gin_trgm_ops);Re: Fast Search Using PostgreSQL Trigram Indexes
#4 Planning time: 0.143 ms
Execution time: 158.008 ms
Would take about 10 ms with elasticsearch if data was structured properly.
Re: Fast Search Using PostgreSQL Trigram Indexes
#5I thought this was a great article, and trigram indexes are definitely an amazing tool to have in the arsenal.
I think PostgreSQL fulltext search (with tsvector, tsquery etc.) is given an unfairly bad rap though. I'm not sure that "full text search works best when the text vectors are stored in physical columns with an index" is true - in my experience there's no performance penalty to just indexing the tsvector expression - no need to worry about additional columns or triggers.
I also think the assertion that the key problem is that "full text search is that words are broken up according to the rules defined by the language of the text" is very context-dependent. In many situations that's the most awesome feature of fulltext search. Usually when I search for "cat" I'm not interested in results for catacombs or categories, but when I search for restaurants, results matching restaurant (singular) are relevant too.
I definitely see that for a use-case like GitLab's, where the data includes code, full text search's stemming would be a hindrance rather than a help.
Re: Fast Search Using PostgreSQL Trigram Indexes
#6I thought this was a great article, and trigram indexes are definitely an amazing tool to have in the arsenal. I think PostgreSQL fulltext search (with tsvector, tsquery etc.) is given an unfairly bad rap though. I'm not sure that "full text search works best when the text vectors are stored in physical columns with an index" is true - in my experience there's no performance penalty to just indexing the tsvector expr…
One of the perks of using columns is that you can setup multiple columns with different dictionaries and specify which one you're searching against.
I'm not sure how easy it would be to ensure that the index would get picked up if searching on a weighted field with multiple columns specified or if it would require that the program know in advance what those columns were. Would the order matter for the index to be used?
Additionally, a column can also easily included relevant search data from related records in other tables or even datasources if desired.
Re: Fast Search Using PostgreSQL Trigram Indexes
#7Hi! Author in question here, if anybody would like to know more about this particular feature I'll be happy to answer any questions. The changes discussed in this article are currently available on GitLab.com, 8.6 will be released on the 22nd as usual.
Why bother supporting both MySQL and Postgres for the backend? Wouldn't picking one (~cough~ Postgres) simplify things?
From my experience, attempting to support multiple backend stores leads to either crippled functionality, i.e. lowest common denominator CRUD usage, or lots of messy if/then logic to use DB specific features.
Re: Fast Search Using PostgreSQL Trigram Indexes
#8I thought this was a great article, and trigram indexes are definitely an amazing tool to have in the arsenal. I think PostgreSQL fulltext search (with tsvector, tsquery etc.) is given an unfairly bad rap though. I'm not sure that "full text search works best when the text vectors are stored in physical columns with an index" is true - in my experience there's no performance penalty to just indexing the tsvector expr…
> I'm not sure that "full text search works best when the text vectors are stored in physical columns with an index" is true
It's mostly based on my past experiences. At my previous gig we indexed quite a bit of data using PostgreSQL's full text search system and we noticed significantly improved performance when using physical columns containing text vectors over just using GIN indexes. It's been a while so the details are a bit fuzzy.
Re: Fast Search Using PostgreSQL Trigram Indexes
#9Hi! Author in question here, if anybody would like to know more about this particular feature I'll be happy to answer any questions. The changes discussed in this article are currently available on GitLab.com, 8.6 will be released on the 22nd as usual.
Why bother supporting both MySQL and Postgres for the backend? Wouldn't picking one ( ~cough~ Postgres ) simplify things? From my experience, attempting to support multiple backend stores leads to either crippled functionality, i.e. lowest common denominator CRUD usage, or lots of messy if/then logic to use DB specific features.
Personally I'd love to drop support for MySQL but we have a few too many organizations that only run MySQL _and_ want to use GitLab. It would be a waste if said organizations wouldn't be able to use GitLab.
The approach I tend to go with is to make queries perform as good as possible on PostgreSQL without using too many PostgreSQL specific bits (which would make supporting both DBs more difficult), while making sure they still perform good enough on MySQL.
Re: Fast Search Using PostgreSQL Trigram Indexes
#10I thought this was a great article, and trigram indexes are definitely an amazing tool to have in the arsenal. I think PostgreSQL fulltext search (with tsvector, tsquery etc.) is given an unfairly bad rap though. I'm not sure that "full text search works best when the text vectors are stored in physical columns with an index" is true - in my experience there's no performance penalty to just indexing the tsvector expr…
One of the perks of using columns is that you can setup multiple columns with different dictionaries and specify which one you're searching against. I'm not sure how easy it would be to ensure that the index would get picked up if searching on a weighted field with multiple columns specified or if it would require that the program know in advance what those columns were. Would the order matter for the index to be use…
There's nothing stopping you indexing multiple columns with different dictionaries either :). And indexing works beautifully with multiple weighted columns.
The related records point is true though - indexes don't help if you're trying to implement multi-table search. Although then I'd argue a materialized view (refreshed with triggers) might be better than putting more columns into one of the data tables.