Christopher Baines <mail@cbaines.net> writes:

> Remove the Builds_index from the schema, as it would have been removed in
> error by the upgrade-6 upgrade.  Add a specific index on the Builds status
> field, as this helps with db-get-builds queries, and add an index on the
> Outputs derivation field, as this helps with the db-get-outputs part of
> db-get-builds.
>
> * src/sql/upgrade-7.sql: New file.
> * src/schema.sql: Update accordingly.
> ---
>  src/schema.sql        | 3 ++-
>  src/sql/upgrade-7.sql | 7 +++++++
>  2 files changed, 9 insertions(+), 1 deletion(-)
>  create mode 100644 src/sql/upgrade-7.sql
>
> diff --git a/src/schema.sql b/src/schema.sql
> index 1104551..60d250a 100644
> --- a/src/schema.sql
> +++ b/src/schema.sql
> @@ -74,7 +74,8 @@ CREATE TABLE Events (
>
>  -- Create indexes to speed up common queries, in particular those
>  -- corresponding to /api/latestbuilds and /api/queue HTTP requests.
> -CREATE INDEX Builds_index ON Builds(job_name, system, status ASC, timestamp ASC, derivation, evaluation, stoptime DESC);
> +CREATE INDEX Builds_status_index ON Builds (status);
> +CREATE INDEX Outputs_derivation_index ON Outputs (derivation);
>  CREATE INDEX Inputs_index ON Inputs(specification, name, branch);
>
>  COMMIT;
> diff --git a/src/sql/upgrade-7.sql b/src/sql/upgrade-7.sql
> new file mode 100644
> index 0000000..1be3470
> --- /dev/null
> +++ b/src/sql/upgrade-7.sql
> @@ -0,0 +1,7 @@
> +BEGIN TRANSACTION;
> +
> +CREATE INDEX Builds_status_index ON Builds (status);
> +
> +CREATE INDEX Outputs_derivation_index ON Outputs (derivation);
> +
> +COMMIT;

I've pushed a rebased version of this patch now. I've also recofigured
bayfront to apply these changes. Checking with guix weather, I think it
might be helping.

→ guix weather --substitute-urls=https://bayfront.guix.gnu.org
computing 13,852 package derivations for x86_64-linux...
looking for 14,400 store items on https://bayfront.guix.gnu.org...
updating substitutes from 'https://bayfront.guix.gnu.org'... 100.0%
https://bayfront.guix.gnu.org
  54.7% substitutes available (7,881 out of 14,400)
  at least 37,690.9 MiB of nars (compressed)
  71,872.0 MiB on disk (uncompressed)
  0.048 seconds per request (697.1 seconds in total)
  20.7 requests per second

  13.8% (897 out of 6,519) of the missing items are queued
  at least 1,000 queued builds
      x86_64-linux: 1000 (100.0%)
  build rate: 9.34 builds per hour
      x86_64-linux: 9.34 builds per hour