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) ...