What is a query execution plan and how to use it for optimization?

A query execution plan is a detailed description of how a database management system (e.g., PostgreSQL, MySQL, Oracle) intends to execute an SQL query. It shows which indexes will be used, in what order tables will be joined, and what filtering and sorting operations will be applied.

Using an execution plan helps optimize queries by identifying bottlenecks: for example, full table scans instead of index usage, redundant joins, or inefficient sorting operations.

To obtain an execution plan in PostgreSQL, the EXPLAIN command is used:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

This will show the actual execution time and the number of rows at each step. By analyzing the plan, one can add indexes, rewrite the query, or change the data structure to improve performance.