POFile queries are not using partial indexes well

[Данило Шеган]

With message sharing switchover landed, POFile.getPOTMsgSet*() methods do not make best use of partial indexes in all cases. We should avoid using "something.variant NOT DISTINCT FROM other.variant", AND whenever we have direct values for eg. language or potemplate, we should use that in subselects instead of depending on postgres to be smart when we have that in the outer select and use something.language=outerTM.language.

This happens a lot with getPOTMsgSetWithNewSuggestions and updateStatistics (which makes use of all the methods above).

A workaround is to prepare a bigger, non-partial index like

CREATE INDEX translationmessage__potmsgset__language__idx ON translationmessage USING btree(potmsgset,language);

but we should also make sure queries are using partial indexes for a much better performance.

OOPS-1216D236 OOPS-1216B466 OOPS-1216E469 OOPS-1216B467 seem related (if you look at QUERY_STRING, there's either show=new_suggestions or show=changed_in_launchpad: OOPSes with "show=untranslated" seem to be about caching problems but might be related as well, just like search OOPS-1216H360 is likely to be).

[Данило Шеган (danilo)]

Stuart will add a workaround index to production database.

[Данило Шеган (danilo)]

This is likely the cause of bug #361844 as well.

[Данило Шеган (danilo)]

Fixed in devel 8414. We should watch how new queries are using partial indexes (compared to the full one).