https://www.redmine.org/https://www.redmine.org/favicon.ico?16793021292013-12-30T04:48:44ZRedmineRedmine - Defect #15781: Customfields have a noticable impact on search performance due to slow database COUNThttps://www.redmine.org/issues/15781?journal_id=540372013-12-30T04:48:44ZToshi MARUYAMA
<ul><li><strong>Category</strong> set to <i>Custom fields</i></li></ul> Redmine - Defect #15781: Customfields have a noticable impact on search performance due to slow database COUNThttps://www.redmine.org/issues/15781?journal_id=540392013-12-30T04:49:08ZToshi MARUYAMA
<ul><li><strong>Related to</strong> <i><a class="issue tracker-2 status-1 priority-4 priority-default" href="/issues/10897">Feature #10897</a>: Offer an advanced issue query language as an alternative to it's current UI</i> added</li></ul> Redmine - Defect #15781: Customfields have a noticable impact on search performance due to slow database COUNThttps://www.redmine.org/issues/15781?journal_id=540412013-12-30T04:49:25ZToshi MARUYAMA
<ul><li><strong>Related to</strong> <i><a class="issue tracker-2 status-1 priority-4 priority-default" href="/issues/9180">Feature #9180</a>: Improve search system for issues - like "context specific search"</i> added</li></ul> Redmine - Defect #15781: Customfields have a noticable impact on search performance due to slow database COUNThttps://www.redmine.org/issues/15781?journal_id=541252014-01-05T14:29:46ZJean-Philippe Langjp_lang@yahoo.fr
<ul><li><strong>Assignee</strong> set to <i>Jean-Philippe Lang</i></li><li><strong>Target version</strong> set to <i>2.4.3</i></li></ul><p><a class="changeset" title="Reduces the number of subqueries when searching with many custom fields set as searchable (#15781)." href="https://www.redmine.org/projects/redmine/repository/svn/revisions/12481">r12481</a> should significantly improve that by reducing the number of subqueries. Here are the results for up to 7 searchable custom fields and ~10 000 issues on my dev machine, the table shows the execution time of the count query:</p>
<table>
<tr>
<td>searchable fields</td>
<td>without fix</td>
<td>with fix applied</td>
</tr>
<tr>
<td>0</td>
<td>1.5</td>
<td>1.5</td>
</tr>
<tr>
<td>1</td>
<td>1.9</td>
<td>1.9</td>
</tr>
<tr>
<td>2</td>
<td>2.3</td>
<td>1.9</td>
</tr>
<tr>
<td>3</td>
<td>2.7</td>
<td>1.9</td>
</tr>
<tr>
<td>4</td>
<td>3.0</td>
<td>2.0</td>
</tr>
<tr>
<td>5</td>
<td>3.5</td>
<td>2.0</td>
</tr>
<tr>
<td>6</td>
<td>3.9</td>
<td>2.0</td>
</tr>
<tr>
<td>7</td>
<td>4.3</td>
<td>2.1</td>
</tr>
</table>
<p>The results may depend on your custom fields visibility (different subqueries will still be done if custom field have different visibilities across project for the current user). If you are able to try the patch, I'd be happy to get some feedback.</p> Redmine - Defect #15781: Customfields have a noticable impact on search performance due to slow database COUNThttps://www.redmine.org/issues/15781?journal_id=541342014-01-06T04:16:12ZAnonymous
<ul></ul><p>Thanks Jean-Philippe,</p>
<p>I've tested the patch out and my search query went from 10.1s to 7.5s<br />While this is an improvement, It's still very slow, I'd suggest that redmine would benefit greatly from utilising elasticsearch.</p>
<p>Jean-Philippe Lang wrote:</p>
<blockquote>
<p>...<br />The results may depend on your custom fields visibility (different subqueries will still be done if custom field have different visibilities across project for the current user). If you are able to try the patch, I'd be happy to get some feedback.</p>
</blockquote> Redmine - Defect #15781: Customfields have a noticable impact on search performance due to slow database COUNThttps://www.redmine.org/issues/15781?journal_id=541862014-01-09T07:40:17ZJean-Philippe Langjp_lang@yahoo.fr
<ul><li><strong>Status</strong> changed from <i>New</i> to <i>Closed</i></li><li><strong>Resolution</strong> set to <i>Fixed</i></li></ul><p>Thanks for the feedback, I'm closing it as using an alternative search engine is out of the scope of this request. It's true that sequential scan of many text columns is slow but IMHO using something like elasticsearch is overkill and having to install this along with Redmine would be a deal breaker for a few. Plus, I'm not sure it's designed to run queries with complex conditions on other things than text (eg. private projects, privates notes, custom fields visibility vs. user's permissions).</p>
<p>We can get pretty good results by using advanced full text search features of the RDBMS. I've made a POC with Postgres, a few indexes and some SQL changes, the COUNT query with the same data now takes a few milliseconds.</p> Redmine - Defect #15781: Customfields have a noticable impact on search performance due to slow database COUNThttps://www.redmine.org/issues/15781?journal_id=547942014-02-10T17:19:38ZIlya S
<ul></ul><p>why i can't find this revision in 2.4.3.stable ? <a class="external" href="http://www.redmine.org/releases/redmine-2.4.3.zip">http://www.redmine.org/releases/redmine-2.4.3.zip</a></p> Redmine - Defect #15781: Customfields have a noticable impact on search performance due to slow database COUNThttps://www.redmine.org/issues/15781?journal_id=551522014-03-04T04:05:28ZToshi MARUYAMA
<ul></ul><p>Ilya S wrote:</p>
<blockquote>
<p>why i can't find this revision in 2.4.3.stable ? <a class="external" href="http://www.redmine.org/releases/redmine-2.4.3.zip">http://www.redmine.org/releases/redmine-2.4.3.zip</a></p>
</blockquote>
<p>Really. <a class="changeset" title="Reduces the number of subqueries when searching with many custom fields set as searchable (#15781)." href="https://www.redmine.org/projects/redmine/repository/svn/revisions/12481">r12481</a> has not merged to 2.4-stable.<br />Should we fix target version and ChangeLog?</p> Redmine - Defect #15781: Customfields have a noticable impact on search performance due to slow database COUNThttps://www.redmine.org/issues/15781?journal_id=555212014-03-25T08:41:08ZToshi MARUYAMA
<ul></ul><p>Toshi MARUYAMA wrote:</p>
<blockquote>
<p>Ilya S wrote:</p>
<blockquote>
<p>why i can't find this revision in 2.4.3.stable ? <a class="external" href="http://www.redmine.org/releases/redmine-2.4.3.zip">http://www.redmine.org/releases/redmine-2.4.3.zip</a></p>
</blockquote>
<p>Really. <a class="changeset" title="Reduces the number of subqueries when searching with many custom fields set as searchable (#15781)." href="https://www.redmine.org/projects/redmine/repository/svn/revisions/12481">r12481</a> has not merged to 2.4-stable.<br />Should we fix target version and ChangeLog?</p>
</blockquote>
<p>I have added note to ChangeLog and wiki.</p>