Answer.

Background:
From the very beginning, SQL queries were designed to be declarative: "what to get", not "how to get it". The DBMS optimizer builds the execution plan, which defines the order of joins, filters, scans, and index usage.

Problem:
Without understanding the execution plan, it is impossible to explain why a seemingly simple query runs too slowly while a complex one runs quickly. An incorrect plan can lock up the server for hours due to unnecessary operations or improper use of indexes.

Solution:
Analysis tools include instructions like EXPLAIN, EXPLAIN ANALYZE (PostgreSQL), SHOW EXPLAIN (MySQL), Management Studio Execution Plan (SQL Server). These tools allow you to see step-by-step how the query was executed, which volume of data was read, which index was used, where full table scans occurred, and where delays arose.

Example code:

EXPLAIN ANALYZE SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id WHERE o.status = 'shipped';

Key features:

The plan will provide a detailed scheme of which operation was performed in what order by the DBMS.
You can see where an index scan was used versus a table scan.
It helps to identify bottlenecks for optimization and to test hypotheses about the impact of rewriting the query on performance.

Tricky Questions.

Will adding an index always speed up the query?

No! An index will only help if filtering on its field significantly reduces the number of rows returned. If most records meet the condition, the optimizer may ignore the index.

Example:

-- The field 'gender' has only two values — the index won’t help
CREATE INDEX idx_gender ON people(gender);
SELECT * FROM people WHERE gender = 'M';

Does the order of tables in JOIN affect the result of execution?

No, the final data will be the same; however, the optimizer may rearrange the order of joins to improve performance. But if a specific JOIN is written or hints like "JOIN HINT" are used, the order may affect execution efficiency.

Why analyze "Estimated rows" and "Actual rows" in the execution plan?

The difference between them may indicate that statistics about the tables are outdated or inaccurate, and the chosen plan is suboptimal — statistics need to be updated or the query structure reconsidered.

-- PostgreSQL
ANALYZE table_name; -- update statistics

Common Mistakes and Anti-Patterns

Execution plans are not reviewed when issues arise: attempts are made to optimize "blindly"
Adding unnecessary/irrelevant indexes for "speeding up", which only slows down data changes
Statistics on tables are not updated
Queries are designed without consideration of how the optimizer will execute them

Real-Life Example

Negative Case

On a project, analysts complained for a long time about the "hanging" reports. A query with five JOINs took 25 minutes to execute. It turned out that a plan was chosen that involved full scanning of a huge table instead of an index scan, the indexes were on the wrong fields, and the statistics had not been updated for a year.

Pros:

Simple maintenance, minimal indexes

Cons:

The query ran slowly, wasting hours of human labor
User dissatisfaction
Server load

Positive Case

An execution plan analysis was conducted, an index was added on a truly filtering field, and statistics were updated. Query time decreased to 20 seconds. Server load was reduced by an order of magnitude.