When looking at the 6500 ISA it's mandatory to keep in mind that it's not a middle of the road schoolbook CPU like its predecessor, the 6800, or the later 68k. It uses resources similar to, but less than, the 6800, in more specialized order to gain more versatility. Part of this is done by dropping some functionality (*1), but more often by trading orthogonality for added addressing capabilities.
The 6800 had exactly one memory addressing mode beside ZP and ABS, loading a 16 bit pointer into its only index register X and using that plus an 8 bit offset for addressing. It was maybe the most relevant idea about the 6500 ISA to drop that 16 bit register for two 8 bit ones and turn the single indexed operation into SIX different ways of addressing.
Lets be honest, wasn't that versatile way of addressing what did set the 6502 apart from all other early 8 bit ISAs?
Of course doing so comes with a cost. 6 variants for memory operations (plus immediate) eat not only up a lot of microcode space, which can only in part be reclaimed by 'compression' but also fills the opcode space. The 6500 has 21 memory operations. Having all 6 addressing modes assigned orthogonality to them would already fill up half the opcode space.
That's already a reason why not all memory operations support all addressing modes. Only modes that are thought of as useful for each are added. BIT being the most visible example, with only ZP and ABS - after all, in an embedded system ports are usually at fixed locations.
The same optimization goes for the use of index registers. They are not symmetric, but have dedicated usage. Yes, that's exactly like Intel did with the 8086 (or Zilog with the Z80): registers are not equal but have special functions where they excel. It's a common way to add more versatile functions without blowing too much resources.
The other very important point when musing about the way the 6500 ISA is built is to keep in mind that it was targeted at embedded operation, not general computation and even less personal computing, which wasn't really a thing at the time. So it's about tight RAM and ROM and fixed function, not managing huge swathes of highly dynamic data.
Indirect is only used on the JMP instruction, no other. Why was this mode limited to only JMP,
Maybe because it came (almost) for free. The ability to do an indirect jump is needed to perform reset/interrupts, as they as well fetch target vectors. Having an indirect jump is a very convenient way to build context dependent operation (e.g. state machines) (*2).
rather than being more generally available? It seems very useful,
Making it more generally available would need more opcodes, increasing the CPU size for something that can equally well be reached by using (ZP),Y addressing with Y cleared. That saves a whole set of opcodes.
Also, Rockwell introduced them as (ZP) with their 6500 line, later available with the 65C02.
Similarly, what are the design choices [...] for, especially indexed indirect [...] often touted as "rarely-used [...]
True, but it becomes useful when a table of pointers set according to machine state is to be worked on (*2). Again, think state machines, or managing a list of buffers, one for each port and direction.
Not to mention, that it not only adds another addressing mode, but enables, as a side effect, the use of (ZP,X) with X cleared as (ZP) - like before with (ZP),Y. That way either register can be 'used' for non indexed indirect while still having two different modes.
It seems much more useful to be able to use indirect indexed, i.e. ($hh),Y, with both the X and Y registers instead of only Y.
That function is already available as (ZP),X, so no need to waste opcodes doing the same (less common) operation on Y as well. Keep in mind, 6502 registers are specialized.
*1 - Like 16-bit operations and having two fully functional accumulators.
*2 - Again, think embedded operation.