The symptom

We had a dashboard that loaded order summaries for a given merchant. For small accounts it was fine — 200-300ms. But one of our larger customers started complaining that their dashboard took 8+ seconds to load. No alerts had fired. No errors in logs. Just a very unhappy customer.

First stop: the slow query log. We had log_min_duration_statement = 1000 set in Postgres, so anything over a second was logged. The culprit showed up immediately:

-- the offending query (simplified)
SELECT
  o.id,
  o.created_at,
  o.total_cents,
  o.status,
  c.email AS customer_email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE
  o.merchant_id = $1
  AND o.created_at BETWEEN $2 AND $3
  AND o.status IN ('completed', 'refunded')
ORDER BY o.created_at DESC
LIMIT 100;

Looks reasonable. The table had indexes on merchant_id and created_at separately. So what was going wrong?

Running EXPLAIN ANALYZE

The most important tool in your Postgres debugging kit. Always use EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) — the BUFFERS option shows you exactly how much I/O is happening.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT /* ... */;

The output was telling:

Seq Scan on orders  (cost=0.00..480201.34 rows=847 width=72)
                    (actual time=0.041..8043.221 rows=2341 loops=1)
  Filter: ((merchant_id = 1048) AND (created_at BETWEEN ...) AND ...)
  Rows Removed by Filter: 11,847,219
  Buffers: shared hit=42 read=213847
Planning Time: 1.2 ms
Execution Time: 8102.4 ms

A sequential scan removing 11.8 million rows. Postgres was reading the entire table and filtering in memory. With 213,847 shared buffer reads, most of the time was pure I/O.

But why? We had an index on merchant_id. Time to check:

SELECT
  indexname,
  indexdef
FROM pg_indexes
WHERE tablename = 'orders';

-- output
orders_pkey              | CREATE UNIQUE INDEX orders_pkey ON orders USING btree (id)
orders_merchant_id_idx   | CREATE INDEX orders_merchant_id_idx ON orders USING btree (merchant_id)
orders_created_at_idx    | CREATE INDEX orders_created_at_idx ON orders USING btree (created_at)
orders_status_idx        | CREATE INDEX orders_status_idx ON orders USING btree (status)

Problem 1 — no composite index

Postgres had three separate single-column indexes available but chose none of them. Why? Because our WHERE clause filters on three columns simultaneously. Using the merchant_id index alone would still return every order for that merchant across all time — potentially millions of rows — before filtering by date and status. Postgres estimated that a seq scan was cheaper.

The fix: a composite index that covers all three filter columns, ordered so the highest-cardinality equality filter comes first, then the range column, with the status column included:

CREATE INDEX CONCURRENTLY orders_merchant_date_status_idx
  ON orders (merchant_id, created_at DESC, status)
  WHERE status IN ('completed', 'refunded');

The partial index (WHERE status IN (...)) is a nice touch — it excludes the large share of rows with status 'pending' or 'cancelled', keeping the index small and fast.

I used CONCURRENTLY so the build doesn't lock the table in production. It takes longer, but you keep serving traffic.

Problem 2 — the implicit cast

After adding the index I ran EXPLAIN ANALYZE again. Better — down to ~600ms — but still not using the new index cleanly. There was a subtle warning buried in the plan:

Filter: ((merchant_id)::text = $1)

See the ::text cast? Our application was passing merchant_id as a string (the ORM's default), but the column type was integer. Postgres was casting every row's merchant_id to text before comparing — making the index on the integer column unusable for that predicate.

Fix: explicitly bind the parameter as an integer in the query layer. In our Go service using pgx:

// Before — ORM passes everything as text
args := []interface{}{merchantID}

// After — explicit integer type
args := []pgx.QueryResultFormats{}
args = append(args, pgtype.Int4{Int: merchantID, Valid: true})

The results

Metric	Before	After
Execution time	8,102 ms	198 ms
Scan type	Seq Scan	Index Scan
Buffer reads	213,847 pages	312 pages
Rows examined	11,849,560	2,341
Speedup	—	~40×

Lessons

• Single-column indexes rarely help queries with multiple filter conditions — design composite indexes to match your actual query patterns.
• Always check for implicit casts in EXPLAIN output. An ORM passing the wrong type can silently destroy index usage.
• Use EXPLAIN (ANALYZE, BUFFERS), not just EXPLAIN. The buffer stats tell you whether you have an I/O problem or a CPU problem.
• Partial indexes are underused. If a large fraction of your rows will never be queried, exclude them from the index entirely.
• CREATE INDEX CONCURRENTLY is your friend in production. Slightly slower to build, zero downtime.