Catalog My learning Blog
SQL Server to PostgreSQL Migration Guide (30 Days) · Phase 3 · Lesson 21 · 4 min read

SQL Server to PostgreSQL: Week 3 Performance Lab

On this page
  1. The performance tuning lab
  2. Step 1: Measure before changing anything
  3. Step 2: The obvious index
  4. Step 3: A covering index, and its price
  5. A reusable tuning workflow
  6. Week 3 in one table
  7. Check yourself

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.

SQL
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:

SQL
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

SQL
EXPLAIN ANALYZE
SELECT customer_id, order_date, total_amount
FROM lab_orders WHERE customer_id = 12345 ORDER BY order_date;
Code
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 ms

Reading 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

SQL
CREATE INDEX lab_orders_customer_idx ON lab_orders (customer_id);
Code
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 ms

From 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):

SQL
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;
Code
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 ms

The 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:

Code
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 MB

The 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

  1. Reproduce the slow query with realistic parameters.
  2. Run EXPLAIN ANALYZE and compare estimated with actual rows for each node.
  3. Find the most expensive node. Check statistics before blaming the planner.
  4. Change one thing: an index, a rewrite (Day 17) or a setting.
  5. Measure again, and check the cost, such as index size, write overhead and effects on other queries.
  6. Write down the before and after plans.

Week 3 in one table

DayThe idea to keep
15EXPLAIN shows the plan, and EXPLAIN ANALYZE runs it. Compare estimated with actual rows.
16No clustered index. INCLUDE and partial indexes carry over. GIN, GiST and BRIN are new tools.
17Watch case-insensitive comparisons, functions on indexed columns, NOT IN, and ANALYZE after loads.
18Dead tuples are yours to manage. Tune autovacuum per table and never turn it off.
19Partitions are real tables. Detach and drop replaces SWITCH.
20Pool 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.