diff options
| -rw-r--r-- | guix-data-service/model/package-metadata.scm | 44 | ||||
| -rw-r--r-- | sqitch/deploy/package_derivations_id_package_id_idx.sql | 7 | ||||
| -rw-r--r-- | sqitch/revert/package_derivations_id_package_id_idx.sql | 7 | ||||
| -rw-r--r-- | sqitch/sqitch.plan | 1 | ||||
| -rw-r--r-- | sqitch/verify/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; |