Get relevant and fast results from your Redmine search.
Redmine supports a lot of different databases with very different feature sets. Naturally such flexibility comes at a price - only features that are present in all of these databases can be used, or the code must branch out to handle each database system separately. This would be prone to errors and expensive in terms of development time, which is why most of the time Redmine goes with what DB features just work everywhere and ignores the rest.
But that does not mean you have to suffer! PostgreSQL (and to a lesser degree also recent versions of MySQL) has full text search capabilities built in that can not only dramatically speed up your searches, but also deliver better results in a meaningful order.
For the reasons stated above, Redmine does simple
LIKE queries and therefore has no means to sort search results by relevance.
Instead, results are sorted by the date of creation / last modification.
Especially in large setups with potentially big result sets this leads to a very bad user experience because older items tend to never show up on the first or second result pages.
With this plugin, Redmine will rank more relevant hits higher, so an issue with a title matching the user’s query will be listed before an issue where just the description matches, even if the latter has a more recent modification date.
With it’s fine grained permissions and lots of different entities to search, Redmine search is a complicated beast.
A single search easily results in multiple SQL queries, each using
LIKE statements, often against multiple columns, to do the matching. This in turn leads to multiple full table scans, which kill search performance.
This plugin makes Redmine use PostgreSQL’s full text search capabilities, replacing
LIKE search with native queries against the inverted index1 which is kept in a separate table.
Language aware preprocessing of text before it is indexed makes sure that derived words like satisfies and satisfy are treated as the same word so a search for either one finds any document containing one or the other. This also handles plural forms.
You will need a recent version of Redmine. It might run with 3.0, but at the moment I work with and test only against Redmine 3.1 and trunk.
Any PostgreSQL 9.x should work, I develop with 9.1 and TravisCI tests run against 9.4.
Follow the generic Redmine plugin installation instructions, but with a twist for Redmine installations with mostly non-english content:
Before running the plugin migrations, set the
language environment variable to the language your Redmine content (mostly) is in. The
setup_tsearch migration uses this to create a matching text search configuration in your Redmine database.
This directly influences search results quality so don’t just skip this (unless your language would be english, this is the default used by the migration).
To find out which languages are supported by your PostgreSQL installation, run
\dF in a shell:
So if your Redmine installation has mostly German text, you would run
after unpacking the plugin to
After running the migrations, make sure to index all your existing content by running
In case you want to learn more about the internals of PostgreSQL full text search - I found these two articles quite helpful:
Please report any issues not mentioned here on Github.
Search on custom fields and journal entries is done separately from search in issues - therefore a search for all words of the query
foo bar will not find issues with
foo in the description and
bar in a journal entry or custom field.
The reason is the way Redmine search works on a fundamental level.
To make things a little better this plugin indexes the issue’s subject along with each journal, so if you have
foo in the subject and
bar in a journal entry you will find the issue.
Copyright (C) 2015 Jens Krämer
The Postgresql full text search plugin for Redmine is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.