Code review comment for lp:~danilo/launchpad/bug-662552-fast-pofile-selection

> > SELECT POFile.*
> > FROM POFile
> > WHERE POFile.potemplate IN (
> > SELECT potemplate
> > FROM TranslationTemplateItem
> > WHERE potmsgset = 1
> > AND sequence > 0)
> > LIMIT 1;
>
> So, it's not surprising that this is by far the worst option. Original
> query ran in ~100ms on staging, my improved version runs in ~1ms, and
> this one takes ~250ms (with warm caches). I've already gotten timings
> for my query on production DB as well: it runs in 0.8ms compared to 60ms
> for the original version.

That's interesting. I had hand-wavy ideas of Postgres being able to
optimize more than it can, i.e. realizing that it did not need to
materialize the IN clause fully. Thanks for trying it out, I have
learnt something :)

> Also, note that I am (ab)using more knowledge of the data model to make
> a slightly different query: if matching POFile does not exist for only
> one selected POTMsgSet in my new version, no POFile will be returned
> even though another POTMsgSet that I excluded through LIMIT 1 in the
> inner query might have a matching POFile. However, that can't happen in
> our model (yet can't be expressed as a DB constraint), so that's where I
> am smarter than Postgres :)

Yeah, I meant to mention - just to make sure that it was intended -
that the original query filtered on POFile.language but not on
TranslationTemplateItem.sequence, as the latter does. I assume that's
what you're referring to.