Query optimisation, EXPLAIN and connection pooling

A routine for slow queries

1. Find the slow or heavy queries: slow query log, database statistics views, or application tracing. Rank by total time (frequency times duration), not only the slowest single query.
2. Reproduce it with real parameters on realistic data size.
3. Read the plan with EXPLAIN (or EXPLAIN ANALYZE to execute and show actual times).
4. Change one thing (an index, a rewrite, a smaller result) and measure again.
5. Guard it: add a test or an alert so it does not regress.

Reading an EXPLAIN plan

A plan is a tree of operations. Things to look for:

100%

Figure SDA-DDV-03-1 · What to look for in a query plan · © forcsguy.com

• Scan types: sequential scan (whole table), index scan, index-only scan, bitmap scan.
• Join methods: nested loop (good for few outer rows with an indexed inner side), hash join (builds a hash of one side; good for larger sets), merge join (both sides sorted).
• Cost and rows: estimates; with ANALYZE you also see actual rows and time per step. The biggest actual time is where to look.
• Buffers/IO show whether data came from memory or disk.

The usual suspects

Symptom	Likely cause	Fix
Sequential scan on a big table for a few rows	Missing or unusable index	Add an index; avoid functions on the column
Slow ORDER BY ... LIMIT	Sort of many rows	Index matching filter and order
Hundreds of tiny queries	N+1 pattern	Join or batch with IN
SELECT * on wide rows	Reading unneeded columns	Select only needed columns; covering index
Deep OFFSET pagination	Database skips all earlier rows	Keyset pagination: WHERE (created_at, id) < (last_created_at, last_id) ORDER BY ... LIMIT n
LIKE '%text%'	Leading wildcard cannot use a B-tree	Full-text search or trigram index
Large aggregates every request	Recomputing totals	Precompute, cache, or use a summary table
Lock waits	Long transactions	Shorten transactions, update in a consistent order
Plan flips after growth	Stale statistics	Refresh statistics; review the plan

-- Keyset pagination: stable and fast at any depth
SELECT id, created_at, total
FROM orders
WHERE customer_id = $1 AND (created_at, id) < ($2, $3)
ORDER BY created_at DESC, id DESC
LIMIT 20;

The N+1 problem

// slow: 1 query for orders + 1 per order
const orders = await db.orders.list();
for (const o of orders) o.customer = await db.customers.get(o.customerId);

// better: two queries total
const orders2 = await db.orders.list();
const customers = await db.customers.getMany([...new Set(orders2.map(o => o.customerId))]);

ORMs hide this easily; watch the number of queries per request.

Connection pooling

Each database connection costs memory and a server process or thread; databases cap connections (often hundreds). Opening one per request is slow and can exhaust the limit.

100%

Figure SDA-DDV-03-2 · Connection pool · © forcsguy.com

• Size the pool from what the database can handle, not from the number of web threads. More connections is often slower past a point.
• With many application instances, the total is instances times pool size; use an external pooler (for example PgBouncer or a proxy) in front of the database for large fleets and serverless apps.
• Always return connections (use finally blocks) and set timeouts; a leaked connection slowly starves the app.
• Keep transactions short; a connection held during a slow external call is wasted.

Beyond the single query

• Cache results of expensive, stable queries (see the caching lessons).
• Read replicas take read load (watch replication lag).
• Partition large tables by time or key so queries touch less data.
• Archive old data.
• Use batch inserts and bulk loaders for large writes.

A healthy habit

Look at the top queries by total time every sprint, not only after incidents. Most gains come from a handful of queries.

Real-world reading