Merge lp:~stevenk/launchpad/spph-packageupload-index-redux into lp:launchpad

[Steve Kowalik]

Add an index on SourcePackagePublishingHistory.id where packageupload is unset so an upcoming garbo job can find publications to work on quickly.

Add an index on SourcePackagePublishingHistory.id when packageupload is unset so that the query contained in https://code.launchpad.net/~stevenk/launchpad/set-spph-packageupload/+merge/108513 makes use of this index to find SPPHs that need to be checked.

[Stuart Bishop (stub)]

This looks fine.

Have we tested those queries anywhere yet with the new index? I haven't had much success getting this sort of index used in joins with PG 8.4, but we are on 9.1 now. We can test on staging before applying to production easily enough.

review: Approve (db)

[Stuart Bishop (stub)]

This has been applied to production.

=== added file 'database/schema/patch-2209-15-3.sql'
--- database/schema/patch-2209-15-3.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2209-15-3.sql	2012-06-05 23:48:24 +0000
@@ -0,0 +1,8 @@
+-- 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 INDEX sourcepackagepublishinghistory__packageupload__idx ON sourcepackagepublishinghistory USING btree (id) WHERE packageupload IS NULL;
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 15, 3);