Launchpad itself

Merge lp:~cjwatson/launchpad/db-optimise-publish-a2 into lp:launchpad/db-devel

  1. db-optimise-publish-a2
  2. Merge into db-devel
Proposed by Colin Watson
Status: Merged
Approved by: Colin Watson
Approved revision: no longer in the source branch.
Merged at revision: 12750
Proposed branch: lp:~cjwatson/launchpad/db-optimise-publish-a2
Merge into: lp:launchpad/db-devel
Diff against target: 18 lines (+14/-0)
1 file modified
database/schema/patch-2209-53-8.sql (+14/-0)
To merge this branch: bzr merge lp:~cjwatson/launchpad/db-optimise-publish-a2
Reviewer Review Type Date Requested Status
William Grant db Approve
Stuart Bishop db Pending
Review via email: mp+226798@code.launchpad.net

Commit message

Add xPPH(archive, status) WHERE scheduleddeletiondate IS NULL indices, to help speed up step A2 of the publisher.

Description of the change

Index queries used by step A2 of the publisher. This drops the number of pages it needs to read by a factor of several hundred (at least for the new optimised versions of those queries), and generally seems like a good thing.

Should be applicable live.

To post a comment you must log in.
Revision history for this message
William Grant (wgrant) :
review: Approve (db)

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== added file 'database/schema/patch-2209-53-8.sql'
2--- database/schema/patch-2209-53-8.sql 1970-01-01 00:00:00 +0000
3+++ database/schema/patch-2209-53-8.sql 2014-07-15 11:15:34 +0000
4@@ -0,0 +1,14 @@
5+-- Copyright 2014 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 INDEX sourcepackagepublishinghistory__archive__status__scheduleddeletiondate__idx
11+ ON sourcepackagepublishinghistory (archive, status)
12+ WHERE scheduleddeletiondate IS NULL;
13+
14+CREATE INDEX binarypackagepublishinghistory__archive__status__scheduleddeletiondate__idx
15+ ON binarypackagepublishinghistory (archive, status)
16+ WHERE scheduleddeletiondate IS NULL;
17+
18+INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 53, 8);

Subscribers

People subscribed via source and target branches

to status/vote changes: