'untranslated' filter slow with message sharing

Bug #360547 reported by Данило Шеган
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Launchpad itself
Fix Released
High
Данило Шеган

Bug Description

Postgres chooses a bad query plan with existing 'untranslated' filter.

A fix means several things:
 * replace "POTMsgSet.id IN" with "TranslationTemplateItem.potmsgset IN"
 * add TranslationTemplateItem.potmsgset = TranslationTemplateItem.potmsgset to the translated query so index on that table is preferred (yeah, this looks silly, but improves the speed by more than 10x on staging DB).

Basically, the second change makes Postgres use translationtemplateitem__potemplate__potmsgset__key index instead of using POTMsgSet id index.

These two changes combined give us around 40x improvement, so on our biggest POTemplate (i.e. ddtp-universe package descriptions, with 45000 messages) we get queries in 550ms where we had 21s.

Changed in rosetta:
assignee: nobody → Данило Шеган (danilo)
milestone: none → 2.2.4
Changed in rosetta:
importance: Undecided → High
status: New → In Progress
description: updated
description: updated
description: updated
description: updated
Revision history for this message
Данило Шеган (danilo) wrote :
Revision history for this message
Данило Шеган (danilo) wrote :

Fixed in db-devel 7968.

Changed in rosetta:
status: In Progress → Fix Committed
Changed in rosetta:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.