catmaid / CATMAID / 6167
45%
master: 45%

LAST BUILD BRANCH: dev
DEFAULT BRANCH: master
Ran
Jobs 3
Files 100
Run time 14min
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
6167

push

travis-ci

tomka 
Add BRIN index to the execution_time column of the transaction log

Since this table keeps track of all transactions to provide additional
semantics, it is rather large on big instances. The execution time
column is constantly increasing and typically part of queries. A BRIN
index is therefore a good choice: it is very small and fast to update,
if the data it works on is sequential (it stores min/max per page).

As an example, such a BRIN index takes up only 280 kB for 57,997,765
transaction log entries.

Without such an index, queries can take an unreasonable amount of time.
The history view in the front-end does exactly such a query and could
easily occupy a back-end worker for a long time, e.g.:

  SELECT execution_time
  FROM catmaid_transaction_info
  WHERE execution_time >= '2019-07-07T00:00:00+00:00'::timestamptz
  AND execution_time <= '2019-07-18T00:00:00+00:00'::timestamptz
  AND user_id = '304'
  AND project_id = '1'

This index changes the common query plan for this query from

 Index Scan using catmaid_transaction_info_pk on catmaid_transaction_info  (cost=0.56..1031296.76 rows=1 width=8)
   Index Cond: ((execution_time >= '2019-07-06 20:00:00-04'::timestamp with time zone) AND (execution_time <= '2019-07-17 20:00:00-04'::timestamp with time zone))
   Filter: ((user_id = 304) AND (project_id = 1))

to

 Bitmap Heap Scan on catmaid_transaction_info  (cost=59.03..15641.84 rows=1 width=8)
   Recheck Cond: ((execution_time >= '2019-07-06 20:00:00-04'::timestamp with time zone) AND (execution_time <= '2019-07-17 20:00:00-04'::timestamp with time zone))
   Filter: ((user_id = 304) AND (project_id = 1))
   ->  Bitmap Index Scan on catmaid_transaction_info_execution_time_idx  (cost=0.00..59.03 rows=8122 width=0)
         Index Cond: ((execution_time >= '2019-07-06 20:00:00-04'::timestamp with time zone) AND (execution_time <= '2019-07-17 20:00:00-04'::timestamp with time zone))

and saves a lot of time by doing so.

8169 of 18354 relevant lines covered (44.51%)

1.24 hits per line

Jobs
ID Job ID Ran Files Coverage
1 6167.1 0
44.51
 Travis Job 6167.1
2 6167.2 0
44.51
 Travis Job 6167.2
3 6167.3 0
41.43
 Travis Job 6167.3
Source Files on build 6167
Detailed source file information is not available for this build.