Simplest approach seems to have done the trick: I've removed all output datasets from the big selectDimensions query, since they didn't constrain it anyway, and we were already not relying on that query returning dataset_ids for them.
After that, some profiling revealed that the slow overall speed of preflight (12 minutes for ci_hsc!) was not being spent in that query - it was in slow, mostly Python data ID manipulations afterwards. All but the first commit on this branch are optimizations for that. It's down to 5 minutes now, and while I have ideas on how to go further, I think they're out of scope for this ticket.
Andy Salnikov, could you look at the first commit?
Nate Lust, could you look at the rest?
PR is: https://github.com/lsst/daf_butler/pull/124