Merge ~cjwatson/launchpad:db-potmsgset-msgid-plural-idx into launchpad:master

[Colin Watson]

Index POTMsgSet(msgid_plural)

This is used by POFileMixIn._getTemplateSearchQuery. Adding this index probably won't fix timeouts on its own, but it certainly helps avoid some full table scans.

This will need to be applied live with CONCURRENTLY.

[William Grant (wgrant)]

Can you provide some example plans? This may make things worse, as the current query assumes that listing all the potmsgsets in the template and filtering those down is faster than going the other direction, which doesn't seem unreasonable.

review: Needs Information

[Colin Watson (cjwatson)]

It's a bit long for a merge proposal comment, but here's what I got by starting from the query in https://oops.canonical.com/oops/?oopsid=OOPS-95113ba2c33ebc63b0cd97430215a98d:

https://paste.ubuntu.com/p/44zxm2XWs3/

[Colin Watson (cjwatson)]

I did previously observe seq scans on potmsgset as well, but on further inspection those were from an experimental query where I'd lifted part of the TranslationTemplateItem join out to a CTE and made some other possible simplifications, so not quite applicable here.

Unmerged commits

ecfb28b... by Colin Watson on 2020-04-01

Index POTMsgSet(msgid_plural)

This is used by POFileMixIn._getTemplateSearchQuery. Adding this index
probably won't fix timeouts on its own, but it certainly helps avoid
some full table scans.

LP: #736005

diff --git a/database/schema/patch-2210-01-5.sql b/database/schema/patch-2210-01-5.sql
 new file mode 100644
index 0000000..f740a32
--- /dev/null
+++ b/database/schema/patch-2210-01-5.sql
@@ -0,0 +1,6 @@
+-- Copyright 2020 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+CREATE INDEX potmsgset__msgid_plural__idx ON POTMsgSet (msgid_plural);
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2210, 01, 5);