How to understand Execution Plans in PostgreSQL

Hi Guys,

In this series of Blogs, I'm going to explain about the execution plan in PostgreSQL.How it works and how performed scans and Joins on the statement by Optimizer.

Execution plans can become a very beneficial weapon for all database developer. They give a broader knowledge of the mechanisms involved in the processing of queries. This blog will get a closer look at how execution plans are retrieved and how to read them.

What Are Execution Plans?

SQL is, to a great measure, a declarative language. The user specifies what should be done but does not define how the queries should be performed. There are various ways in which particular parts of an SQL statement can be processed. For example, predicates can be measured in any sequence and subqueries can be changed into joins if required.

The query planner tries to determine the common productive way to perform a query based on their expected costs and builds an execution plan. This plan is executed step-by-step by the database. when you come across remarks which perform slowly in your database, the execution plan should be the starting point for your investigation. Retrieving the Query Execution Plan. The execution plan of a query can be recovered utilizing the keyword EXPLAIN in front of a Query,

As in the following example:

EXPLAIN SELECT * FROM user;

As an outcome of the above statement, the operation will print the execution plan:

                      QUERY PLAN
----------------------------------------------------------
Seq Scan on users  (cost=0.00..5.24 rows=234 width=41)

In the above case, the simple form of EXPLAIN is used, which means that the statement is not performed and we only get a probability of what is possible to happen. When we will be using ANALYZE keyword which does really run the query and which prints more specific information to the output, for example:

EXPLAIN ANALYZE SELECT * FROM users;

 
                        QUERY PLAN
-----------------------------------------------------------
Seq Scan on users (cost=0.00..5.21 rows=173 width=118)
         (actual time=0.018..0.018 rows=0 loops=1)
 Total runtime: 0.020 ms

A new line emerged in the output, describing the actual time consumed in the execution of the query as well as the number of rows recovered. EXPLAIN is most generally run on SELECT queries, but it is possible to implement it to any INSERT, UPDATE, DELETE, EXECUTE or DECLARE article. Your queries will be performed when you use ANALYZE. This means that an INSERT, UPDATE, DELETE or EXECUTE clause will modify your data. To bypass this, you can wrap your queries in a transaction,

for example:

BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;

How to Interpret an Execution Plan?

The execution plan gives data about the particular plans accepted by the query optimizer. Let’s examine some typical examples of its usage.

=> EXPLAIN SELECT * FROM users;
 
                       QUERY PLAN
---------------------------------------------------------
 Seq Scan on users (cost=0.00..5.24 rows=213 width=41)

The above output shows that a complete table scan has been performed on the table users. Four numbers are then defined in the braces with the following meaning:

The two cost values define the startup cost and the total cost, individually. By default, the cost variables are included with the cost of a sequential page fetch and the extra cost variables are set with the source to that. The startup cost details the amount of work spent before output scan can begin. The total cost explains the expected cost if all rows were to be recovered. The rows value determines the number of rows output by the plan is executed to finish. The width value represents the expected average width of rows in bytes. The two major decisions which the optimizer has to make are the selection of a scan method and the selection of a join method.

In the system, PostgreSQL makes use of four distinct scan techniques and three join methods.

Scan Methods :

1.Sequential Scan

2.Index Scan

3.Index Only Scan

4.Bitmap Index Scan + Recheck + Bitmap Heap Scan

Join Methods :

1.Nested Loop

2.Hash Join

3.Merge Join

In the next article, we will discuss how the optimizer will use scans and joins methods.

Thanks 
Sanjay Saini