$ git clone git@github.com:bgamari/ghc-perf-import
$ cd ghc-perf-import
$ git clone git@github.com:nomeata/ghc-speed-logs
$ git clone git@github.com:ghc/ghc
$ cabal install
$ psql postgres < schema.sql
$ perf-import -e nomeata ghc-perf-import/*.log
$ perf-import-git
$ echo 'SELECT count(1) FROM commits;' | psql ghc_perf postgres
$ cat <<EOF | psql ghc_perf postgres
COPY (
    SELECT commits.commit_sha, commits.commit_date, results.result_value
    FROM results, commits
    WHERE results.commit_id = commits.commit_id AND
        results.test_id = 36
    ORDER BY (commits.commit_date)
) TO STDOUT CSV;
EOF

\copy (
    SELECT *
    FROM results_view
    WHERE test_env = 'nomeata'
      AND branch_name = 'master'
      AND test_name='compile-allocs/AbsConc3'
    ORDER BY commit_date
) TO 'hi' CSV;

WITH deltas(commit_sha, commit_date, commit_title, test_name, delta) AS (
    SELECT x.commit_sha,
           x.commit_date,
           x.commit_title,
           x.test_name,
           (x.result_value - x.prev_value) / NULLIF(x.prev_value, 0) AS delta
    FROM (
        SELECT commit_sha,
               commit_date,
               commit_title,
               test_name,
               result_value,
               lag(result_value) OVER (PARTITION BY test_name
                                       ORDER BY commit_date) AS prev_value
        FROM results_view
        WHERE branch_name = 'master'
          AND test_env = 'nomeata'
        ORDER BY commit_date
    ) AS x
)
SELECT *
FROM deltas
WHERE abs(delta) > 0.10
  AND test_name LIKE 'compile-allocs/%'
ORDER BY commit_sha, abs(delta) DESC;

$ i=0; while IFS= read -r tests; do echo $tests; ./plot.py -o $i.svg $tests; let i=i+1; done < tests.grouped

SELECT test_envs.test_env_name, tests.test_name,
       array_agg(old.result_value) as old,
       array_agg(new.result_value) as new,
       (avg(new.result_value) - avg(old.result_value)) / NULLIF(avg(old.result_value), 0) as change
FROM (
    SELECT *
    FROM results
    JOIN commits ON (commits.commit_id = results.commit_id)
    WHERE commits.commit_sha ~ '567dbd9bcb603*'
) AS old, (
    SELECT * 
    FROM results
    JOIN commits ON (commits.commit_id = results.commit_id)
    WHERE commits.commit_sha ~ 'ffe4660510a*'
) AS new
JOIN test_envs ON (test_envs.test_env_id = new.test_env_id)
JOIN tests ON (tests.test_id = new.test_id)
WHERE old.test_env_id = new.test_env_id
  AND old.test_id = new.test_id
GROUP BY (new.test_env_id, test_envs.test_env_name, tests.test_name, new.test_id);

SELECT avg(rel_delta)
FROM deltas2
WHERE commit_1 ~ '567dbd9bcb603*'
  AND commit_2 ~ 'ffe4660510a*'
  AND test_name ~ 'compile-alloc*';

SELECT *
FROM deltas
WHERE abs(delta) > 0
  AND test_name ~ 'compile-allocs/'
ORDER BY rel_delta DESC;

SELECT test_name, delta, rel_delta
FROM deltas
WHERE commit_sha = '673efccb3b348e9daf23d9e65460691bbea8586e'
ORDER BY rel_delta DESC;