Jim Bosch, if things work OK now on this branch I presume it should be OK to merge it now?

Actually, it might be best to hold up a bit - I'm just now seeing some evidence that this might be an overall performance regression rather than an improvement, but at least some of the results are contradictory.  More tests ongoing.

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?

I'm not sure how it can be slower, it is supposed to be faster (basically GROUP BY should need two passes and window function a single pass). Chris should probably be able to figure it out from optimizer plan.
If collections were ordered in database it could work better I guess (basically doing what you describe), the trouble in our case is that order of collections is undefined (not fixed), can be different in different queries so it probably has to go through all of them and "re-order" every time. Again, there may be something else entirely, we do need Chris' help.

DM-17023 has made the ideas being tried here mostly obsolete and the branches totally unmergeable, though it's probably worth keeping the branches in case we decide we want some bits from them later.