What methods are used to evaluate the effectiveness and optimize SQL queries?

The following methods are used to evaluate the efficiency and optimize SQL queries:

• EXPLAIN / EXPLAIN ANALYZE — shows the query execution plan, helps identify bottlenecks.
• Query profiling — measuring execution time and resource usage.
• Indexing — checking if indexes are used and creating new ones if necessary.
• JOIN optimization — simplifying or changing the order of joins.
• **Avoiding SELECT *** — selecting only the necessary columns.
• Caching results — if data does not change often.

Example of using EXPLAIN in PostgreSQL:

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;