EasyEnv
Engineering/Courses/Catalog/PostgreSQL/EXPLAIN and Query Tuning
PostgreSQL
IntermediateLesson 12 of 1435 min · hands-on

EXPLAIN and Query Tuning

Part of the PostgreSQL course

A page that used to load instantly now takes nine seconds, and the instinct is to start guessing. Add an index here. Rewrite the join. Maybe it is the ORDER BY. Guessing is a bad way to tune a database, and the reason it is unnecessary is that Postgres will simply tell you what it plans to do and, if you ask nicely, what it actually did and how long each part took.

The last part of this lesson uses pg_stat_statements, an extension the server can only load when it starts. Open the terminal, turn it on and restart Postgres, then give this lesson a fresh database of its own and connect to it as postgres (lesson 02 explains the sudo -u postgres) ...

Want to learn PostgreSQL EXPLAIN and Query Tuning in a real environment?

Click below, get a fresh Linux box with PostgreSQL ready to go, and work through EXPLAIN and Query Tuning hands-on with an AI tutor that knows your code.

Start this lessonEXPLAIN and Query Tuning

What you'll learn

  • EXPLAIN
  • EXPLAIN ANALYZE
  • Seq scan vs index scan vs index-only scan
  • Fixing a missing index
  • Pg_stat_statements overview

Read this lesson in your language

All lessons in this course

14 lessons
  1. 1What is PostgreSQL20 min
  2. 2psql and First Commands25 min
  3. 3Creating Databases and Tables30 min
  4. 4Data Types25 min
  5. 5SELECT and WHERE30 min
  6. 6Joins35 min
  7. 7Aggregates and Grouping30 min
  8. 8Indexes35 min
  9. 9Transactions and Isolation35 min
  10. 10Functions and Triggers35 min
  11. 11JSONB and Arrays35 min
  12. 12EXPLAIN and Query Tuning35 min
  13. 13Backup and Restore25 min
  14. 14Capstone: Design a Schema60 min

Explore other courses

EXPLAIN and Query Tuning - PostgreSQL (English) | Engineering Courses