02/04: Speed up finding the locales for a revision

[Christopher Baines]

cbaines pushed a commit to branch master
in repository data-service.

commit e3062abf33f9887656ce4bd99f34317694533d50
Author: Christopher Baines <mail@cbaines.net>
AuthorDate: Wed Sep 14 10:26:03 2022 +0100

    Speed up finding the locales for a revision
---
 guix-data-service/model/package-metadata.scm       | 44 ++++++++++++++--------
 .../package_derivations_id_package_id_idx.sql      |  7 ++++
 .../package_derivations_id_package_id_idx.sql      |  7 ++++
 sqitch/sqitch.plan                                 |  1 +
 .../package_derivations_id_package_id_idx.sql      |  7 ++++
 5 files changed, 51 insertions(+), 15 deletions(-)

diff --git a/guix-data-service/model/package-metadata.scm 
b/guix-data-service/model/package-metadata.scm
index 9593b49..4372d8f 100644
--- a/guix-data-service/model/package-metadata.scm
+++ b/guix-data-service/model/package-metadata.scm
@@ -435,23 +435,37 @@ WHERE packages.id IN (
                                                                        
revision-id)
   (exec-query
    conn
-   "SELECT DISTINCT coalesce(package_descriptions.locale, 
package_synopsis.locale)
-    FROM package_descriptions
-    INNER JOIN package_description_sets
-      ON package_descriptions.id = ANY 
(package_description_sets.description_ids)
-    INNER JOIN package_metadata
-      ON package_metadata.package_description_set_id = 
package_description_sets.id
-    INNER JOIN package_synopsis_sets
-      ON package_synopsis_sets.id = package_metadata.package_synopsis_set_id
-    INNER JOIN package_synopsis
-      ON package_synopsis.id = ANY (package_synopsis_sets.synopsis_ids)
-    INNER JOIN packages
-      ON packages.package_metadata_id = package_metadata.id
-    INNER JOIN package_derivations
-      ON package_derivations.package_id = packages.id
+   "
+WITH package_metadata_ids AS (
+  SELECT packages.package_metadata_id AS id
+  FROM packages
+  WHERE packages.id IN (
+    SELECT package_derivations.package_id
+    FROM package_derivations
     INNER JOIN guix_revision_package_derivations
       ON package_derivations.id = 
guix_revision_package_derivations.package_derivation_id
-    WHERE guix_revision_package_derivations.revision_id = $1"
+    WHERE guix_revision_package_derivations.revision_id = $1
+  )
+), synopsis_locales AS (
+  SELECT package_synopsis.locale
+  FROM package_metadata
+  INNER JOIN package_synopsis_sets
+    ON package_synopsis_sets.id = package_metadata.package_synopsis_set_id
+  INNER JOIN package_synopsis
+    ON package_synopsis.id = ANY (package_synopsis_sets.synopsis_ids)
+  WHERE package_metadata.id IN (SELECT id FROM package_metadata_ids)
+), description_locales AS (
+  SELECT package_descriptions.locale
+  FROM package_metadata
+  INNER JOIN package_description_sets
+    ON package_description_sets.id = 
package_metadata.package_description_set_id
+  INNER JOIN package_descriptions
+    ON package_descriptions.id = ANY (package_description_sets.description_ids)
+  WHERE package_metadata.id IN (SELECT id FROM package_metadata_ids)
+)
+SELECT locale FROM synopsis_locales
+UNION
+SELECT locale FROM description_locales"
    (list revision-id)))
 
 (define (synopsis-counts-by-locale conn revision-id)
diff --git a/sqitch/deploy/package_derivations_id_package_id_idx.sql 
b/sqitch/deploy/package_derivations_id_package_id_idx.sql
new file mode 100644
index 0000000..ff05c4b
--- /dev/null
+++ b/sqitch/deploy/package_derivations_id_package_id_idx.sql
@@ -0,0 +1,7 @@
+-- Deploy guix-data-service:package_derivations_id_package_id_idx to pg
+
+BEGIN;
+
+CREATE INDEX package_derivations_id_package_id_idx ON package_derivations (id, 
package_id) WITH (fillfactor='100');
+
+COMMIT;
diff --git a/sqitch/revert/package_derivations_id_package_id_idx.sql 
b/sqitch/revert/package_derivations_id_package_id_idx.sql
new file mode 100644
index 0000000..4378119
--- /dev/null
+++ b/sqitch/revert/package_derivations_id_package_id_idx.sql
@@ -0,0 +1,7 @@
+-- Revert guix-data-service:package_derivations_id_package_id_idx from pg
+
+BEGIN;
+
+-- XXX Add DDLs here.
+
+COMMIT;
diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan
index 5b7408b..a33137b 100644
--- a/sqitch/sqitch.plan
+++ b/sqitch/sqitch.plan
@@ -88,3 +88,4 @@ partition_package_derivations_by_guix_revision_range 
2022-05-23T18:20:37Z Chris
 package_range_index 2022-06-17T10:39:31Z Chris <chris@felis> # Add index on 
package_derivations_by_guix_revision_range.package_name
 fix_git_commits_duplicates 2022-06-17T10:39:50Z Chris <chris@felis> # Fix 
git_commits duplicates
 git_repositories_query_substitutes 2022-09-09T11:35:16Z Chris <chris@felis> # 
Add git_repositories.query_substitutes
+package_derivations_id_package_id_idx 2022-09-14T09:24:30Z Chris <chris@felis> 
# Add index on package_derivations id and package_id
diff --git a/sqitch/verify/package_derivations_id_package_id_idx.sql 
b/sqitch/verify/package_derivations_id_package_id_idx.sql
new file mode 100644
index 0000000..cf5c246
--- /dev/null
+++ b/sqitch/verify/package_derivations_id_package_id_idx.sql
@@ -0,0 +1,7 @@
+-- Verify guix-data-service:package_derivations_id_package_id_idx on pg
+
+BEGIN;
+
+-- XXX Add verifications here.
+
+ROLLBACK;