launchpad-reviewers team mailing list archive

[William Grant <me@xxxxxxxxxxxxxxxxxx>]

William Grant has proposed merging lp:~wgrant/launchpad/bugsummary-v2-db-1 into lp:launchpad with lp:launchpad/db-devel as a prerequisite.

Requested reviews:
  Launchpad code reviewers (launchpad-reviewers)

For more details, see:
https://code.launchpad.net/~wgrant/launchpad/bugsummary-v2-db-1/+merge/113500

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.
-- 
https://code.launchpad.net/~wgrant/launchpad/bugsummary-v2-db-1/+merge/113500
Your team Launchpad code reviewers is requested to review the proposed merge of lp:~wgrant/launchpad/bugsummary-v2-db-1 into lp:launchpad.

=== 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);