Merge lp:~lifeless/launchpad/bug-421901 into lp:launchpad/db-devel

[Robert Collins]

[r=stub][bug=421901] Schema preparing for improving who-commented-on-bug queries.

The commented bugs query is slow because we scan across 4 tables, even a bitmap join will only drop that to effectively three, so we end up reading all the messages from $foo, then filtering at the bugmessage step, and then onto bug. Making a copy of owner available on bugmessage and indexing appropriately (see the bug for a variety of tried indices) gives a 10x speed up on our pathological cases.

[Stuart Bishop (stub)]

Fine

review: Approve (db)

=== modified file 'database/schema/comments.sql'
--- database/schema/comments.sql	2011-03-31 15:08:15 +0000
+++ database/schema/comments.sql	2011-04-07 10:58:28 +0000
@@ -1360,6 +1360,7 @@
 COMMENT ON COLUMN BugMessage.bugwatch IS 'The external bug this bug comment was imported from.';
 COMMENT ON COLUMN BugMessage.remote_comment_id IS 'The id this bug comment has in the external bug tracker, if it is an imported comment. If it is NULL while having a bugwatch set, this comment was added in Launchpad and needs to be pushed to the external bug tracker.';
 COMMENT ON COLUMN BugMessage.index IS 'The index (used in urls) of the message in a particular bug.';
+COMMENT ON COLUMN BugMessage.owner IS 'Denormalised owner from Message, used for efficient queries on commentors.';
 
 -- Messaging subsytem
 COMMENT ON TABLE Message IS 'This table stores a single RFC822-style message. Messages can be threaded (using the parent field). These messages can then be referenced from elsewhere in the system, such as the BugMessage table, integrating messageboard facilities with the rest of The Launchpad.';

=== added file 'database/schema/patch-2208-60-0.sql'
--- database/schema/patch-2208-60-0.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2208-60-0.sql	2011-04-07 10:58:28 +0000
@@ -0,0 +1,15 @@
+-- Copyright 2011 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+SET client_min_messages=ERROR;
+
+-- denormalise message.owner for query efficiency.
+ALTER TABLE bugmessage ADD COLUMN owner int;
+-- And an index.
+CREATE INDEX bugmessage__owner__index__idx ON bugmessage USING btree(owner, index);
+
+-- Triggers to maintain in both directions.
+CREATE TRIGGER bugmessage__owner__mirror AFTER UPDATE OR INSERT ON bugmessage FOR EACH ROW EXECUTE PROCEDURE bugmessage_copy_owner_from_message();
+CREATE TRIGGER message__owner__mirror AFTER UPDATE ON message FOR EACH ROW EXECUTE PROCEDURE message_copy_owner_to_bugmessage();
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2208, 60, 0);

=== modified file 'database/schema/trusted.sql'
--- database/schema/trusted.sql	2011-03-29 10:17:46 +0000
+++ database/schema/trusted.sql	2011-04-07 10:58:28 +0000
@@ -1954,6 +1954,50 @@
     return int(total_heat)
 $$;
 
+CREATE OR REPLACE FUNCTION bugmessage_copy_owner_from_message()
+RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path TO public AS
+$$
+BEGIN
+    IF TG_OP = 'INSERT' THEN
+        IF NEW.owner is NULL THEN
+            UPDATE BugMessage
+            SET owner = Message.owner FROM
+            Message WHERE
+            Message.id = NEW.message AND
+            BugMessage.id = NEW.id;
+        END IF;
+    ELSIF NEW.message != OLD.message THEN
+        UPDATE BugMessage
+        SET owner = Message.owner FROM
+        Message WHERE
+        Message.id = NEW.message AND
+        BugMessage.id = NEW.id;
+    END IF;
+    RETURN NULL; -- Ignored - this is an AFTER trigger
+END;
+$$;
+
+COMMENT ON FUNCTION bugmessage_copy_owner_from_message() IS
+'Copies the message owner into bugmessage when bugmessage changes.';
+
+CREATE OR REPLACE FUNCTION message_copy_owner_to_bugmessage()
+RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path TO public AS
+$$
+BEGIN
+    IF NEW.owner != OLD.owner THEN
+        UPDATE BugMessage
+        SET owner = NEW.owner
+        WHERE
+        BugMessage.message = NEW.id;
+    END IF;
+    RETURN NULL; -- Ignored - this is an AFTER trigger
+END;
+$$;
+
+COMMENT ON FUNCTION message_copy_owner_to_bugmessage() IS
+'Copies the message owner into bugmessage whenmessage changes.';
+
+
 CREATE OR REPLACE FUNCTION bug_update_heat_copy_to_bugtask()
 RETURNS trigger LANGUAGE plpgsql SECURITY DEFINER SET search_path TO public AS
 $$