What specific indexes would you create for an optimized version to support those joins and filters?

For joins, create indexes on the foreign key columns used in the JOIN predicates. For example, if you join orders.customer_id to customers.id, add an index on orders.customer_id and ensure customers.id is a primary key. For filters, index the columns used in WHERE clauses. If you filter by multiple columns, consider a composite index that matches the filter order. For example, WHERE status = 'active' AND created_at > '2023-01-01' benefits from an index on (status, created_at). If the query also selects a few columns, a covering index that includes those columns can eliminate lookups. Use partial indexes for predicates that are frequently true, e.g., CREATE INDEX ON orders (customer_id) WHERE status = 'active'. Finally, analyze the query plan to confirm the index is used and adjust if necessary.