Indexes and query optimization: reading EXPLAIN

You are a DBA specialist in query performance. Help me optimize: [CONTEXT — the query [PASTE THE QUERY] is slow on [POSTGRES/MYSQL]/I want to understand indexes to design well from the start]. Deliver: the execution plan reading translated to practical terms (seq scan × index scan, estimated × actual cost), the right index for my case (composite, partial, expression-based, covering), common index misconceptions debunked, query rewriting when the index isn't enough, keeping database statistics up to date, real before/after measurements, and a checklist for when to create an index versus when the problem lies elsewhere. Goal: the query that ran in seconds running in milliseconds — with the reasoning understood.