catmaid / CATMAID
46%
master: 45%

LAST BUILD BRANCH: dev
DEFAULT BRANCH: master
Repo Added
Files 111
Badge
Embed ▾
README BADGES
x

If you need to use a raster PNG badge, change the '.svg' to '.png' in the link

Markdown

Textile

RDoc

HTML

Rst

LAST BUILD ON BRANCH features/class-instance-trigram-index
branch: features/class-instance-trigram-index
CHANGE BRANCH
x

5555

Pull #1821

travis-ci

web-flow 
Enable Posgres trigram extension and add class instance name index

This adds a trigram index to the class_instance table to speed up name
queries that involve wildcards and regular expressions. These are
common, especially in the Neuron Search Widget. Since we also allow
wildcards at the end of a name (e.g. when searching for a neuron name),
B-tree can't be used. Trigram however works fine with this and is
included in a regular Postgres setup. It only needs to be enabled
through an extension.

For small instances, these queries are already fast. However on large
instances, sequential scans on the class_instance table can be quite
slow when doing name lookups. For instance, in a large test instance I
have 215,707,715 class instance entries and looking for a name takes
about ~30 seconds on a heavily optimized Postgres database. Using the
trigram index bring this down to 2 ms (!). The size of this index is
comparable to the foreign key index. The example table has a size of 19
GB, the primary key index is ~4.5 GB and the trigram index ~5 GB. Given
that neuron name search and annotation search is done a lot, I feel this
increase in space requirement is justified.

A simple name search looks like this (typing "aSIP" into the neuron name
search):

  SELECT ci.id, ci.user_id, ci.creation_time, ci.edition_time,
    ci.project_id, ci.class_id, ci.name, skel_link.skeletons
  FROM class_instance ci
  LEFT JOIN LATERAL (
    SELECT cici_n.class_instance_b AS id,
    array_agg(cici_n.class_instance_a) AS skeletons
    FROM class_instance_class_instance cici_n
    WHERE cici_n.class_instance_b = ci.id
    AND cici_n.relation_id = 10
    GROUP BY 1
  ) skel_link ON ci.id = skel_link.id
  WHERE ci.project_id = 1
    AND ci.class_id = ANY (ARRAY[3,6])
    AND ci.name ~~* '%aSIP%'
  ORDER BY id ASC;

The query plan without the trigram index looks like this:

 Sort  (cost=3443312.34..3443339.28 rows=10779 width=83) (actual time=25818.498..25818.498 rows=5 loops=1)
   S... (continued)
Pull Request #1821: Enable pg_trgm extension and add trigram index for class instance names

7679 of 16850 relevant lines covered (45.57%)

1.81 hits per line

Relevant lines Covered
16850 RELEVANT LINES 7679 COVERED LINES
1.81 HITS PER LINE
Source Files on features/class-instance-trigram-index
Coverage ∆ File Lines Relevant Covered Missed Hits/Line

Recent builds

Builds Branch Commit Type Ran Committer Via Coverage
5555 features/class-instance-trigram-index Enable Posgres trigram extension and add class instance name index This adds a trigram index to the class_instance table to speed up name queries that involve wildcards and regular expressions. These are common, especially in the Neuron Search Wi... Pull #1821 web-flow travis-ci pending completion  
5554 features/class-instance-trigram-index Enable Posgres trigram extension and add class instance name index This adds a trigram index to the class_instance table to speed up name queries that involve wildcards and regular expressions. These are common, especially in the Neuron Search Wi... push tomka travis-ci pending completion  
5525 features/class-instance-trigram-index Enable Posgres trigram extension and add class instance name index This adds a trigram index to the class_instance table to speed up name queries that involve wildcards and regular expressions. These are common, especially in the Neuron Search Wi... Pull #1821 web-flow travis-ci pending completion  
5524 features/class-instance-trigram-index Enable Posgres trigram extension and add class instance name index This adds a trigram index to the class_instance table to speed up name queries that involve wildcards and regular expressions. These are common, especially in the Neuron Search Wi... push tomka travis-ci pending completion  
5523 features/class-instance-trigram-index Enable Posgres trigram extension and add class instance name index This adds a trigram index to the class_instance table to speed up name queries that involve wildcards and regular expressions. These are common, especially in the neuron widget. S... Pull #1821 web-flow travis-ci pending completion  
5522 features/class-instance-trigram-index Enable Posgres trigram extension and add class instance name index This adds a trigram index to the class_instance table to speed up name queries that involve wildcards and regular expressions. These are common, especially in the neuron widget. S... push tomka travis-ci pending completion  
See All Builds (4802)