SQL Server to PostgreSQL: Week 3 Performance Lab
On this page
The performance tuning lab
A customer page is slow. It shows one customer's orders, newest dates last. Your job is to make it fast, prove that it's fast, and make sure the fix doesn't cost more than it saves. Try it yourself first, using the setup below, then compare with the walkthrough.
CREATE TABLE lab_orders (
order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id integer NOT NULL,
order_date date NOT NULL,
status text NOT NULL,
total_amount numeric(12,2) NOT NULL
);
INSERT INTO lab_orders (customer_id, order_date, status, total_amount)
SELECT (random() * 50000)::int,
date '2024-01-01' + (random() * 1000)::int,
(ARRAY['NEW', 'SHIPPED', 'DELIVERED', 'CANCELLED'])[ceil(random() * 4)],
round((random() * 500)::numeric, 2)
FROM generate_series(1, 1000000);
ANALYZE lab_orders;The slow query:
SELECT customer_id, order_date, total_amount
FROM lab_orders
WHERE customer_id = 12345
ORDER BY order_date;The numbers below come from one run on a laptop. Yours will differ, but the pattern will be the same.
Step 1: Measure before changing anything
EXPLAIN ANALYZE
SELECT customer_id, order_date, total_amount
FROM lab_orders WHERE customer_id = 12345 ORDER BY order_date;Sort (actual time=56.906..56.913 rows=15 loops=1)
Sort Key: order_date
-> Seq Scan on lab_orders (rows=21) (actual time=2.660..56.773 rows=15 loops=1)
Filter: (customer_id = 12345)
Rows Removed by Filter: 999985
Execution Time: 56.979 msReading it as on Day 15: the estimate (21 rows) is close to the actual count (15), so statistics are fine. The cost is the Seq Scan, which reads a million rows to keep 15. That's a missing index, not a planner problem.
Step 2: The obvious index
CREATE INDEX lab_orders_customer_idx ON lab_orders (customer_id);Sort (actual time=0.067..0.072 rows=15 loops=1)
-> Bitmap Heap Scan on lab_orders (actual time=0.034..0.052 rows=15 loops=1)
-> Bitmap Index Scan on lab_orders_customer_idx
Execution Time: 0.096 msFrom 57 ms to 0.1 ms. On a busy system that's often enough. The plan still sorts the rows and still visits the table for order_date and total_amount.
Step 3: A covering index, and its price
A SQL Server DBA would reach for a key on the filter and sort columns, with INCLUDE for the rest. PostgreSQL supports the same design (Day 16):
DROP INDEX lab_orders_customer_idx;
CREATE INDEX lab_orders_customer_date_idx ON lab_orders (customer_id, order_date) INCLUDE (total_amount);
VACUUM lab_orders;Index Only Scan using lab_orders_customer_date_idx on lab_orders (actual time=0.077..0.082 rows=15 loops=1)
Index Cond: (customer_id = 12345)
Heap Fetches: 0
Execution Time: 0.124 msThe Sort node is gone, because the index returns rows already in order_date order, and Heap Fetches: 0 means the table wasn't touched. Note the VACUUM before the test. Index-only scans depend on the visibility map, which vacuum maintains (Day 18).
Now check the price:
SELECT pg_size_pretty(pg_relation_size('lab_orders')) AS table_size,
pg_size_pretty(pg_relation_size('lab_orders_customer_date_idx')) AS index_size;
-- 61 MB | 30 MBThe covering index is half the size of the table, and every insert writes to it. For 15 rows, it wasn't measurably faster than the plain index on this run. The honest conclusion: the plain index on customer_id is the right fix here, unless measurements under real load, with many rows per customer or a cold cache, show the covering index pays for itself. Measure, don't assume.
A reusable tuning workflow
- Reproduce the slow query with realistic parameters.
- Run EXPLAIN ANALYZE and compare estimated with actual rows for each node.
- Find the most expensive node. Check statistics before blaming the planner.
- Change one thing: an index, a rewrite (Day 17) or a setting.
- Measure again, and check the cost, such as index size, write overhead and effects on other queries.
- Write down the before and after plans.
Week 3 in one table
| Day | The idea to keep |
|---|---|
| 15 | EXPLAIN shows the plan, and EXPLAIN ANALYZE runs it. Compare estimated with actual rows. |
| 16 | No clustered index. INCLUDE and partial indexes carry over. GIN, GiST and BRIN are new tools. |
| 17 | Watch case-insensitive comparisons, functions on indexed columns, NOT IN, and ANALYZE after loads. |
| 18 | Dead tuples are yours to manage. Tune autovacuum per table and never turn it off. |
| 19 | Partitions are real tables. Detach and drop replaces SWITCH. |
| 20 | Pool connections with PgBouncer. Find expensive queries with pg_stat_statements. |
Check yourself
Run ANALYZE on the table, then check the plan again. The planner was still using statistics gathered before the nightly load, so it believed the table was tiny and chose a plan suited to 40 rows. Add an explicit ANALYZE step at the end of the load job so this doesn't recur.
Probably not. The read gain is tiny compared with the extra write and storage cost. Keep the simpler index unless measurements under real load justify the covering one.
Index-only scans skip the table only for pages the visibility map marks as all-visible, and VACUUM maintains that map. On freshly loaded data, the map wasn't set yet.
Without a baseline you can't tell whether a change helped, made things worse or did nothing. Record the query's timing, plan and buffer counts first, change one thing at a time, and measure again with the same data.
How many times PostgreSQL still had to visit the table to check row visibility. Pages marked all-visible in the visibility map can be answered from the index alone. A high count means the visibility map is out of date, which a VACUUM fixes.