Analyze the provided PostgreSQL query which performs a recursive CTE operation on a table with 50 million rows. The current execution plan shows a Nested Loop that is causing severe performance degradation. Rewrite the query to eliminate the recursive CTE by using set-based operations or window functions. Additionally, recommend the exact indexing strategy required to support the new query structure, specifying index types (e.g., B-Tree, GIN) and the columns to include.