[00:00] <wgrant> Hmmm, getting there.
[00:03] <wgrant> I'm not quite sure why it's choosing the bad plan for the source query.
[00:03] <wgrant> But I have it reliably down around 10ms hot, and not toooo much worse cold.
[00:04] <wgrant> Which will probably do.
[00:08] <wgrant> Weird.
[00:08] <wgrant> But that's 4-50ms without any particularly weird indices. Possibly nicer than my other attempt.
[00:11] <cjwatson> Oh, what did I miss?
[00:12] <wgrant> http://paste.ubuntu.com/16935503/
[00:12] <wgrant> Main win was deconfusing the planner by pre-materialising main_archive_ids.
[00:12] <wgrant> But that only got it down to 100ms.
[00:12] <wgrant> Which isn't good enough for me.
[00:13] <wgrant> The last query probably wins here. I don't understand exactly why the planner wanted the index that way, since it's only 3x faster, but it works.
[00:14] <wgrant> cjwatson: Are you copying the definition of what it means to search from the existing vocab?
[00:14] <wgrant> Because this definition of search is pretty weird.
[00:14] <cjwatson> Sort of; this was from my rewritten vocabs.
[00:14] <cjwatson> Can we just use DSPC.name instead of going through xPN at all?
[00:14] <wgrant> See the third query.
[00:15] <wgrant> That was my attempt at that.
[00:15] <wgrant> It works well, but needs an extra extension (installed by default, just not in the DB)
[00:15] <wgrant> The indexes are all in place on DF if you want to experiment.
[00:16] <cjwatson> Ah right.  You could do it for DistroSeriesPackageCache.name as well I think.
[00:16] <wgrant> (btree_gin lets us include an integer in a GIN index, which isn't normally possible)
[00:16] <wgrant> Yeah, the same sort of thing applies there.
[00:16] <wgrant> But that seems to be planned sensibly anyway.
[00:16] <wgrant> Aaand you could not use DSPC at all.
[00:16] <wgrant> Er, not use DistroSeriesPackageCache at all, that is.
[00:17] <wgrant> Since we have the binary names in a column on the other table.
[00:17] <cjwatson> Bit harder to pick out, but true.
[00:17] <cjwatson> Not going to experiment now, since bedtime.
[00:17] <wgrant> This query is pretty weird since it prefers all substring-matching source names, then all substring-matching matching binary names.
[00:17] <wgrant> A good plan.
[00:17] <wgrant> Night.
[00:20] <wgrant> (otoh the weirdness makes the query quite fast in the primary archive case, since it can just run straight down SPN(name) and return the first 10 it finds that exist. probably not so fast in a PPA.)
[00:25] <wgrant> Er, no, that was my hacked query, I guess. the ORDER BY name at the end prevents that.