Have you ever optimized SQL queries? How do you use EXPLAIN ANALYZE?

SQL query optimization is an important step in improving application performance. I usually start by analyzing the execution plan of the query using EXPLAIN ANALYZE. This command not only shows how the database plans to execute the query but also the actual time taken for each step.

With EXPLAIN ANALYZE, you can identify bottlenecks: for example, full table scans (Seq Scan), lack of indexes, expensive sorting or join operations. After that, I try adding indexes, rewriting the query, using aggregations, or limiting the selection.

An example of usage in PostgreSQL:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 123;

The output will show whether the query uses an index on customer_id or performs a full table scan. This helps to understand where exactly to optimize.