genome / ptero-workflow

93%

master: 63%

LAST BUILD ON BRANCH fix_performance_degradation

branch: fix_performance_degradation

CHANGE BRANCH

x

Reset

• fix_performance_degradation
• concurrency
• delete_workflows
• dev-all-in-one-db-strings
• dsl-update
• handle_parallel_task_with_scalar_input
• janitor-spec
• list_patch
• live_demo
• logging_updates
• master
• revert-256-service_data_to_save_feature
• v0.1.0

Build # 389

Build Type

push

travis-ci

Committed by davidlmorton

Commit Message

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)

Run Details

1818 of 1947 relevant lines covered (93.37%)

0.93 hits per line