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.translationmessage.msgstr2, (-1))), (COALESCE(public.translationmessage.msgstr3, (-1))), (COALESCE(public.translationmessage.msgstr4, (-1))), (COALESCE(public.translationmessage.msgstr5, (-1))), public.translationmessage.date_created Sort Method: quicksort Memory: 25kB -> Nested Loop (cost=101.49..176.41 rows=8 width=36) (actual time=0.563..0.663 rows=6 loops=1) -> HashAggregate (cost=101.49..101.54 rows=5 width=4) (actual time=0.495..0.499 rows=6 loops=1) -> Nested Loop (cost=0.00..101.45 rows=18 width=4) (actual time=0.055..0.464 rows=24 loops=1) -> Nested Loop (cost=0.00..96.38 rows=18 width=8) (actual time=0.043..0.224 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.043 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) (actual time=0.012..0.018 rows=3 loops=9) Index Cond: (translationtemplateitem.potmsgset = potmsgset.id) -> Index Scan using suggestivepotemplate_pkey on suggestivepotemplate (cost=0.00..0.27 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=25) Index Cond: (suggestivepotemplate.potemplate = translationtemplateitem.potemplate) -> Index Scan using translationmessage__potmsgset__language__idx on translationmessage (cost=0.00..14.94 rows=2 width=40) (actual time=0.023..0.024 rows=1 loops=6) Index Cond: ((public.translationmessage.potmsgset = potmsgset.id) AND (public.translationmessage.language = 132)) Filter: (public.translationmessage.potmsgset <> 8171049) -> Index Scan using translationmessage_pkey on translationmessage (cost=0.00..11.99 rows=1 width=76) (actual time=0.014..0.015 rows=1 loops=4) Index Cond: (public.translationmessage.id = public.translationmessage.id) Total runtime: 1.048 ms (23 rows)
Time: 10.518 ms
SELECT TranslationMess age.COMMENT, TranslationMess age.date_ created,
TranslationMess age.date_ reviewed,
TranslationMess age.id,
TranslationMess age.is_ current_ ubuntu,
TranslationMess age.is_ current_ upstream,
TranslationMess age.LANGUAGE, TranslationMess age.msgstr0,
TranslationMes sage.msgstr1,
TranslationMes sage.msgstr2,
TranslationMes sage.msgstr3,
TranslationMes sage.msgstr4,
TranslationMes sage.msgstr5,
TranslationMes sage.origin,
TranslationMes sage.potemplate ,
TranslationMes sage.potmsgset,
TranslationMes sage.reviewer,
TranslationMes sage.submitter,
TranslationMes sage.validation _status,
TranslationMes sage.was_ obsolete_ in_last_ import age.id IN age.id sage.LANGUAGE IN (132)) age.potmsgset <> 8171049 lateItem ON TranslationTemp lateItem. potmsgset = POTMsgSet.id plate ON TranslationTemp lateItem. potemplate = SuggestivePOTem plate.potemplat e
FROM TranslationMessage
WHERE TranslationMess
(SELECT DISTINCT ON (COALESCE(msgstr0, -1), COALESCE(msgstr1, -1), COALESCE(msgstr2, -1), COALESCE(msgstr3, -1), COALESCE(msgstr4, -1), COALESCE(msgstr5, -1)) TranslationMess
FROM TranslationMessage
WHERE (TranslationMes
AND TranslationMess
AND potmsgset IN
(SELECT distinct POTMsgSet.id
FROM POTMsgSet
JOIN TranslationTemp
JOIN SuggestivePOTem
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) 77..272. 91 rows=8 width=76) (actual time=0.742..0.793 rows=4 loops=1) 77..176. 85 rows=8 width=4) (actual time=0.720..0.723 rows=4 loops=1) 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. translationmess age.msgstr0, (-1))), (COALESCE( public. translationmess age.msgstr1, (-1))), (COALESCE( public. translationmess age.msgstr2, (-1))), (COALESCE( public. translationmess age.msgstr3, (-1))), (COALESCE( public. translationmess age.msgstr4, (-1))), (COALESCE( public. translationmess age.msgstr5, (-1))), public. translationmess age.date_ created
Sort Method: quicksort Memory: 25kB
- > Nested Loop (cost=101. 49..176. 41 rows=8 width=36) (actual time=0.563..0.663 rows=6 loops=1)
-> HashAggregate (cost=101. 49..101. 54 rows=5 width=4) (actual time=0.495..0.499 rows=6 loops=1)
-> Nested Loop (cost=0.00..101.45 rows=18 width=4) (actual time=0.055..0.464 rows=24 loops=1)
-> Nested Loop (cost=0.00..96.38 rows=18 width=8) (actual time=0.043..0.224 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.043 rows=9 loops=1)
Index Cond: (msgid_singular = 347523)
-> Index Scan using translationtemp lateitem_ _potmsgset_ _idx on translationtemp lateitem (cost=0.00..15.52 rows=4 width=8) (actual time=0.012..0.018 rows=3 loops=9)
Index Cond: (translationtem plateitem. potmsgset = potmsgset.id)
-> Index Scan using suggestivepotem plate_pkey on suggestivepotem plate (cost=0.00..0.27 rows=1 width=4) (actual time=0.007..0.008 rows=1 loops=25)
Index Cond: (suggestivepote mplate. potemplate = translationtemp lateitem. potemplate)
-> Index Scan using translationmess age__potmsgset_ _language_ _idx on translationmessage (cost=0.00..14.94 rows=2 width=40) (actual time=0.023..0.024 rows=1 loops=6)
Index Cond: ((public. translationmess age.potmsgset = potmsgset.id) AND (public. translationmess age.language = 132))
Filter: (public. translationmess age.potmsgset <> 8171049) age_pkey on translationmessage (cost=0.00..11.99 rows=1 width=76) (actual time=0.014..0.015 rows=1 loops=4)
Index Cond: (public. translationmess age.id = public. translationmess age.id)
-> Nested Loop (cost=176.
-> HashAggregate (cost=176.
-> Unique (cost=176.
-> Index Scan using translationmess
Total runtime: 1.048 ms
(23 rows)
Time: 10.518 ms