catmaid / CATMAID / 5554
46%
master: 45%

LAST BUILD BRANCH: dev
DEFAULT BRANCH: master
Ran
Jobs 4
Files 97
Run time 12min
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
5554

push

travis-ci

tomka 
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)

7679 of 16850 relevant lines covered (45.57%)

1.81 hits per line

Jobs
ID Job ID Ran Files Coverage
1 5554.1 0
45.57
 Travis Job 5554.1
2 5554.2 0
45.57
 Travis Job 5554.2
3 5554.3 0
45.57
 Travis Job 5554.3
4 5554.4 0
44.21
 Travis Job 5554.4
Source Files on build 5554
Detailed source file information is not available for this build.