Tell about your experience optimizing complex SQL queries and working with execution plans. Was there a case where you managed to speed up a query 2-3 times?

In one of my projects, I encountered a slow report generated by a complex SQL query with multiple JOINs and subqueries. To optimize it:

• I analyzed the execution plan (EXPLAIN), identified full table scans instead of index usage.
• Added missing indexes on columns involved in JOIN and WHERE conditions.
• Rewrote subqueries as JOINs to allow the database to optimize execution better.
• Used aggregate functions and grouping earlier in the process to reduce data volume.

As a result, I managed to reduce the query execution time from 15 seconds to 5 seconds — a 3-fold speedup. This approach demonstrated that deep analysis of the execution plan and proper indexing are critical for performance.