POFile:+translate timeouts

SELECT TranslationMessage.COMMENT, TranslationMessage.date_created,
                                   TranslationMessage.date_reviewed,
                                   TranslationMessage.id,
                                   TranslationMessage.is_current_ubuntu,
                                   TranslationMessage.is_current_upstream,
                                   TranslationMessage.LANGUAGE, TranslationMessage.msgstr0,
                                                                TranslationMessage.msgstr1,
                                                                TranslationMessage.msgstr2,
                                                                TranslationMessage.msgstr3,
                                                                TranslationMessage.msgstr4,
                                                                TranslationMessage.msgstr5,
                                                                TranslationMessage.origin,
                                                                TranslationMessage.potemplate,
                                                                TranslationMessage.potmsgset,
                                                                TranslationMessage.reviewer,
                                                                TranslationMessage.submitter,
                                                                TranslationMessage.validation_status,
                                                                TranslationMessage.was_obsolete_in_last_import
FROM TranslationMessage
WHERE TranslationMessage.id IN
    (SELECT DISTINCT ON (COALESCE(msgstr0, -1), COALESCE(msgstr1, -1), COALESCE(msgstr2, -1), COALESCE(msgstr3, -1), COALESCE(msgstr4, -1), COALESCE(msgstr5, -1)) TranslationMessage.id
     FROM TranslationMessage
     WHERE (TranslationMessage.LANGUAGE IN (132))
       AND TranslationMessage.potmsgset <> 8171049
       AND potmsgset IN
         (SELECT POTMsgSet.id
          FROM POTMsgSet
          JOIN TranslationTemplateItem ON TranslationTemplateItem.potmsgset = POTMsgSet.id
          JOIN SuggestivePOTemplate ON TranslationTemplateItem.potemplate = SuggestivePOTemplate.potemplate
          WHERE msgid_singular = 347523)
     ORDER BY COALESCE(msgstr0, -1), COALESCE(msgstr1, -1), COALESCE(msgstr2, -1), COALESCE(msgstr3, -1), COALESCE(msgstr4, -1), COALESCE(msgstr5, -1), date_created DESC)LIMIT 2001

 Limit (cost=294364.63..296495.59 rows=200 width=76) (actual time=994.854..1063.958 rows=4 loops=1)
   -> Nested Loop (cost=294364.63..296495.59 rows=200 width=76) (actual time=994.852..1063.954 rows=4 loops=1)
         -> HashAggregate (cost=294364.63..294366.63 rows=200 width=4) (actual time=935.851..935.857 rows=4 loops=1)
               -> Unique (cost=256804.40..291860.61 rows=200322 width=36) (actual time=926.269..926.276 rows=4 loops=1)
                     -> Sort (cost=256804.40..261812.43 rows=2003212 width=36) (actual time=926.268..926.270 rows=6 loops=1)
                           Sort Key: (COALESCE(public.translationmessage.msgstr0, (-1))), (COALESCE(public.translationmessage.msgstr1, (-1))), (COALESCE(public.translation...

this is substantially faster than the double nested query:
with pots as (SELECT POTMsgSet.id
          FROM POTMsgSet
          JOIN TranslationTemplateItem ON TranslationTemplateItem.potmsgset = POTMsgSet.id
          JOIN SuggestivePOTemplate ON TranslationTemplateItem.potemplate = SuggestivePOTemplate.potemplate
          WHERE msgid_singular = 347523)
 SELECT TranslationMessage.COMMENT, TranslationMessage.date_created,
                                   TranslationMessage.date_reviewed,
                                   TranslationMessage.id,
                                   TranslationMessage.is_current_ubuntu,
                                   TranslationMessage.is_current_upstream,
                                   TranslationMessage.LANGUAGE, TranslationMessage.msgstr0,
                                                                TranslationMessage.msgstr1,
                                                                TranslationMessage.msgstr2,
                                                                TranslationMessage.msgstr3,
                                                                TranslationMessage.msgstr4,
                                                                TranslationMessage.msgstr5,
                                                                TranslationMessage.origin,
                                                                TranslationMessage.potemplate,
                                                                TranslationMessage.potmsgset,
                                                                TranslationMessage.reviewer,
                                                                TranslationMessage.submitter,
                                                                TranslationMessage.validation_status,
                                                                TranslationMessage.was_obsolete_in_last_import
FROM TranslationMessage
WHERE TranslationMessage.id IN
    (SELECT DISTINCT ON (COALESCE(msgstr0, -1), COALESCE(msgstr1, -1), COALESCE(msgstr2, -1), COALESCE(msgstr3, -1), COALESCE(msgstr4, -1), COALESCE(msgstr5, -1)) TranslationMessage.id
     FROM TranslationMessage
     WHERE (TranslationMessage.LANGUAGE IN (132))
       AND TranslationMessage.potmsgset <> 8171049
       AND potmsgset IN (select id from pots)
     ORDER BY COALESCE(msgstr0, -1), COALESCE(msgstr1, -1), COALESCE(msgstr2, -1), COALESCE(msgstr3, -1), COALESCE(msgstr4, -1), COALESCE(msgstr5, -1), date_created DESC)LIMIT 2001

 Limit (cost=372.84..697.30 rows=27 width=76) (actual time=0.814..0.872 rows=4 loops=1)
   CTE pots
     -> Nested Loop (cost=0.00..101.45 rows=18 width=4) (actual time=0.057..0.513 rows=24 loops=1)
           -> Nested Loop (cost=0.00..96.38 rows=18 width=8) (actual time=0.044..0.268 rows=25 loops=1)
                 -> Index Scan using potmsgset_primemsgid_idx on potmsgset (cost=0.00..18.54 rows=5 width=4) (actual time=0.021..0.047 rows=9 loops=1)
                       Index Cond: (msgid_singular = 347523)
                 -> Index Scan using translationtemplateitem__potmsgset__idx on translationtemplateitem (cost=0.00..15.52 rows=4 width=8) (actu...

SELECT TranslationMessage.COMMENT, TranslationMessage.date_created,
                                   TranslationMessage.date_reviewed,
                                   TranslationMessage.id,
                                   TranslationMessage.is_current_ubuntu,
                                   TranslationMessage.is_current_upstream,
                                   TranslationMessage.LANGUAGE, TranslationMessage.msgstr0,
                                                                TranslationMessage.msgstr1,
                                                                TranslationMessage.msgstr2,
                                                                TranslationMessage.msgstr3,
                                                                TranslationMessage.msgstr4,
                                                                TranslationMessage.msgstr5,
                                                                TranslationMessage.origin,
                                                                TranslationMessage.potemplate,
                                                                TranslationMessage.potmsgset,
                                                                TranslationMessage.reviewer,
                                                                TranslationMessage.submitter,
                                                                TranslationMessage.validation_status,
                                                                TranslationMessage.was_obsolete_in_last_import
FROM TranslationMessage
WHERE TranslationMessage.id IN
    (SELECT DISTINCT ON (COALESCE(msgstr0, -1), COALESCE(msgstr1, -1), COALESCE(msgstr2, -1), COALESCE(msgstr3, -1), COALESCE(msgstr4, -1), COALESCE(msgstr5, -1)) TranslationMessage.id
     FROM TranslationMessage
     WHERE (TranslationMessage.LANGUAGE IN (132))
       AND TranslationMessage.potmsgset <> 8171049
       AND potmsgset IN
         (SELECT distinct POTMsgSet.id
          FROM POTMsgSet
          JOIN TranslationTemplateItem ON TranslationTemplateItem.potmsgset = POTMsgSet.id
          JOIN SuggestivePOTemplate ON TranslationTemplateItem.potemplate = SuggestivePOTemplate.potemplate
          WHERE msgid_singular = 347523)
     ORDER BY COALESCE(msgstr0, -1), COALESCE(msgstr1, -1), COALESCE(msgstr2, -1), COALESCE(msgstr3, -1), COALESCE(msgstr4, -1), COALESCE(msgstr5, -1), date_created DESC)LIMIT 2001;

(note the distinct in the innermost select)
is also much cheaper:

 Limit (cost=176.77..272.91 rows=8 width=76) (actual time=0.743..0.796 rows=4 loops=1)
   -> Nested Loop (cost=176.77..272.91 rows=8 width=76) (actual time=0.742..0.793 rows=4 loops=1)
         -> HashAggregate (cost=176.77..176.85 rows=8 width=4) (actual time=0.720..0.723 rows=4 loops=1)
               -> Unique (cost=176.53..176.67 rows=8 width=36) (actual time=0.694..0.710 rows=4 loops=1)
                     -> Sort (cost=176.53..176.55 rows=8 width=36) (actual time=0.692..0.700 rows=6 loops=1)
                           Sort Key: (COALESCE(public.translationmessage.msgstr0, (-1))), (COALESCE(public.translationmessage.msgstr1, (-1))), (COALESCE(public.tr...

using a regular join gets a 225 estimated cost and 300ms runtime:
SELECT DISTINCT ON (COALESCE(msgstr0, -1), COALESCE(msgstr1, -1), COALESCE(msgstr2, -1), COALESCE(msgstr3, -1), COALESCE(msgstr4, -1), COALESCE(msgstr5, -1)) TranslationMessage.id
     FROM TranslationMessage join (potmsgset JOIN TranslationTemplateItem ON TranslationTemplateItem.potmsgset = POTMsgSet.id
          JOIN SuggestivePOTemplate ON TranslationTemplateItem.potemplate = SuggestivePOTemplate.potemplate) on TranslationMessage.potmsgset=POTMsgSet.id
     WHERE (TranslationMessage.LANGUAGE IN (132))
       AND TranslationMessage.potmsgset <> 8171049
       AND msgid_singular = 347523
     ORDER BY COALESCE(msgstr0, -1), COALESCE(msgstr1, -1), COALESCE(msgstr2, -1), COALESCE(msgstr3, -1), COALESCE(msgstr4, -1), COALESCE(msgstr5, -1), date_created DESC LIMIT 2001;

This has the best plan and performance I've been able to put together: 372 startup and completion estimate, 10ms actual runtime.

 explain analyze with pots as (SELECT POTMsgSet.id
          FROM POTMsgSet
          JOIN TranslationTemplateItem ON TranslationTemplateItem.potmsgset = POTMsgSet.id
          JOIN SuggestivePOTemplate ON TranslationTemplateItem.potemplate = SuggestivePOTemplate.potemplate
          WHERE msgid_singular = 347523 and potmsgset.id<>8171049)
SELECT DISTINCT ON (COALESCE(msgstr0, -1), COALESCE(msgstr1, -1), COALESCE(msgstr2, -1), COALESCE(msgstr3, -1), COALESCE(msgstr4, -1), COALESCE(msgstr5, -1)) TranslationMessage.id, TranslationMessage.COMMENT, TranslationMessage.date_created,
                                   TranslationMessage.date_reviewed,TranslationMessage.is_current_ubuntu,
                                   TranslationMessage.is_current_upstream,
                                   TranslationMessage.LANGUAGE, TranslationMessage.msgstr0,
                                                                TranslationMessage.msgstr1,
                                                                TranslationMessage.msgstr2,
                                                                TranslationMessage.msgstr3,
                                                                TranslationMessage.msgstr4,
                                                                TranslationMessage.msgstr5,
                                                                TranslationMessage.origin,
                                                                TranslationMessage.potemplate,
                                                                TranslationMessage.potmsgset,
                                                                TranslationMessage.reviewer,
                                                                TranslationMessage.submitter,
                                                                TranslationMessage.validation_status,
                                                                TranslationMessage.was_obsolete_in_last_import
     FROM TranslationMessage join pots on pots.id=translationmessage.potmsgset and translationmessage.language in (132)
 ORDER BY COALESCE(msgstr0, -1), COALESCE(msgstr1, -1), COALESCE(msgstr2, -1), COALESCE(msgstr3, -1), COALESCE(msgstr4, -1), COALESCE(msgstr5, -1), date_created DESC LIMIT 2001;
                                                                                                                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit (cost=371.72..372.20 rows=27 width=76) (actual time=1.075..1.121 rows=4 loops=1)
   CTE pots
     ...

On prod, cold: Limit (cost=241.77..242.26 rows=28 width=76) (actual time=14.100..14.162 rows=4 loops=1)
and then
20:01 < stub> 7ms on prod_2
20:02 < stub> 70ms on the master
20:02 < stub> 1.4ms the second run

So, this looks win to me.

Its past EOD for me, but if noone picks this up and does it over night I'll implement tomorrow am.

Do note that the original version returned 200 rows, while the final one you have returns 27 (and innermost distinct shouldn't change that, since that was for potmsgset in (...)).

Also, your "pots" query seems to contain unused JOINs:

  JOIN TranslationTemplateItem ON TranslationTemplateItem.potmsgset = POTMsgSet.id
          JOIN SuggestivePOTemplate ON TranslationTemplateItem.potemplate = SuggestivePOTemplate.potemplate

I haven't looked closely otherwise, but unless Gary has added WITH support to Storm in the last week (he did say he wanted to :), then tough luck with that too.

Huh, the original one returns 4 rows (on qastaging):
ids 206643309 215539313 192242823 215770253
and the doctored one returns
192242823 215539313 206643309 215770253

- the same ids.

I added with support to storm last week, it made component bug searches about 30 times faster.

The joins will constrain the rows to ones where the potmsg has a suggestion (because they are inner joins). I'm taking it on faith that they are needed because they were present in the original query I refactored.

(Oh - rows - I think you're reading the explain *estimated* row count, not the *actual* row count). Here is the original queries outer line of the plan:
 Limit (cost=294364.63..296495.59 rows=200 width=76) (actual time=994.854..1063.958 rows=4 loops=1)

note the actual row count is 4, only the estimate is 200.

Fixed in stable r12595 <http://bazaar.launchpad.net/~launchpad-pqm/launchpad/stable/revision/12595>.

All three pages render on qastaging in about 30% of the time on production; the packagekit one timed out once cold, then worked - OOPS-1900QS38. I think we're good to go on this, it will give us more headroom on partly-cold pages even if it doesn't solve the entire issue.