Project: Optimize a Slow Query

-- ---------- STEP 1: build a realistic dataset ----------

CREATE DATABASE perf_lab;
\c perf_lab

CREATE TABLE customers (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name    text NOT NULL,
    city    text,
    country char(2) NOT NULL DEFAULT 'IN'
);

CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers(id),
    status      text NOT NULL,
    total       numeric(10,2) NOT NULL,
    placed_at   timestamptz NOT NULL
);

-- 50,000 customers
INSERT INTO customers (name, city)
SELECT 'Customer ' || i,
       (ARRAY['Pune','Kochi','Delhi','Mumbai','Chennai'])[1 + (i % 5)]
FROM generate_series(1, 50000) AS i;

-- 2,000,000 orders spread over two years.
-- Note: only ~8% are 'completed' — a realistic skew, and the reason
-- a partial index will win later.
INSERT INTO orders (customer_id, status, total, placed_at)
SELECT 1 + (random() * 49999)::int,
       CASE WHEN random() < 0.08 THEN 'completed'
            WHEN random() < 0.4  THEN 'cancelled'
            ELSE 'pending' END,
       round((random() * 5000 + 50)::numeric, 2),
       now() - (random() * interval '730 days')
FROM generate_series(1, 2000000);

ANALYZE;     -- statistics first, always

-- ---------- STEP 2: measure the baseline ----------

\timing on

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.city,
       count(*)   AS orders,
       sum(o.total) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'completed'
  AND o.placed_at >= now() - interval '90 days'
GROUP BY c.city
ORDER BY revenue DESC;

-- RECORD THE NUMBER. Run it three times and take the best —
-- the first run also pays for reading data into cache.

---------- STEP 3: read the plan ----------

HashAggregate  (actual time=7984.2..7984.6 rows=5 loops=1)
  ->  Hash Join  (actual time=612.4..7799.1 rows=19847 loops=1)
        Hash Cond: (o.customer_id = c.id)
        ->  Seq Scan on orders o
              (cost=0.00..48210.00 rows=98234 width=22)
              (actual time=0.03..7201.8 rows=19847 loops=1)
              Filter: ((status = 'completed'::text)
                       AND (placed_at >= (now() - '90 days'::interval)))
              Rows Removed by Filter: 1980153          <-- !!
        ->  Hash (actual time=610.2..610.2 rows=50000 loops=1)
              ->  Seq Scan on customers c (actual time=0.01..298.7 rows=50000)
Execution Time: 7985.1 ms

WHAT THIS SAYS:
  (a) Seq Scan on orders: read 2,000,000 rows to return 19,847.
  (b) "Rows Removed by Filter: 1,980,153" — 99% of the work was wasted.
  (c) Estimate 98,234 vs actual 19,847 — a 5x overestimate.
  (d) No index is being used for the filter at all.

-- ---------- STEP 4: check statistics BEFORE adding indexes ----------

ANALYZE orders;
-- Re-run the query. Did the estimate improve? Sometimes this alone fixes
-- a bad plan, and a stale estimate can make the planner IGNORE a new index.
-- Here the estimate is closer but the Seq Scan remains, because there is
-- genuinely no index to use.

-- ---------- STEP 5: hypothesis 1 — index the date ----------

CREATE INDEX CONCURRENTLY orders_placed_at_idx ON orders (placed_at);
ANALYZE orders;

-- Re-run. Expect roughly: 7985ms -> ~2100ms
-- Better: the date range is now an index scan. But PostgreSQL still
-- fetches every order in the last 90 days (~250,000 rows) and then
-- discards the 92% that are not 'completed'.

-- ---------- STEP 6: hypothesis 2 — composite index ----------

CREATE INDEX CONCURRENTLY orders_status_placed_idx
    ON orders (status, placed_at);
ANALYZE orders;

-- Re-run. Expect roughly: ~2100ms -> ~480ms
-- Column order matters: status is used with =, so it goes FIRST;
-- placed_at is a range, so it goes SECOND.
-- Reversing them would make the index far less effective.

-- ---------- STEP 7: hypothesis 3 — partial index ----------

-- Only 8% of rows are 'completed', and the query ONLY ever wants those.
CREATE INDEX CONCURRENTLY orders_completed_placed_idx
    ON orders (placed_at)
    WHERE status = 'completed';
ANALYZE orders;

-- Re-run. Expect roughly: ~480ms -> ~210ms

-- Compare the sizes — this is the real story:
SELECT indexrelname,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       idx_scan AS times_used
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY pg_relation_size(indexrelid) DESC;

-- orders_status_placed_idx     ~86 MB   (every row)
-- orders_completed_placed_idx   ~4 MB   (only completed rows)
-- Smaller index = less disk, faster scans, and cheaper writes, because
-- non-completed rows never touch it at all.

-- ---------- STEP 8: covering index — avoid touching the table ----------

DROP INDEX CONCURRENTLY orders_completed_placed_idx;

CREATE INDEX CONCURRENTLY orders_completed_covering_idx
    ON orders (placed_at)
    INCLUDE (customer_id, total)      -- carry the other needed columns
    WHERE status = 'completed';
ANALYZE orders;

-- Re-run. Look for "Index Only Scan" in the plan.
-- The table itself is never read, because every column the query needs
-- is inside the index. Expect roughly: ~210ms -> ~140ms

-- ---------- STEP 9: remove what is no longer earning its place ----------

-- Three indexes now overlap. Keep the one that wins, drop the rest —
-- every index costs time on every INSERT, UPDATE and DELETE.
DROP INDEX CONCURRENTLY orders_placed_at_idx;
DROP INDEX CONCURRENTLY orders_status_placed_idx;

-- Confirm the query still uses the remaining index:
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;   -- the same query

-- Find indexes nothing ever uses:
SELECT indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan;

---------- RESULTS TABLE — fill this in yourself ----------

Change                             Time      vs baseline   Index size
-----------------------------------------------------------------------
baseline (no index)                7985 ms      1.0x           -
+ index on (placed_at)             2100 ms      3.8x          43 MB
+ index on (status, placed_at)      480 ms     16.6x          86 MB
+ partial index (completed only)    210 ms     38.0x           4 MB
+ covering (INCLUDE)                140 ms     57.0x           6 MB

Your numbers WILL differ — hardware, cache state and the random data
all matter. The SHAPE of the improvement is what to pay attention to.

-- ---------- STEP 10: find what to optimise next ----------

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- (needs shared_preload_libraries = 'pg_stat_statements' + a restart)

SELECT round(total_exec_time::numeric) AS total_ms,
       calls,
       round(mean_exec_time::numeric,2) AS avg_ms,
       left(query, 60) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Optimise by TOTAL time. A 15ms query called a million times
-- costs far more than this 8-second report run twice a day.