So, even with new indexes, this is holding steady at about 5min slower (on a single-tract query) than without the changes on this branch. I think the previous benchmarks here must have been user error, because it's been pretty consistent since: ~25 minutes without these changes, ~29 minutes with.
I think we should wait for Christopher Stephens [X] to get back and have a chance to analyze further before giving up on it, though; there may be some small change that would push it out in front.
One quick thought on this query: if I were writing out the algorithm I wanted for this subquery in an imperative programming language (considering the fact that it's always joined to other tables on (instrument, exposure, detector) - I think that's got to be important in the right query plan here), I'd want to step through the ordered list of collections and short circuit as soon as I found a match, instead of trying all of them and then ranking them. Is that actually what (either form of) this query is doing? If not, is that possible?