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, inserts, and deletes. 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 used in the query.
Syntax and Parameters
Below is the syntax of explaining analyze in PostgreSQL are 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: Which 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 true.
- 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 which output format of explaining analyze statement in PostgreSQL used. 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 parameter 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 explain analyze to find the plan of SQL statement in PostgreSQL. Explain analysis is critical in PostgreSQL to find the best execution plan of a query.
- Explain analysis is a PostgreSQL command that accepts the SQL statement such as selecting, inserts or updating, etc. When the statements are executed, a thorough query will be given after executing the statement instead of returning the data as to what method the planner has taken to execute the SQL statement received.
- PostgreSQL explains analysis will build the SQL statement plan, including which action will be performed on the query and which scan 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 statement.
- The sort node is the main node in the tree structure; it includes information on which algorithm is used to sort the SQL statement. Also, it will contain information of whether sort was done in disk or in memory and the amount of disk and memory space needed.
Examples to Implement EXPLAIN ANALYZE in PostgreSQL
We have using the Employee1 table to describe the example of explaining analyze in PostgreSQL are 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
This is a guide to EXPLAIN ANALYZE in PostgreSQL. Here we discuss the parameters, how it works and examples to implement EXPLAIN ANALYZE in PostgreSQL with proper codes and output. You can also go through our other related articles to learn more –
