Launchpad itself

Merge lp:~wgrant/launchpad/lpsprc-index-harder into lp:launchpad

  1. lpsprc-index-harder
  2. Merge into devel
Proposed by William Grant on 2012-11-19
Status: Merged
Approved by: Stuart Bishop on 2012-11-19
Approved revision: no longer in the source branch.
Merged at revision: 16291
Proposed branch: lp:~wgrant/launchpad/lpsprc-index-harder
Merge into: lp:launchpad
Diff against target: 21 lines (+17/-0)
1 file modified
database/schema/patch-2209-38-3.sql (+17/-0)
To merge this branch: bzr merge lp:~wgrant/launchpad/lpsprc-index-harder
Reviewer Review Type Date Requested Status
Stuart Bishop db 2012-11-19 Approve on 2012-11-19
Review via email: mp+134848@code.launchpad.net

Commit Message

Add indices for Person:+(ppa|uploaded|maintained)-packages.

Description of the Change

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.

To post a comment you must log in.
Stuart Bishop (stub) wrote :

Looks fine.

review: Approve (db)
Stuart Bishop (stub) wrote :

Applied to staging and qastaging

Stuart Bishop (stub) wrote :

Applied to production

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== added file 'database/schema/patch-2209-38-3.sql'
2--- database/schema/patch-2209-38-3.sql 1970-01-01 00:00:00 +0000
3+++ database/schema/patch-2209-38-3.sql 2012-11-19 09:23:23 +0000
4@@ -0,0 +1,17 @@
5+-- Copyright 2012 Canonical Ltd. This software is licensed under the
6+-- GNU Affero General Public License version 3 (see the file LICENSE).
7+
8+SET client_min_messages=ERROR;
9+
10+-- Create indices for Person:+(ppa|uploaded|maintained)-packages.
11+CREATE INDEX latestpersonsourcepackagereleasecache__creator__purpose__date__idx
12+ ON latestpersonsourcepackagereleasecache (creator, archive_purpose, date_uploaded DESC);
13+CREATE INDEX latestpersonsourcepackagereleasecache__creator__date__non_ppa__idx
14+ ON latestpersonsourcepackagereleasecache (creator, date_uploaded DESC) WHERE archive_purpose <> 2;
15+
16+CREATE INDEX latestpersonsourcepackagereleasecache__maintainer__purpose__date__idx
17+ ON latestpersonsourcepackagereleasecache (maintainer, archive_purpose, date_uploaded DESC);
18+CREATE INDEX latestpersonsourcepackagereleasecache__maintainer__date__non_ppa__idx
19+ ON latestpersonsourcepackagereleasecache (maintainer, date_uploaded DESC) WHERE archive_purpose <> 2;
20+
21+INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 38, 3);