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

Proposed by Colin Watson
Status: Needs review
Proposed branch: ~cjwatson/launchpad:db-potmsgset-msgid-plural-idx
Merge into: launchpad:master
Diff against target: 12 lines (+6/-0)
1 file modified
database/schema/patch-2210-01-5.sql (+6/-0)
Reviewer Review Type Date Requested Status
William Grant Needs Information
Review via email: mp+381533@code.launchpad.net

Commit message

Index POTMsgSet(msgid_plural)

Description of the change

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.

To post a comment you must log in.
Revision history for this message
William Grant (wgrant) wrote :

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
Revision history for this message
Colin Watson (cjwatson) wrote :

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/

Revision history for this message
Colin Watson (cjwatson) wrote :

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

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1diff --git a/database/schema/patch-2210-01-5.sql b/database/schema/patch-2210-01-5.sql
2new file mode 100644
3index 0000000..f740a32
4--- /dev/null
5+++ b/database/schema/patch-2210-01-5.sql
6@@ -0,0 +1,6 @@
7+-- Copyright 2020 Canonical Ltd. This software is licensed under the
8+-- GNU Affero General Public License version 3 (see the file LICENSE).
9+
10+CREATE INDEX potmsgset__msgid_plural__idx ON POTMsgSet (msgid_plural);
11+
12+INSERT INTO LaunchpadDatabaseRevision VALUES (2210, 01, 5);

Subscribers

People subscribed via source and target branches

to status/vote changes: