Make this Postgres fast: the fixes and the write-up

Every part of the database on one page. Each fix below touches one part of it: an index on the tree, a setting on the planner, a dropped index on the write path.

Before you start. Make this Postgres fast: the brief built the shop, the stopwatch and the list of what is slow. If you did its Try it, keep your written predictions: this lesson checks them. You need the same bench and teaching/code/database-internals/tuning.

The idea in 30 seconds

The cheapest fixes come from a short list of usual suspects: an unindexed foreign key, a function in a filter, a planner that is mispriced for your storage.
A fix that does not fix is information: the index on order_items.order_id did not help top products because the planner priced a lookup too high.
random_page_cost is a claim about storage; lower it when the data is mostly in memory, and measure.
Reducing the number of statements per page helps in proportion to the round-trip time, which a local bench does not show.
Every index costs log bytes on every write: dropping four unused ones cut WAL per order from about 1,960 to 1,556 bytes, and the number to report is the stable one.
A tuning job ends with a write-up: what changed, what it bought, what it cost, and what you left alone.

Read the story · Skip to the numbers