How can inefficient SQL queries be detected and optimized?

The following approaches are usually used to detect inefficient SQL queries:

• Using database profilers and logs (e.g., EXPLAIN in PostgreSQL or MySQL) to analyze query execution plans.
• Monitoring query execution times and identifying slow ones.
• Analyzing indexes and their usage.

Optimization may include:

• Adding or adjusting indexes.
• Rewriting queries to reduce the number of JOINs or subqueries.
• Using aggregate functions and grouping more efficiently.
• Caching results of frequently executed queries.

Example of using EXPLAIN in PostgreSQL:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

This will show how the database executes the query and where bottlenecks may occur.