Comment 4 for bug 371609

Trying to execute the above query results in different query plans on staging and production. Surprisingly, though, it runs in 2s on staging, but 8s on production.

Staging run is suboptimal because it does a full seqscan on pomsgid using ILIKE (subselects above are designed exactly to avoid such behaviour), and the production run does a full seqscan on potmsgset table.

explain analyze
SELECT POTMsgSet.id
  FROM POTMsgSet
  JOIN TranslationTemplateItem
    ON TranslationTemplateItem.potmsgset=POTMsgSet.id
       AND TranslationTemplateItem.potemplate=702
  WHERE (
    POTMsgSet.msgid_singular IS NOT NULL
    AND POTMsgSet.msgid_singular IN (
      SELECT POMsgID.id FROM POMsgID
        WHERE
        id IN (
          SELECT (msgid_singular)
            FROM POTMsgSet
            JOIN TranslationTemplateItem
              ON TranslationTemplateItem.potmsgset = POTMsgSet.id
            WHERE TranslationTemplateItem.potemplate=702
                  AND TranslationTemplateItem.sequence > 0 )
        AND msgid ILIKE '%' || 'Media Management and Playback application' || '%'));
                                                                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop (cost=253887.25..254114.66 rows=1 width=4) (actual time=9017.276..9017.465 rows=5 loops=1)
   -> Nested Loop (cost=253887.25..254102.16 rows=16 width=4) (actual time=9017.219..9017.320 rows=5 loops=1)
         -> HashAggregate (cost=253887.25..253887.30 rows=5 width=8) (actual time=9017.088..9017.093 rows=5 loops=1)
               -> Hash IN Join (cost=222355.53..253887.24 rows=5 width=8) (actual time=8773.518..9017.060 rows=5 loops=1)
                     Hash Cond: (pomsgid.id = public.potmsgset.msgid_singular)
                     -> Seq Scan on pomsgid (cost=0.00..31346.40 rows=127 width=4) (actual time=1352.051..1595.569 rows=6 loops=1)
                           Filter: (msgid ~~* '%Media Management and Playback application%'::text)
                     -> Hash (cost=221744.94..221744.94 rows=48847 width=4) (actual time=7421.417..7421.417 rows=51630 loops=1)
                           -> Hash Join (cost=182046.18..221744.94 rows=48847 width=4) (actual time=6678.880..7377.252 rows=51630 loops=1)
                                 Hash Cond: (public.translationtemplateitem.potmsgset = public.potmsgset.id)
                                 -> Bitmap Heap Scan on translationtemplateitem (cost=1116.28..23893.98 rows=48847 width=4) (actual time=7.308..41.628 rows=51630 loops=1)
                                       Recheck Cond: ((potemplate = 702) AND (sequence > 0))
                                       -> Bitmap Index Scan on translationtemplateitem__potemplate__sequence__key (cost=0.00..1104.07 rows=48847 width=0) (actual time=7.255..7.255 rows=51630 loops=1)
                                             Index Cond: ((potemplate = 702) AND (sequence > 0))
                                 -> Hash (cost=116084.40..116084.40 rows=3952440 width=8) (actual time=6671.123..6671.123 rows=3951332 loops=1)
                                       -> Seq Scan on potmsgset (cost=0.00..116084.40 rows=3952440 width=8) (actual time=0.021..3490.685 rows=3951332 loops=1)
         -> Index Scan using potmsgset_primemsgid_idx on potmsgset (cost=0.00..42.81 rows=13 width=8) (actual time=0.041..0.041 rows=1 loops=5)
               Index Cond: (public.potmsgset.msgid_singular = pomsgid.id)
   -> Index Scan using translationtemplateitem__potmsgset__idx on translationtemplateitem (cost=0.00..0.77 rows=1 width=4) (actual time=0.024..0.025 rows=1 loops=5)
         Index Cond: (public.translationtemplateitem.potmsgset = public.potmsgset.id)
         Filter: (public.translationtemplateitem.potemplate = 702)
 Total runtime: 9019.455 ms
(22 rows)