I think adding the index to the “seven” table is probably the more meaningful change.
You said the index was ignored, but based on that plan output the planner made the right choice since the filter on the “sierra_zulu” table (only one there with an ANY so I assume that’s the one) matches over 90% of the rows. An index scan on that would be far worse.
Adding an index on a different table allowed the driving side of the join to be different, and, with already prefiltered data allowed the other index to be useful as well (fewer index lookups). I’d bet with that index addition your IN clause works just fine.
If you look at sierra_zulu (line #3 on both plans) it took almost 22 seconds initially and after query rewrite (and no index change) took 713ms.
So that change of query alone reduced query time from 35s to ~5s, after that the seven table become the bottleneck and adding an index there reduced the whole query down to 750ms.
I should have saved the intermediate plan to illustrate it, but it essentially looked same as the second plan, except there was a sequence scan on seven table.
You said the index was ignored, but based on that plan output the planner made the right choice since the filter on the “sierra_zulu” table (only one there with an ANY so I assume that’s the one) matches over 90% of the rows. An index scan on that would be far worse.
Adding an index on a different table allowed the driving side of the join to be different, and, with already prefiltered data allowed the other index to be useful as well (fewer index lookups). I’d bet with that index addition your IN clause works just fine.