Merge lp:~cjwatson/launchpad/package-cache-indexes into lp:launchpad

[Colin Watson]

Add DistributionSourcePackageCache(sourcepackagename, archive) and DistributionSourcePackageCache(binpkgnames) indexes.

Add a DistributionSourcePackageCache(sourcepackagename, archive) index. This makes the upcoming changes to SourcePackageNameVocabulary to use the package cache perform much better - a few milliseconds vs. about a second on dogfood for reasonably typical queries.

Also add a DistributionSourcePackageCache(binpkgnames) index, which makes it feasible to implement a cache-aware equivalent of BinaryAndSourcePackageNameVocabulary using that column as well as SourcePackageName.name. This is much faster than going through DistroSeriesPackageCache in that case.

This can be applied as a hot patch.

[Colin Watson (cjwatson)]

I accidentally landed this as part of spn-vocabulary-use-dspc, so better land the remaining piece now!

review: Approve

=== added file 'database/schema/patch-2209-78-2.sql'
--- database/schema/patch-2209-78-2.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2209-78-2.sql	2016-06-10 16:06:22 +0000
@@ -0,0 +1,12 @@
+-- Copyright 2016 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 distributionsourcepackagecache__sourcepackagename__archive__idx
+    ON DistributionSourcePackageCache (sourcepackagename, archive);
+
+CREATE INDEX distributionsourcepackagecache__binpkgnames__idx
+    ON DistributionSourcePackageCache USING gin (binpkgnames trgm.gin_trgm_ops);
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 78, 2);