genome / ptero-workflow / 389
93%
master: 63%

LAST BUILD BRANCH: revert-256-service_data_to_save_feature
DEFAULT BRANCH: master
Ran
Jobs 1
Files 57
Run time 1s
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
389

push

travis-ci

davidlmorton 
Add indexes to improve performance

Using the postgres contrib 'pg_stat_statements' module, I was able to
determine what the cause of our performance degradation was:

```
ptero_workflow=# SELECT query, total_time, calls, total_time/calls as
time_per_call, rows/calls as rows_per_call FROM pg_stat_statements
ORDER BY time_per_call;
```

```
query         | SELECT execution_status_history.id AS
execution_status_history_id, execution_status_history.execution_id AS
execution_status_history_execution_id, execution_statu
s_history.timestamp AS execution_status_history_timestamp,
execution_status_history.status AS execution_status_history_status
              | FROM execution_status_history
              | WHERE ? = execution_status_history.execution_id ORDER
BY execution_status_history.timestamp
total_time    | 5094.504
calls         | 309
time_per_call | 16.487067961165
rows_per_call | 3
```

Then using the EXPLAIN command, I was able to spot the missing index:

```
ptero_workflow=# EXPLAIN SELECT execution_status_history.id AS
execution_status_history_id, execution_status_history.execution_id AS
execution_status_history_execution_id, execution_status_history.timestamp AS
execution_status_history_timestamp,
execution_status_history.status AS execution_status_history_status FROM
execution_status_history WHERE 1 = execution_status_history.execution_id
ORDER BY
execution_status_history.timestamp;
```

```
-[ RECORD 1 ]----------------------------------------------
QUERY PLAN | Sort  (cost=4583.27..4583.28 rows=4 width=23)
-[ RECORD 2 ]----------------------------------------------
QUERY PLAN |   Sort Key: "timestamp"
-[ RECORD 3 ]----------------------------------------------
QUERY PLAN |   ->  Seq Scan on execution_status_history
(cost=0.00..4583.23 rows=4 width=23)
-[ RECORD 4 ]----------------------------------------------
QUERY PLAN |         Filter: (5 = execution_id)
```

After adding these indexes, which were added based on other similar
explain statem... (continued)

1818 of 1947 relevant lines covered (93.37%)

0.93 hits per line

Jobs
ID Job ID Ran Files Coverage
1 389.1 0
93.37
 Travis Job 389.1
Source Files on build 389
Detailed source file information is not available for this build.