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 -> Nested Loop (cost=0.00..101.46 rows=18 width=4) (actual time=0.064..0.475 rows=16 loops=1) -> Nested Loop (cost=0.00..96.39 rows=18 width=8) (actual time=0.051..0.287 rows=17 loops=1) -> Index Scan using potmsgset_primemsgid_idx on potmsgset (cost=0.00..18.56 rows=5 width=4) (actual time=0.028..0.062 rows=8 loops=1) Index Cond: (msgid_singular = 347523) Filter: (id <> 8171049) -> Index Scan using translationtemplateitem__potmsgset__idx on translationtemplateitem (cost=0.00..15.52 rows=4 width=8) (actual time=0.013..0.025 rows=2 loops=8) 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.008..0.010 rows=1 loops=17) Index Cond: (suggestivepotemplate.potemplate = translationtemplateitem.potemplate) -> Unique (cost=270.26..270.74 rows=27 width=76) (actual time=1.074..1.119 rows=4 loops=1) -> Sort (cost=270.26..270.33 rows=27 width=76) (actual time=1.073..1.087 rows=18 loops=1) Sort Key: (COALESCE(translationmessage.msgstr0, (-1))), (COALESCE(translationmessage.msgstr1, (-1))), (COALESCE(translationmessage.msgstr2, (-1))), (COALESCE(translationmessage.msgstr3, (-1))), (COALESCE(translationmessage.msgstr4, (-1))), (COALESCE(translationmessage.msgstr5, (-1))), translationmessage.date_created Sort Method: quicksort Memory: 27kB -> Nested Loop (cost=0.00..269.62 rows=27 width=76) (actual time=0.099..0.764 rows=18 loops=1) -> CTE Scan on pots (cost=0.00..0.36 rows=18 width=4) (actual time=0.070..0.503 rows=16 loops=1) -> Index Scan using translationmessage__potmsgset__language__idx on translationmessage (cost=0.00..14.93 rows=2 width=76) (actual time=0.011..0.012 rows=1 loops=16) Index Cond: ((translationmessage.potmsgset = pots.id) AND (translationmessage.language = 132)) Total runtime: 1.390 ms (20 rows)
Time: 10.611 ms
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 lateItem ON TranslationTemp lateItem. potmsgset = POTMsgSet.id plate ON TranslationTemp lateItem. potemplate = SuggestivePOTem plate.potemplat e id<>8171049) age.id, TranslationMess age.COMMENT, TranslationMess age.date_ created,
TranslationMess age.date_ reviewed, 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 translationmess age.potmsgset and translationmess age.language in (132)
QUERY PLAN ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ------- ---- 72..372. 20 rows=27 width=76) (actual time=1.075..1.121 rows=4 loops=1) primemsgid_ idx on potmsgset (cost=0.00..18.56 rows=5 width=4) (actual time=0.028..0.062 rows=8 loops=1)
Index Cond: (msgid_singular = 347523)
Filter: (id <> 8171049) lateitem_ _potmsgset_ _idx on translationtemp lateitem (cost=0.00..15.52 rows=4 width=8) (actual time=0.013..0.025 rows=2 loops=8)
Index Cond: (translationtem plateitem. potmsgset = potmsgset.id) plate_pkey on suggestivepotem plate (cost=0.00..0.27 rows=1 width=4) (actual time=0.008..0.010 rows=1 loops=17)
Index Cond: (suggestivepote mplate. potemplate = translationtemp lateitem. potemplate) 26..270. 74 rows=27 width=76) (actual time=1.074..1.119 rows=4 loops=1) 26..270. 33 rows=27 width=76) (actual time=1.073..1.087 rows=18 loops=1) translationmess age.msgstr0, (-1))), (COALESCE( translationmess age.msgstr1, (-1))), (COALESCE( translationmess age.msgstr2, (-1))), (COALESCE( translationmess age.msgstr3, (-1))), (COALESCE( translationmess age.msgstr4, (-1))), (COALESCE( translationmess age.msgstr5, (-1))), translationmess age.date_ created
-> CTE Scan on pots (cost=0.00..0.36 rows=18 width=4) (actual time=0.070..0.503 rows=16 loops=1)
-> Index Scan using translationmess age__potmsgset_ _language_ _idx on translationmessage (cost=0.00..14.93 rows=2 width=76) (actual time=0.011..0.012 rows=1 loops=16)
Index Cond: ((translationme ssage.potmsgset = pots.id) AND (translationmes sage.language = 132))
FROM POTMsgSet
JOIN TranslationTemp
JOIN SuggestivePOTem
WHERE msgid_singular = 347523 and potmsgset.
SELECT DISTINCT ON (COALESCE(msgstr0, -1), COALESCE(msgstr1, -1), COALESCE(msgstr2, -1), COALESCE(msgstr3, -1), COALESCE(msgstr4, -1), COALESCE(msgstr5, -1)) TranslationMess
FROM TranslationMessage join pots on pots.id=
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=371.
CTE pots
-> Nested Loop (cost=0.00..101.46 rows=18 width=4) (actual time=0.064..0.475 rows=16 loops=1)
-> Nested Loop (cost=0.00..96.39 rows=18 width=8) (actual time=0.051..0.287 rows=17 loops=1)
-> Index Scan using potmsgset_
-> Index Scan using translationtemp
-> Index Scan using suggestivepotem
-> Unique (cost=270.
-> Sort (cost=270.
Sort Key: (COALESCE(
Sort Method: quicksort Memory: 27kB
-> Nested Loop (cost=0.00..269.62 rows=27 width=76) (actual time=0.099..0.764 rows=18 loops=1)
Total runtime: 1.390 ms
(20 rows)
Time: 10.611 ms