OPTIMIZING POSTGRES DATABASES > MISSION 2 > DEBUGGING QUERIES

Debugging Postgres Queries

In the previous lesson, we learned about schemas in Postgres databases and built a database description from scratch. Using internal tables, we were able to describe metadata associated with our Postgres database. The metadata included data types, table names, created schemas, and plenty of other useful information.

In this lesson, we will be using internal tables to describe Postgres operations. However, instead of tables and data types, we will be examining queries and their plans of execution. Using Postgres tooling, we will introduce basic techniques for debugging Postgres queries that will also be used in future lessons.

Throughout this lesson, you will learn about concepts such as the `EXPLAIN` query that can help in debugging Postgres queries. You will also add the `ANALYZE` option to force runtime statistics and have those returned to you, to further aid in debugging queries.

As you work through debugging Postgres queries, you’ll get to apply what you’ve learned from within your browser so that there's no need to use your own machine to do the exercises. The Python environment inside of this course includes answer checking so you can ensure that you've fully mastered each concept before moving on to the next one.

Objectives

  • Learn how to debug Postgres queries.
  • Learn how to measure estimated and actual execution times.

Mission Outline

1. The EXPLAIN Query
2. The Path of a Query
3. Additional Output Formats
4. Describing EXPLAIN Output
5. Adding the ANALYZE Option
6. Test and Rollback
7. ANALYZE a Join Statement
8. Join on Sequence Sacans
9. Next Steps
10. Takeaways

optimizing-postgres-databases-data-engineering

Course Info:

Intermediate

The median completion time for this course is 4.7 hours. ​View Details​​​

This course is free and includes five missions. It is the second course in the Data Engineer path.

START LEARNING FREE

Take a Look Inside