=== frankban|afk is now known as frankban
[16:27] <cjwatson> wgrant: Any suggestions on speeding up the query shown in http://paste.ubuntu.com/16923480/ ?  It's rather essential to the xPN vocabulary -> package cache work.  I've also tried the "... AND binarypackagename.id IN (SELECT distroseriespackagecache.binarypackagename FROM ...)" strategy, which is slower.
[16:28] <cjwatson> Or perhaps we can tolerate this given that vocabulary lookups aren't generally fast-path?
[16:31] <cjwatson> It's a *lot* slower than the couple-of-milliseconds lookups in xPN though.
[16:36] <cjwatson> Hm.  It would be a lot better if the planner did the name scan first.
[16:40] <cjwatson> Do I just have to do two separate queries to stop it being stupid?
=== frankban is now known as frankban|afk
[16:42] <cjwatson> Hm, even if I split it into separate queries the DSPC part isn't great.
[16:48] <cjwatson> Aha!  Needs distroseriespackagecache (binarypackagename, archive)
[16:56] <cjwatson> OK, with that it's much better in cases where there aren't too many BPNs.  If there are lots then there seems to be a tipping point where it goes back to terrible (e.g. "name LIKE '%man%'").
[16:58] <cjwatson> Looks like it only uses distroseriespackagecache__binarypackagename__archive__idx if there aren't too many possible BPNs.
[17:11] <cjwatson> I think it will be OK if I can limit the result set.
[23:40] <wgrant> cjwatson: Two separate queries might be necessary. What's your latest progress on that, so I can optimise it further?
[23:46] <cjwatson> wgrant: Further optimisation may not be necessary.  http://paste.ubuntu.com/16934843/ with https://code.launchpad.net/~cjwatson/launchpad/package-cache-indexes/+merge/296379
[23:46] <cjwatson> The trick seems to be pushing the LIMITs down far enough
[23:46] <cjwatson> As well as the indexes of course
[23:47] <wgrant> Try with linux and it will probably take eleven years.
[23:48] <cjwatson> 1440ms hot
[23:48] <cjwatson> http://paste.ubuntu.com/16934912/
[23:49] <wgrant> Thirteen years, then.
[23:52] <cjwatson> Incidentally I wonder why I made GitRepositoryTargetWidget use BinaryAndSourcePackageNameVocabulary rather than SourcePackageNameVocabulary.  Seems silly.
[23:52] <cjwatson> Not that it really helps since LaunchpadTargetWidget needs the combined one anyway, but I noticed it in passing.