aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--guix-data-service/model/package-metadata.scm44
-rw-r--r--sqitch/deploy/package_derivations_id_package_id_idx.sql7
-rw-r--r--sqitch/revert/package_derivations_id_package_id_idx.sql7
-rw-r--r--sqitch/sqitch.plan1
-rw-r--r--sqitch/verify/package_derivations_id_package_id_idx.sql7
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;