Slow Customer Page? Fix a LEFT JOIN Query with EXPLAIN, Indexes & EXISTS (PostgreSQL Case Study)

Every developer eventually meets this ticket: "The customer detail page takes forever to load." After some digging, you discover the page is doing something very simple on the surface: it only needs to know whether this customer has overdue payments. Yet the query behind it chains several LEFT JOINs across millions of rows.

The good news is that this kind of problem is almost always caused by a handful of repeatable mistakes, and each one has a cheap fix. In this guide we reproduce the problem on a realistic dataset (a clothing store with shirts, pants, jackets, and T-shirts), read the query plan like a detective, and fix the query one step at a time while measuring the result after every change.

All examples use PostgreSQL. The concepts apply to MySQL as well, and there is a dedicated section on the differences near the end.

The Case Study Schema

Our online clothing store has five tables. Customers place orders, each order contains items (a product from a category such as shirt, pants, jacket, or tshirt), and customers pay for orders through payments.

erDiagram
    CUSTOMERS ||--o{ ORDERS : places
    ORDERS ||--o{ ORDER_ITEMS : contains
    PRODUCTS ||--o{ ORDER_ITEMS : "appears in"
    ORDERS ||--o{ PAYMENTS : "paid by"
    CUSTOMERS {
        bigint id PK
        text name
        text email
        text city
    }
    ORDERS {
        bigint id PK
        bigint customer_id FK
        text status
        numeric total
        date due_date
    }
    ORDER_ITEMS {
        bigint id PK
        bigint order_id FK
        bigint product_id FK
        int qty
    }
    PRODUCTS {
        bigint id PK
        text name
        text category
        numeric price
    }
    PAYMENTS {
        bigint id PK
        bigint order_id FK
        numeric amount
    }

Create the Tables

CREATE TABLE customers (
    id         BIGSERIAL PRIMARY KEY,
    name       TEXT NOT NULL,
    email      TEXT NOT NULL,
    city       TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE products (
    id       BIGSERIAL PRIMARY KEY,
    name     TEXT NOT NULL,
    category TEXT NOT NULL,   -- shirt, pants, jacket, tshirt
    price    NUMERIC(12,2) NOT NULL
);

CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id),
    status      TEXT NOT NULL,  -- paid, pending, unpaid
    total       NUMERIC(12,2) NOT NULL,
    due_date    DATE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
    id         BIGSERIAL PRIMARY KEY,
    order_id   BIGINT NOT NULL REFERENCES orders(id),
    product_id BIGINT NOT NULL REFERENCES products(id),
    qty        INT NOT NULL,
    price      NUMERIC(12,2) NOT NULL
);

CREATE TABLE payments (
    id       BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(id),
    amount   NUMERIC(12,2) NOT NULL,
    paid_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

Remember This Detail
In PostgreSQL, a PRIMARY KEY automatically gets an index, but a foreign key does not. Right now orders.customer_id has no index at all. Keep that in mind; it becomes one of our main suspects.

Generate Realistic Test Data

Performance problems only show up with volume. On a table of 100 rows everything is fast. Let's generate 100K customers, 1M orders, and 3M order items:

-- 100K customers
INSERT INTO customers (name, email, city)
SELECT 'Customer ' || g,
       'user' || g || '@mail.test',
       (ARRAY['Bandung','Jakarta','Surabaya','Medan'])[1 + g % 4]
FROM generate_series(1, 100000) g;

-- 200 products across 4 categories
INSERT INTO products (name, category, price)
SELECT 'Product ' || g,
       (ARRAY['shirt','pants','jacket','tshirt'])[1 + g % 4],
       (50000 + (random() * 450000))::int
FROM generate_series(1, 200) g;

-- 1M orders: ~2% unpaid, ~3% pending, the rest paid
INSERT INTO orders (customer_id, status, total, due_date, created_at)
SELECT 1 + (random() * 99999)::int,
       CASE WHEN r < 0.02 THEN 'unpaid'
            WHEN r < 0.05 THEN 'pending'
            ELSE 'paid' END,
       (100000 + (random() * 900000))::int,
       current_date - (random() * 120)::int,
       now() - (random() * interval '730 days')
FROM (SELECT random() AS r FROM generate_series(1, 1000000)) t;

-- 3M order items
INSERT INTO order_items (order_id, product_id, qty, price)
SELECT 1 + (random() * 999999)::int,
       1 + (random() * 199)::int,
       1 + (random() * 3)::int,
       (50000 + (random() * 450000))::int
FROM generate_series(1, 3000000);

-- payments for every paid order
INSERT INTO payments (order_id, amount)
SELECT id, total FROM orders WHERE status = 'paid';

-- give customer 1 a few overdue orders to test with
INSERT INTO orders (customer_id, status, total, due_date)
SELECT 1, 'unpaid', 250000, current_date - 10
FROM generate_series(1, 3);

ANALYZE;

The final ANALYZE refreshes table statistics so the query planner knows how big each table really is.

Step 1: Reproduce the Slow Query

This is the pattern we see all the time in customer detail pages: start from orders, then LEFT JOIN everything in sight:

SELECT c.id,
       c.name,
       SUM(o.total - COALESCE(p.amount, 0)) AS outstanding
FROM orders o
LEFT JOIN customers   c  ON c.id = o.customer_id
LEFT JOIN order_items oi ON oi.order_id = o.id
LEFT JOIN payments    p  ON p.order_id = o.id
WHERE c.id = 1
  AND o.status = 'unpaid'
GROUP BY c.id, c.name;

There are three problems hiding in this query:

1. It starts from orders, the biggest table, even though we only care about one customer.
2. It joins order_items for no reason. Nothing in the select list uses it, yet it multiplies the rows and silently inflates the SUM.
3. It aggregates with SUM + GROUP BY when the page may only need a yes/no answer.

Honest Caveat
Sometimes PostgreSQL is smart enough to notice that WHERE c.id = 1 turns the LEFT JOIN into an inner join and reorders the tables on its own. That is never guaranteed. It depends on your version, the number of joins, how the filter is written, and how fresh your statistics are. Do not rely on the optimizer's goodwill. Always check EXPLAIN on your own database.

Step 2: Read the Query Plan with EXPLAIN ANALYZE

Never guess. Ask the database what it is actually doing:

EXPLAIN (ANALYZE, BUFFERS)
SELECT ... -- the query above

An illustrative output for a problematic plan (your numbers will differ):

GroupAggregate  (actual time=7412.3..7412.9 rows=1 loops=1)
  ->  Hash Left Join  (actual time=3120.4..7390.1 rows=11 loops=1)
        ->  Hash Left Join  (actual time=2210.7..5980.2 rows=3000000 loops=1)
              ->  Seq Scan on orders o  (actual time=0.02..410.5 rows=1000003 loops=1)
                    Filter: (status = 'unpaid')
                    Rows Removed by Filter: 960000
Execution Time: 7415.882 ms

Three things to look for every time:

What You See	What It Means
Seq Scan on a big table	The database reads the entire table. For a question about one customer, that is a red flag.
Huge rows= in early nodes	Millions of rows are processed to produce a handful of results.
Large Rows Removed by Filter	Most of the work is thrown away after being done.

A simple rule of thumb: compare rows read to rows returned. If the ratio is millions to one, something is wrong.

Step 3: Start from the Right Table (Driving Table)

In a LEFT JOIN, the leftmost table is the driving table: every one of its rows must be kept, so the optimizer is not free to swap the order. Start from orders and you begin with a million rows. Start from customers filtered by primary key and you begin with exactly one.

SELECT c.id,
       c.name,
       SUM(o.total - COALESCE(p.amount, 0)) AS outstanding
FROM customers c
LEFT JOIN orders   o ON o.customer_id = c.id AND o.status = 'unpaid'
LEFT JOIN payments p ON p.order_id = o.id
WHERE c.id = 1
GROUP BY c.id, c.name;

Two changes: the query now begins at customers, and the useless order_items join is gone. Notice that the status = 'unpaid' filter moved into the ON clause. For a LEFT JOIN that is the correct place; putting it in WHERE would discard customers who have no unpaid orders and quietly turn the join into an inner join.

Golden Rule
Start from the table that has the fewest rows after filtering.

If you re-run EXPLAIN now, you will likely still see a Seq Scan on orders. That is the next step.

Step 4: Index Your Foreign Keys

The order is now right, but orders.customer_id has no index. The database knows it must find the orders of customer 1 but has no shortcut, so it reads the whole table anyway.

CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_payments_order_id  ON payments (order_id);

Re-run EXPLAIN ANALYZE and you should now see:

Index Scan using idx_orders_customer_id on orders o
  Index Cond: (customer_id = 1)

Production Tip
On a busy table, use CREATE INDEX CONCURRENTLY ... so writes are not blocked while the index builds.

Step 5: Use EXISTS When You Only Need Yes or No

Now ask the most important question: does the page need the amount, or just a "Has Overdue Payments" badge? If it is only a badge, we do not need to sum anything. We only need to know whether at least one overdue order exists.

SELECT c.id,
       c.name,
       EXISTS (
           SELECT 1
           FROM orders o
           WHERE o.customer_id = c.id
             AND o.status = 'unpaid'
             AND o.due_date < CURRENT_DATE
       ) AS has_arrears
FROM customers c
WHERE c.id = 1;

Why this is faster:

• EXISTS stops at the first matching row. A JOIN with aggregation must process everything first.
• No row multiplication, no wasted arithmetic.
• The result is a plain true / false your application can use directly.

Step 6: Add a Partial Index

Only about 2% of orders are unpaid, but our index stores all of them. For the arrears check we only care about that small slice, which is exactly what a partial index is for:

CREATE INDEX idx_orders_unpaid
ON orders (customer_id, due_date)
WHERE status = 'unpaid';

• The index is a fraction of the size, so it fits in memory easily.
• Writes are cheaper: inserting a paid order never touches this index.
• The planner can use it only when the query's condition matches the index predicate (status = 'unpaid'), which our EXISTS query does.

Verify with:

EXPLAIN (ANALYZE, BUFFERS)
SELECT EXISTS (
    SELECT 1 FROM orders
    WHERE customer_id = 1
      AND status = 'unpaid'
      AND due_date < CURRENT_DATE
);

Look for Index Scan or Index Only Scan using idx_orders_unpaid.

Step 7: When You Really Need the Amount

Sometimes the page must show the outstanding balance. Don't force it back into one giant query. Keep a small, focused aggregation that never touches order_items:

SELECT COALESCE(SUM(
         o.total - COALESCE((SELECT SUM(p.amount)
                             FROM payments p
                             WHERE p.order_id = o.id), 0)
       ), 0) AS outstanding
FROM orders o
WHERE o.customer_id = 1
  AND o.status = 'unpaid';

Because the partial index narrows the work to a few rows, the correlated subquery runs only a handful of times.

Still Not Fast Enough?
For extremely hot pages, consider a summary column such as customers.outstanding_amount, updated whenever an order or payment changes. This is deliberate denormalization: reads become instant, but you must keep the value consistent. Use it only after the steps above have been exhausted.

Results: Before vs. After

Measure after every single change. The table below shows the shape of the improvement. The numbers are illustrative; run the steps on your own database and record your own results.

Step	Change	Main Plan Node	Time (Illustrative)
0	Original query (FROM orders, extra join)	Seq Scan on orders	~7,400 ms
1	Start from customers, drop order_items	Seq Scan on orders	~2,900 ms
2	Index on orders(customer_id)	Index Scan	~45 ms
3	Switch to EXISTS	Index Scan, early stop	~4 ms
4	Partial index on status = 'unpaid'	(Index Only) Scan on a tiny index	~1 ms

The pattern is consistent: the biggest wins come from the first two fixes, correct join order and the right index. Everything after that is polish.

Cheat Sheet: Symptom, Cause, Fix

Symptom	Likely Cause	Fix
Seq Scan on a huge table for a single-customer query	Missing index on the foreign key	CREATE INDEX on the join/filter column
Millions of rows in early plan nodes	Wrong driving table	Start FROM the filtered entity
SUM results too large or slow	Row multiplication from unused one-to-many joins	Remove the join or aggregate separately
Query only needs a yes/no answer	COUNT(*) or JOIN + GROUP BY	Use EXISTS
Index exists but is ignored	Function on the column, e.g. DATE(created_at)	Rewrite as a range: created_at >= ... AND created_at < ...
Index is huge but you query one status	Indexing rows you never search	Partial index with a WHERE clause
Plans look strange after big data changes	Stale statistics	Run ANALYZE

Slow Query Troubleshooting Checklist

Save this list. It is ordered from the most common culprit to the least:

• Run EXPLAIN (ANALYZE, BUFFERS): Never optimize by guessing. Measure first.
• Hunt for Seq Scans: Look for full table scans on large tables in queries that should return few rows.
• Check the FROM / JOIN order: Begin with the entity you are filtering on, such as customers WHERE id = ?.
• Index foreign keys and filter columns: Especially columns used in JOIN ... ON and WHERE.
• Remove unused joins: Every extra one-to-many join multiplies rows.
• Need yes or no? Use EXISTS: Don't count or sum what you will only compare to zero.
• Don't wrap indexed columns in functions: Use range conditions instead of DATE(col) or LOWER(col), or build a matching expression index.
• Consider a partial index: When you only ever search a small slice of the table, such as unpaid orders.
• Refresh statistics: Run ANALYZE after loading or changing lots of data.
• Re-measure after every change: One change, one measurement. Otherwise you will never know what actually helped.

What About MySQL?

The reasoning is identical, but a few details differ:

• Foreign keys are indexed automatically in InnoDB, but only when the constraint exists. Check your real indexes with SHOW CREATE TABLE orders;.
• Partial indexes are not supported. Use a composite index instead, such as (status, customer_id, due_date) or (customer_id, status, due_date), and compare plans to see which one the optimizer prefers.
• EXPLAIN ANALYZE is available from MySQL 8.0.18. On older versions use EXPLAIN or EXPLAIN FORMAT=JSON.
• Wrapping a column in a function inside WHERE defeats indexes in MySQL just as it does in PostgreSQL.

Before You Touch Production
Test every index and query change on a copy of your data first. Adding an index speeds up reads but slows down writes and uses disk space. Create indexes deliberately, measure the result, and remove the ones nobody uses.

Conclusion

A slow customer detail page is rarely a sign that your database is too weak. It is usually the combined effect of three simple mistakes: the wrong driving table, a missing foreign key index, and a query that calculates far more than the page needs.

The whole method fits in one sentence: find out what the page actually needs, then ask the database to do exactly that and nothing more. Measure with EXPLAIN ANALYZE, fix one thing at a time, and measure again.