catmaid / CATMAID / 5522
46%
master: 45%

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

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 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 ans 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)
   Sort Key... (continued)

7679 of 16850 relevant lines covered (45.57%)

1.81 hits per line

Jobs
ID Job ID Ran Files Coverage
1 5522.1 0
45.57
 Travis Job 5522.1
2 5522.2 0
45.57
 Travis Job 5522.2
3 5522.3 0
45.57
 Travis Job 5522.3
4 5522.4 0
44.21
 Travis Job 5522.4
Source Files on build 5522
Detailed source file information is not available for this build.