PostgreSQL query tuning: an eight-case bench

The task

“The report takes ten seconds to open”, “the orders page crawls by the end of the day” - that is how most tuning work starts. What usually follows is an argument with no evidence: add indexes, or buy a bigger server. This bench answers differently. Eight common heavy queries, each shown with the plan before the fix, the change itself, the plan after and a measured time. You see not just “it got faster”, but what made it faster.

The project is a demo: the dataset is synthetic and generated by a script, with not a single row of anyone’s real data.

The solution

An online shop dataset: 120,000 customers, 900,000 orders, 1,800,000 order lines. Generation uses a fixed random seed, so the size and the distribution of the dataset repeat on any machine. PostgreSQL 17 comes up in a container with default settings - no tuned parameters that a production server would not have.

The eight cases cover the usual suspects: a customer’s orders with no indexes at all, a three-table join, a monthly report, a window function spilling its sort to disk, a deep page via LIMIT 20 OFFSET 200000 and the same page via a cursor, plus the two cases that in my experience show up most often: the index is already there, but the planner will not use it, because the left side of the comparison is not a column but a function over it.

The measurement procedure is identical everywhere. Before each case all indexes except primary keys are dropped and the “as it was” state is restored. Then three warm-up runs and nine scored runs per side; the two fastest go into the report. That is deliberate: measurements are taken on a working laptop with other processes alive next door, and a single run easily catches someone else’s load. The rule is the same for the before side and the after side, so the comparison stays fair.

Numbers from one laptop run: a customer’s orders by email - 50.2 ms against 0.144 ms, 349 times faster; a case-insensitive customer lookup by email - 29.5 against 0.052 ms, 567 times; the twelve-month report - 65.3 against 27.0 ms, only 2.4 times. Not every win is three digits, and the run summary shows all eight rows at once.

Details that are easy to miss

• Two cases out of eight are fixed with no DDL at all. In one, the condition created_at::date = ... is rewritten as a date range and the index that was already there finally starts working: 37.4 ms against 3.6 ms. In the other, the deep page moves from OFFSET to a cursor with the very same index: 21.4 ms against 0.067 ms.
• A rewritten query has to return the same thing, and the bench checks that inside the same run. The checks are not equally strong, and it is fairer to say so plainly: for the cursor page the row ids themselves are compared, 20 out of 20 matched; for the type-cast query only the row count of the two forms is compared so far.
• Time is not the only metric. The bench also prints the decisive plan node: in four cases a sequential table scan turns into an Index Only Scan, in the cursor case the number of rows read drops from 200,020 to 20, and an external sort on disk (18,080 kB) simply disappears from the plan.
• Absolute values are machine-dependent: they hang on the CPU, the disk and whatever else the machine is doing. While debugging this same bench under laptop load, the same queries came out 3-5 times slower. The bench documentation says so outright, together with the rule: on a repeat, use your own run’s numbers, not these.

What is left

The run captures were taken in separate sittings, and the numbers between them do not line up: the same case shows different timings on two pictures. Both numbers are genuine, and spread between runs on a laptop is ordinary, but in one set it reads as inconsistent, so there are no captures on this page yet. They all need a reshoot from a single run; until then the card leans on the bench itself, which reproduces all eight cases with one command.