Given a hypothetical poorly performing SQL query that selects data from five tables (Orders, Customers, Products, Shipments, and Reviews) using several nested correlated subqueries and wildcard selects in a WHERE clause, rewrite the query for maximum performance on a PostgreSQL database. Assume the tables contain millions of rows. Explain your optimization steps, including: index usage strategies, replacing subqueries with JOINs or CTEs (Common Table Expressions), eliminating SELECT *, and utilizing window functions if applicable. Provide the final optimized SQL code and a brief explanation of the execution plan improvements.