EXPLAIN ANALYZE in PostgreSQL

Updated May 4, 2023

What is EXPLAIN ANALYZE in PostgreSQL?

Explain Analyze in PostgreSQL is used to understand and optimize the query. Explain analysis is a PostgreSQL command that accepts statements such as select, update, insert, and delete. In PostgreSQL, explain analyze executes the statement, but instead of returning data, it will provide an execution plan of a query. Explain analysis is critical in PostgreSQL to optimize the query; it will show how the table is involved in the SQL statement and will be scanned by index scan or sequential scan, etc.; also, it shows what kind of join is used in the query.

Syntax and Parameters

Below is the syntax for explaining analyze in PostgreSQL as follows.

EXPLAIN [ ANALYZE ] [ VERBOSE ] SQL statement
EXPLAIN [ ( option(Option to be define in plan of SQL statement)) ] SQL statement

Where option in explains analyze can be one of the following:

• ANALYZE [ boolean ]
• VERBOSE [boolean ]
• COSTS [ boolean ]
• BUFFERS [ boolean ]
• TIMING [ boolean ]
• SUMMARY [ boolean ]
• FORMAT (Format defined to display the output){ TEXT OR XML OR JSON OR YAML }

Parameters

Below is the parameter description of the above syntax:

• Explain: Explain the keyword used to understand the plan of the SQL statement.
• Analyze: Analyze shows the running time of the SQL statements. The default value of analyzing parameter in explain analyze is false.
• Verbose: Verbose is used to display a more descriptive output of the plan of a SQL statement. The default value of the verbose parameter is false.
• SQL statement: This SQL statement is used to define, explain analyze the plan of the query.
• Costs: It will show the total costs of SQL statements. The default value of the costs parameter is valid.
• Buffers: This will display information on the buffer used by the query. The default value of the buffers parameter is false.
• Format: It will display the output format of explaining the analysis statement in PostgreSQL. The default value of the format parameter is text.
• Boolean: Boolean specifies that we have selected which option to explain the analysis. The Boolean option is ON and OFF.
• Summary: This parameter will add summary information, such as total timing after the query plan of the SQL statement
• Timing: Timing parameters include the actual startup time of the SQL statement and time spent in each node in the output.

How EXPLAIN ANALYZE works in PostgreSQL?

Below is the working of explaining analyze in PostgreSQL are as follows.

• We have used explained analyze to find the plan of SQL statement in PostgreSQL. Explain analysis is critical in PostgreSQL to find the best execution plan for a query.
• Explain analysis is a PostgreSQL command that accepts SQL statements such as selecting, inserting, updating, etc. After executing the statements, the planner will provide information on the method used to execute the SQL statement instead of returning the data. This information is given in the form of a specific query.
• PostgreSQL explains analysis will build the SQL statement plan, including which action will be performed on the query and which scan will be used to scan the query.
• In many statements, explain analyze will provide more descriptive execution statistics of a query.
• Explain analyze have a tree structure plan of the SQL statement.
• The above tree structure hash node will contain the information of several hash buckets and batches and peak memory uses of SQL statements.
• The sort node is the main node in the tree structure; it includes information on which algorithm is used to sort the SQL statement. The output will also include information on whether the sort was performed in memory or on disk and the amount of disk and memory space required.

Examples to Implement EXPLAIN ANALYZE in PostgreSQL

Using the Employee1 table to describe the example of explaining analyze in PostgreSQL is as follows.

1. Table – Employee

1. Create a table name as Employee1 to describe an example of explaining analyze:

Code:

CREATE TABLE Employee1 (emp_id INT NOT NULL, emp_name character(10) NOT NULL, emp_address character(20) NOT NULL, emp_phone character(14), emp_salary INT NOT NULL, date_of_joining date NOT NULL);

2. Insert data into it.

Code:

INSERT INTO Employee1 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (1, 'ABC', 'Pune', '1234567890', 20000, '01-01-2020');
INSERT INTO Employee1 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (2, 'PQR', 'Pune', '1234567890', 20000, '01-01-2020');
INSERT INTO Employee1 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (3, 'XYZ', 'Mumbai', '1234567890', 35000, '02-01-2020');
INSERT INTO Employee1 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (5, 'RBS', 'Delhi', '1234567890', 50000, '03-01-2020');
INSERT INTO Employee1 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (6, 'ABS', 'Delhi', '1234567890', 25000, '02-25-2020');
INSERT INTO Employee1 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (7, 'SBS', 'Delhi', '1234567890', 55000, '02-26-2020');

3. Check the data of the Employee1 table.

Code:

select * from Employee1;

Output:

2. Using Select Statement

Code:

EXPLAIN ANALYZE select * from Employee1 where emp_id = 1 and emp_address = 'PUNE';

Output:

3. Using Insert Statement

Code:

EXPLAIN ANALYZE INSERT INTO Employee1 (emp_id, emp_name, emp_address, emp_phone, emp_salary, date_of_joining) VALUES (4, 'BBS', 'Mumbai', '1234567890', 45000, '02-01-2020');

Output:

4. Using Delete Statement

Code:

EXPLAIN ANALYZE delete from Employee1 where emp_id = 3 and emp_address = 'PUNE';

Output:

5. Using Update Statement

Code:

EXPLAIN ANALYZE update employee set emp_id = '8' where emp_id = '1';

Output:

Conclusion

Explain analyze is very important in PostgreSQL to describe the plan of the SQL statement. A PostgreSQL command that accepts the statements such as select, update, insert and delete executes the statement. Instead of returning data, it will provide an execution plan of a query.

Recommended Articles

We hope that this EDUCBA information on “EXPLAIN ANALYZE in PostgreSQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. Indexes in PostgreSQL
2. GROUP BY PostgreSQL
3. PostgreSQL Triggers
4. PostgreSQL Operators