PostgreSQL Execution Plan (Explain)

In today’s article, we will be talking about how to use the EXPLAIN concept and its types to understand and optimize SQL queries.

In Database Systems, each query has its own Execution Plan.

We can also call the Execution Plan the life cycle of that query.

The Explain command helps us a lot when examining our written queries.

If we write explain at the beginning of the queries, it gives us the execution plan of that query.

Some of the parameters related to Explain:

ANALYZE, VERBOSE, COSTS, BUFFERS, TIMING, SUMMARY, FORMAT { TEXT | XML | JSON | YAML }

ANALYZE: It presents information to the user such as the number of rows returned from the query, the total elapsed time and the running time.

VERBOSE: It is used to give more detailed information about the execution plan.

COSTS: It is used to obtain the lengths and total costs of the affected rows at each stage of the execution plan.

BUFFERS: Can be used with the ANALYZE parameter.

TIMING: This parameter is used to show the start time, end time and how much time is spent on each node.

SUMMARY: This parameter adds summary information, such as total timing, to the query plan. If ANALYZE is used, it includes summary information by default.

FORMAT: The query plan is used to specify the output format. Output can be produced in TEXT, XML, JSON, YAML formats.