EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home Data Science Data Science Tutorials PostgreSQL Tutorial EXPLAIN ANALYZE in PostgreSQL

EXPLAIN ANALYZE in PostgreSQL

Priya Pedamkar
Article byPriya Pedamkar

Updated May 4, 2023

EXPLAIN ANALYZE in PostgreSQL

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.

ADVERTISEMENT
Popular Course in this category
POSTGRESQL Course Bundle - 5 Courses in 1 | 1 Mock Test

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

EXPLAIN ANALYZE in PostgreSQL - 1

2. Using Select Statement

Code:

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

Output:

Select Statement

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:

EXPLAIN ANALYZE in PostgreSQL - 3

4. Using Delete Statement

Code:

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

Output:

Delete Statement

5. Using Update Statement

Code:

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

Output:

Update Statement

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
ADVERTISEMENT
PROGRAMMING LANGUAGES Course Bundle - 54 Courses in 1 | 4 Mock Tests
338+ Hours of HD Videos
54 Courses
4 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
SELENIUM Course Bundle - 15 Courses in 1 | 9 Mock Tests
39+ Hours of HD Videos
15 Courses
9 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
IOT System Course Bundle - 7 Courses in 1
43+ Hours of HD Videos
7 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
JENKINS Course Bundle - 6 Courses in 1
15+ Hour of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW