William Grant has proposed merging lp:~wgrant/launchpad/lpsprc-index-harder into lp:launchpad.
Commit message: Add indices for Person:+(ppa|uploaded|maintained)-packages. Requested reviews: Launchpad code reviewers (launchpad-reviewers): db For more details, see: https://code.launchpad.net/~wgrant/launchpad/lpsprc-index-harder/+merge/134848 Person:+(maintained|uploaded|ppa)-packages like to query LatestPersonSourcePackageReleaseCache filtering on either creator or maintainer, with archive.purpose == PPA or archive.purpose != PPA, ordered by date_uploaded DESC. This branch adds four indices to satisfy those queries rapidly. -- https://code.launchpad.net/~wgrant/launchpad/lpsprc-index-harder/+merge/134848 Your team Launchpad code reviewers is requested to review the proposed merge of lp:~wgrant/launchpad/lpsprc-index-harder into lp:launchpad.
=== added file 'database/schema/patch-2209-38-3.sql' --- database/schema/patch-2209-38-3.sql 1970-01-01 00:00:00 +0000 +++ database/schema/patch-2209-38-3.sql 2012-11-19 09:19:22 +0000 @@ -0,0 +1,17 @@ +-- Copyright 2012 Canonical Ltd. This software is licensed under the +-- GNU Affero General Public License version 3 (see the file LICENSE). + +SET client_min_messages=ERROR; + +-- Create indices for Person:+(ppa|uploaded|maintained)-packages. +CREATE INDEX latestpersonsourcepackagereleasecache__creator__purpose__date__idx + ON latestpersonsourcepackagereleasecache (creator, archive_purpose, date_uploaded DESC); +CREATE INDEX latestpersonsourcepackagereleasecache__creator__date__non_ppa__idx + ON latestpersonsourcepackagereleasecache (creator, date_uploaded) WHERE archive_purpose <> 2; + +CREATE INDEX latestpersonsourcepackagereleasecache__maintainer__purpose__date__idx + ON latestpersonsourcepackagereleasecache (maintainer, archive_purpose, date_uploaded DESC); +CREATE INDEX latestpersonsourcepackagereleasecache__maintainer__date__non_ppa__idx + ON latestpersonsourcepackagereleasecache (maintainer, date_uploaded) WHERE archive_purpose <> 2; + +INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 38, 3);
_______________________________________________ Mailing list: https://launchpad.net/~launchpad-reviewers Post to : [email protected] Unsubscribe : https://launchpad.net/~launchpad-reviewers More help : https://help.launchpad.net/ListHelp
