Where query time goes

When a query is slow, it is tempting to add an index and hope. Sometimes that works. Often it does nothing, or makes writes slower without helping reads. Effective tuning starts with understanding where time actually goes.

The life of a query

When an application sends a query, PostgreSQL:

1. Parses the SQL into a tree.
2. Rewrites it, for example expanding views.
3. Plans it: considers many possible ways to execute it and estimates the cost of each, choosing the cheapest.
4. Executes the chosen plan, reading data from memory or disk, joining, sorting and aggregating.
5. Returns rows to the client over the network.

For most slow queries, the time is in execution, and it comes from reading too much data, doing expensive operations such as large sorts, or choosing a poor plan because of bad estimates.

Common causes of slowness

• Reading far more rows than needed, often a sequential scan of a large table for a query that returns a few rows.
• Bad row estimates that lead the planner to choose the wrong join method or order.
• Sorts and hashes that spill to disk because they need more memory than allowed.
• Repeated work, such as running the same query hundreds of times in a loop from application code.
• Waiting rather than working, on locks held by other transactions or on a saturated connection pool.
• Returning too much data, such as fetching ten thousand rows to display twenty.

Each cause has different fixes. That is why diagnosis comes before any change.

Measure first

Before tuning, decide what you are measuring and record a baseline:

• Which query, with which parameters?
• How long does it take, and how often does it run?
• How much does it read from memory and disk?

Then change one thing at a time and measure again. Performance work without measurement turns into guesswork, and guesses often make things worse.

Total cost matters more than single runs

A query that takes 2 seconds once a day matters less than a query that takes 20 milliseconds but runs 50,000 times an hour. Focus on total time, which is average duration multiplied by the number of calls. In section 2, pg_stat_statements gives you exactly this view.

The dataset

Run the provided script to create the course database: customers, products, orders and order items, with several million order items spread over a few years. Run ANALYZE afterwards so statistics are up to date. All examples in this course use it, so your results should be close to the ones shown, though timing depends on your machine.