Merge lp:~wgrant/launchpad/bugsummary-v2-db-1 into lp:launchpad

[William Grant]

Create some new indices on bugsummary, optimising rebuilds and coping with the new access_policy and deprecated fixed_upstream columns.

The main BugSummary queries come in two forms: finding all visible rows for a target without a tag, or finding all visible rows for a target with a tag. The product and productseries indices work well for this, but distribution, distroseries, distributionsourcepackage and sourcepackage queries always search on (distribution, sourcepackagename), which was not previously indexed. This branch adds four new indices to satisfy the tagged and tagless queries for all four target types, and another two indices to optimise bugsummaryrebuild's queries which don't filter on tag nullness.

Several of the old indices will be dropped at the conclusion of this work, and a new UNIQUE index will be created in the next branch.

This should be applied live shortly after 2209-19-0 is fastdowntimed.

[Stuart Bishop (stub)]

Looks fine.

I assume all the _idx2 names are going to be renamed after other indexes are dropped.

It might be helpful for us to have a test helper that does "SET enable_seqscan=off; EXPLAIN (FORMAT JSON) $query;" and checks the result to make sure a particular index is being hit.

review: Approve (db)

=== added file 'database/schema/patch-2209-19-1.sql'
--- database/schema/patch-2209-19-1.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2209-19-1.sql	2012-07-05 07:15:27 +0000
@@ -0,0 +1,26 @@
+-- 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 bugsummary__distribution__idx2 ON bugsummary USING btree
+    (distribution, sourcepackagename) WHERE distribution IS NOT NULL;
+CREATE INDEX bugsummary__distroseries__idx2 ON bugsummary USING btree
+    (distroseries, sourcepackagename) WHERE distroseries IS NOT NULL;
+CREATE INDEX bugsummary__distribution_count__idx2 ON bugsummary USING btree
+    (distribution, sourcepackagename, status)
+    WHERE distribution IS NOT NULL AND tag IS NULL;
+CREATE INDEX bugsummary__distroseries_count__idx2 ON bugsummary USING btree
+    (distroseries, sourcepackagename, status)
+    WHERE distroseries IS NOT NULL AND tag IS NULL;
+CREATE INDEX bugsummary__distribution_tag_count__idx2 ON bugsummary USING btree
+    (distribution, sourcepackagename, status)
+    WHERE distribution IS NOT NULL AND tag IS NOT NULL;
+CREATE INDEX bugsummary__distroseries_tag_count__idx2 ON bugsummary USING btree
+    (distroseries, sourcepackagename, status)
+    WHERE distroseries IS NOT NULL AND tag IS NOT NULL;
+CREATE INDEX bugsummary__full__idx2 ON bugsummary USING btree
+    (tag, status, product, productseries, distribution, distroseries,
+     sourcepackagename, viewed_by, access_policy, milestone, importance);
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 19, 1);