Skip to content
Bang for Buck: Scaling PHP Apps

Chapter 06 · Intermediate

Make the Remaining SQL Cheaper

Read a PostgreSQL query plan, build one index for the shop’s order history, and measure what it saves on reads and costs on writes.

The shop’s order history runs one SQL query and returns 20 orders. PostgreSQL still scans a table with 200,000 historical orders and sorts the matches to find that page. You’ll read the plan, add an index that delivers the rows in the requested order, and check whether the faster read is worth the storage and write work.

Start the Compose shop from Set a Baseline for a Laravel App. The benchmark uses the SQL timings from Profile a Slow Laravel Request. Run the commands from code/bang-for-buck against your local lab. Allow about 30 minutes.

Rebuild the app to pick up the order history route. The fixture adds 200,000 orders across 500 products, keeps any orders already in the lab, and refreshes the planner’s table statistics. You can run it again without adding duplicate history.

Terminal window
docker compose build php
docker compose up -d --wait --force-recreate php web worker shipping
curl -fsS --retry 5 --retry-all-errors --retry-delay 1 "http://127.0.0.1:${SHOP_PORT:-8088}/api/lab"
docker compose exec -T db psql -X --set=ON_ERROR_STOP=1 -U shop -d shop < bench/orders-fixture.sql

Use one product and a fixed date range throughout. from includes midnight on 1 April, while before excludes midnight on 1 June. Both dates are UTC. Checkouts create orders at the current time, so they exercise writes to the same table without changing the history page during the test.

Terminal window
orders_url="http://127.0.0.1:${SHOP_PORT:-8088}/api/order-history?product_id=42&from=2025-04-01&before=2025-06-01"
comparison_dir=$(mktemp -d)

Start with the experiment index absent. The existing primary key and the unique index on (run_id, reference) still support order identity and retry handling.

Terminal window
bash bench/orders-index.sh off
curl -fsS "$orders_url" > "$comparison_dir/before.json"
docker compose exec -T db psql -X --set=ON_ERROR_STOP=1 -U shop -d shop < bench/orders-plan.sql

The file runs the same query as the Laravel route, with literal values for the three request parameters.

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT id, reference, product_id, total_cents, created_at
FROM orders
WHERE product_id = 42
AND created_at >= TIMESTAMPTZ '2025-04-01 00:00:00+00'
AND created_at < TIMESTAMPTZ '2025-06-01 00:00:00+00'
ORDER BY created_at DESC, id DESC
LIMIT 20;

Read the indented nodes from the bottom up. Each parent asks its children for rows. cost is the planner’s estimate in relative units. The plain rows estimate is how many rows a node expects to emit. actual rows with loops tells you how many rows it emitted per execution, averaged across executions. ANALYZE runs the statement. TIMING OFF skips per-node clocks while retaining row counts and total execution time. PostgreSQL on reading EXPLAIN · EXPLAIN options

Limit (cost=5794.80..5794.85 rows=20 width=42) (actual rows=20.00 loops=1)
Buffers: shared hit=2708
-> Sort ... (actual rows=20.00 loops=1)
Sort Method: top-N heapsort Memory: 27kB
-> Gather ... (actual rows=140.00 loops=1)
-> Parallel Seq Scan on orders ... (actual rows=70.00 loops=2)
Rows Removed by Filter: 100314

In the local baseline, follow the rows through three stages.

  1. Parallel Seq Scan on orders scanned the table and applied the product and time filters. The actual rows=70.00 loops=2 line is about 140 matches in total. Rows Removed by Filter is also averaged per loop, so the scan discarded about 200,628 rows.
  2. Gather collected the 140 matches. Sort used a top-N heapsort to keep the best 20 by timestamp and ID. PostgreSQL still had to examine all 140 matches to choose them.
  3. Limit returned 20 rows. Read its Buffers line for the execution’s shared-buffer touches. shared hit means PostgreSQL already had the page in shared buffers. A parent includes its children’s counts, so adding the lines would count the same touches again.

Step 2 – Build the index around this query

Section titled “Step 2 – Build the index around this query”

Put product_id first to find one product’s portion of the B-tree. Within that portion, PostgreSQL can seek to the time range and read entries in the order the query needs. id breaks ties when timestamps match. An ordered scan can stop as soon as it has found 20 visible matching rows. PostgreSQL multicolumn indexes · Indexes and ORDER BY

CREATE INDEX orders_product_recent_idx
ON orders (product_id, created_at DESC, id DESC);

The script applies that definition to the local database. Run the same plan again and compare the response bodies.

Terminal window
bash bench/orders-index.sh on
docker compose exec -T db psql -X --set=ON_ERROR_STOP=1 -U shop -d shop < bench/orders-plan.sql
curl -fsS "$orders_url" > "$comparison_dir/after.json"
cmp "$comparison_dir/before.json" "$comparison_dir/after.json"

The indexed plan is Limit → Index Scan using orders_product_recent_idx. Look for the product and both date bounds in Index Cond and 20 rows emitted by the scan. The index emits rows in order, so PostgreSQL skips the sort. This is a regular index scan, so PostgreSQL still visits the table for values such as reference and total_cents. A sequential scan can still be the cheapest plan for a query that needs much of the table.

Limit (cost=0.42..80.24 rows=20 width=42) (actual rows=20.00 loops=1)
Buffers: shared hit=26
-> Index Scan using orders_product_recent_idx on orders ... (actual rows=20.00 loops=1)
Index Cond: ((product_id = 42) AND (created_at >= ...) AND (created_at < ...))

On a live orders table, a plain CREATE INDEX blocks writes during the build. PostgreSQL offers CREATE INDEX CONCURRENTLY to allow ongoing writes. It does more work, takes longer and must run outside a transaction block. The lab builds and drops its experiment index between load runs. PostgreSQL index builds

Run a pair of tests with the same warm-up, rate and duration on both sides. Keep the seed and container limits fixed. Repeat the comparison, alternating which case comes first to reduce order effects.

Terminal window
(
set -e
for mode in off on; do
bash bench/orders-index.sh "$mode"
curl -fsS "$orders_url" > /dev/null
WORKLOAD=orders RATE=40 DURATION_SECONDS=8 bash bench/run.sh steady
# Warm up the insert probe, then record the second plan for comparison.
docker compose exec -T db psql -X --set=ON_ERROR_STOP=1 -U shop -d shop < bench/orders-write-probe.sql > /dev/null
docker compose exec -T db psql -X --set=ON_ERROR_STOP=1 -U shop -d shop < bench/orders-write-probe.sql
done
)

The harness schedules four history reads for each checkout and checks the rows, their order and the number of SQL queries. Each checkout goes through the usual shipping quote and saves a real order in the lab. Compare shop_order_history_ms and shop_order_ms for HTTP p95, and shop_order_history_sql_ms and shop_order_sql_ms for median SQL time. These Laravel trace timings include driver and database round-trip time. Check persisted.json against the number of confirmed orders and reject a run with failures or dropped iterations.

The SQL write probe measures a separate batch of 1,000 inserts into orders. Look for Tuples Inserted: 1000, execution time and WAL bytes. Write-ahead log bytes show how much log the statement generates. Compare repeat runs because full-page images and page splits can move that number. The probe executes the insert inside a transaction and rolls it back. It still does the write work, generates WAL and can advance the ID sequence. Its plan timing excludes commit. PostgreSQL EXPLAIN ANALYZE and WAL

order-index.json records fixture rows and index bytes. Check connections, max_connections and lock_waits in database.tsv to see whether sampled sessions were near the connection limit or waiting on locks during the concurrent load. This sampler can miss short waits. PostgreSQL activity statistics

Three pairs ran on an Apple M3 Pro with PostgreSQL 18.4 and PHP-FPM limited to one CPU. Each eight-second run offered 40 workflows per second, completed 320 or 321 workflows and persisted all 64 checkouts. There were zero failures or drops, and the history responses were byte-identical. The per-run data is in code/bang-for-buck/bench/chapter-06-measurements.tsv.

MeasureIndex offIndex on
History HTTP p95, median and range16.86 ms (16.42–17.56)9.81 ms (9.13–57.63)
History SQL p50, median11.51 ms5.00 ms
Checkout HTTP p95, median and range40.02 ms (38.99–40.35)38.08 ms (37.25–133.81)
Checkout SQL p50, median7.49 ms6.94 ms
Direct read plan execution, median6.904 ms0.059 ms
Direct read plan shared-buffer hits, median2,78526
Insert probe execution, median per 1,000 rows11.39 ms12.41 ms
Insert probe WAL, median per 1,000 rows335,101 B414,590 B
Extra index size, median0 MiB7.8 MiB

The index cut the median history HTTP p95 by 42%. One indexed run had a much slower HTTP tail for both reads and checkouts. Use the full range when evaluating a tail-latency target. The six runs each had two database activity samples, with at most two sessions on the shop database and zero sampled lock waits.

Checkout timing includes shipping, the insert and other SQL. The controlled insert probe shows the write cost more directly. It took about 1 ms longer per 1,000 rows and generated about 24% more WAL with the extra index. Every new order adds an entry, so the read benefit comes with recurring write work and storage. PostgreSQL index trade-offs

Run the comparison again at your shop’s busiest expected rate. Record the read p95, checkout p95, index size and WAL bytes for both cases. Decide whether the tail-latency savings on the read justify the index for the amount of write traffic you expect.

Continue with Cache Data with an Expiry and an Owner.

Report a problem with this example