From 2ea78cff47fd05d658a5f3c02f64f44c4404d995 Mon Sep 17 00:00:00 2001
From: Christopher Baines <mail@cbaines.net>
Date: Sun, 7 Jul 2019 21:23:18 +0100
Subject: Tweak how logs are stored

Previously, the query for the jobs page was really slow, as it checked the
load_new_guix_revision_job_log_parts table for each job, doing a sequential
scan through the potentially large table.

Adding an index didn't seem to help, as the query planner would belive the
query could return loads of rows, where actually, all that needed checking is
whether a single row existed with a given job_id.

To avoid adding the index to the load_new_guix_revision_job_log_parts table,
and fighting with the query planner, this commit changes the
load_new_guix_revision_job_logs table to include a blank entry for jobs which
are currently being processed. This is inserted at the start of the job, and
then updated at the end to combine and replace all the parts.

This all means that the jobs page should render quickly now.
---
 ...nge_load_new_guix_revision_job_logs_contents_to_be_nullable.sql | 7 +++++++
 1 file changed, 7 insertions(+)
 create mode 100644 sqitch/verify/change_load_new_guix_revision_job_logs_contents_to_be_nullable.sql

(limited to 'sqitch/verify/change_load_new_guix_revision_job_logs_contents_to_be_nullable.sql')

diff --git a/sqitch/verify/change_load_new_guix_revision_job_logs_contents_to_be_nullable.sql b/sqitch/verify/change_load_new_guix_revision_job_logs_contents_to_be_nullable.sql
new file mode 100644
index 0000000..a165548
--- /dev/null
+++ b/sqitch/verify/change_load_new_guix_revision_job_logs_contents_to_be_nullable.sql
@@ -0,0 +1,7 @@
+-- Verify guix-data-service:change_load_new_guix_revision_job_logs_contents_to_be_nullable on pg
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;
-- 
cgit v1.2.3

