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

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.