Reading Query Plans

Scenario

Example app: the same invoicing SaaS. The invoice list page — WHERE account_id = $1 ORDER BY issued_at DESC LIMIT 50 — was 3 ms when the table had 200,000 rows. It now has 48 million rows and takes 380 ms for big accounts.

What you see in production

The endpoint is the #1 query by total time. Someone adds CREATE INDEX ON invoices (issued_at) and nothing improves.

The naive code

SELECT id, number, total, issued_at
FROM invoices
WHERE account_id = $1
ORDER BY issued_at DESC
LIMIT 50;

Invoice.objects.filter(account_id=account_id).order_by("-issued_at")[:50]

Reproduce it

Run SQLite's query plan on the tutorial sample shop. Without a suitable index the database scans the table and sorts with a temporary B-tree; with a composite index it searches and reads rows already in order:

EXPLAIN QUERY PLAN
SELECT order_id, order_date FROM orders WHERE customer_id = 3 ORDER BY order_date DESC LIMIT 5;

CREATE INDEX idx_orders_customer_date ON orders (customer_id, order_date);

EXPLAIN QUERY PLAN
SELECT order_id, order_date FROM orders WHERE customer_id = 3 ORDER BY order_date DESC LIMIT 5;

id | parent | notused | detail
4 | 0 | 0 | SCAN orders
18 | 0 | 0 | USE TEMP B-TREE FOR ORDER BY

id | parent | notused | detail
4 | 0 | 0 | SEARCH orders USING COVERING INDEX idx_orders_customer_date (customer_id=?)

Root cause

The planner can only use an index whose leading columns match the filter. An index on issued_at alone doesn't help find one account's rows; the database scans many rows and sorts them. Plans also go wrong when statistics are stale — the planner expects 50 rows and gets 2 million, choosing a nested loop that explodes.

How to read Postgres EXPLAIN (ANALYZE, BUFFERS):

Limit  (cost=0.56..152.31 rows=50 width=40) (actual time=0.041..0.212 rows=50 loops=1)
  ->  Index Scan Backward using invoices_account_issued_idx on invoices
        (cost=0.56..1456221.80 rows=479850 width=40) (actual time=0.040..0.205 rows=50 loops=1)
        Index Cond: (account_id = 42)
        Buffers: shared hit=54
Planning Time: 0.120 ms
Execution Time: 0.239 ms

• Read inside out: the innermost node runs first.
• Compare estimated rows vs actual rows — big gaps mean bad statistics (run ANALYZE, raise the statistics target).
• Seq Scan on a large table with a selective filter, Sort with external merge (spilling to disk), and loops= multiplying work are the usual suspects.
• Buffers: shared read means disk reads; shared hit means cache.

The fix

Index for the query's shape — equality columns first, then the sort column — and verify with the plan:

CREATE INDEX CONCURRENTLY invoices_account_issued_idx ON invoices (account_id, issued_at DESC);
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, number, total, issued_at FROM invoices
WHERE account_id = 42 ORDER BY issued_at DESC LIMIT 50;

ALTER TABLE invoices ADD INDEX account_issued (account_id, issued_at), ALGORITHM=INPLACE, LOCK=NONE;
EXPLAIN ANALYZE SELECT id, number, total, issued_at FROM invoices
WHERE account_id = 42 ORDER BY issued_at DESC LIMIT 50;      -- MySQL 8.0.18+

class Invoice(models.Model):
    class Meta:
        indexes = [models.Index(fields=["account", "-issued_at"], name="invoices_account_issued_idx")]
# print(Invoice.objects.filter(account_id=42).order_by("-issued_at")[:50].explain(analyze=True))

@Table(name = "invoices", indexes = @Index(name = "invoices_account_issued_idx", columnList = "account_id, issued_at DESC"))
public class Invoice { ... }

Trade-offs

• EXPLAIN ANALYZE executes the query — wrap data-changing statements in a transaction you roll back.
• Plans on a small dev database differ from production; test plans on production-sized data (or a copy).
• Planner hints and forcing indexes are a last resort; fix statistics and indexes first.

Detect it in production

• auto_explain (Postgres) logs plans of statements slower than a threshold; MySQL's EXPLAIN ANALYZE and the slow log's Rows_examined show scans.
• Watch "rows examined / rows returned" per query — a ratio of 100,000 : 50 means a missing index.

Key points

• Read plans inside out and compare estimated vs actual rows.
• Indexes help only when their leading columns match the filter; equality first, then sort/range.
• Verify every index with the plan on production-sized data.