launchpad-reviewers team mailing list archive

[Colin Watson <cjwatson@xxxxxxxxxxxxx>]

Colin Watson has proposed merging lp:~cjwatson/launchpad/db-optimise-publish-a2 into lp:launchpad/db-devel.

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

Requested reviews:
  Launchpad code reviewers (launchpad-reviewers): db
  Stuart Bishop (stub): db

For more details, see:
https://code.launchpad.net/~cjwatson/launchpad/db-optimise-publish-a2/+merge/226798

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.
-- 
https://code.launchpad.net/~cjwatson/launchpad/db-optimise-publish-a2/+merge/226798
Your team Launchpad code reviewers is requested to review the proposed merge of lp:~cjwatson/launchpad/db-optimise-publish-a2 into lp:launchpad/db-devel.

=== added file 'database/schema/patch-2209-53-8.sql'
--- database/schema/patch-2209-53-8.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2209-53-8.sql	2014-07-15 10:28:30 +0000
@@ -0,0 +1,14 @@
+-- Copyright 2014 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 INDEX sourcepackagepublishinghistory__archive__status__deleted__idx
+    ON sourcepackagepublishinghistory (archive, status)
+    WHERE scheduleddeletiondate IS NULL AND dateremoved IS NULL;
+
+CREATE INDEX binarypackagepublishinghistory__archive__status__deleted__idx
+    ON binarypackagepublishinghistory (archive, status)
+    WHERE scheduleddeletiondate IS NULL AND dateremoved IS NULL;
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 53, 8);