Database Query Tuning Checklist

Overview

When a query runs slow, the temptation is to start changing things at random — add an index here, rewrite a join there — and hope. This checklist is the opposite of that: a top-down path through the usual suspects, in the order you should check them. Start at the plan (section 1), because a query you haven’t profiled is a query you’re guessing about. Then work through indexes, N+1 patterns, joins, pagination, and finally the infrastructure — pooling and caching — before you blame the database.

Each section tells you what to run, what a bad result looks like, and what the fix usually is. Examples cover PostgreSQL and MySQL; the concepts transfer to any relational engine. Keep the SQL Query Optimization guide open for the deeper theory — this file is the triage sheet.

1. EXPLAIN Plan Analysis

Always start here. EXPLAIN shows the planner’s strategy; EXPLAIN ANALYZE shows what actually happened. The difference matters — estimated row counts can be off by orders of magnitude when table statistics are stale.

1.1 PostgreSQL EXPLAIN

-- Basic EXPLAIN (estimated plan, doesn't run the query)
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

-- EXPLAIN with real execution stats (actually runs it)
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 123;

-- Detailed timing + machine-readable output
EXPLAIN (ANALYZE, BUFFERS, TIMING, FORMAT JSON)
SELECT * FROM orders WHERE customer_id = 123;

1.2 MySQL EXPLAIN

-- Basic EXPLAIN
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

-- EXPLAIN ANALYZE (MySQL 8.0+)
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;

-- SHOW WARNINGS reveals the rewritten query MySQL actually ran
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;
SHOW WARNINGS;

1.3 Red Flags in the Plan

These scan types almost always mean “you’re missing an index” — check them against the actual row counts before reaching for CREATE INDEX:

Scan type	Meaning	Fix
Seq Scan (Postgres)	Full table scan, no index used	Index on the filter column
ALL (MySQL)	Full table scan	Index on the filter column
Filesort	Extra sort step after retrieval	Index matching the ORDER BY
Temporary	Temp table materialized	Rewrite GROUP BY or index it
Nested Loop on big tables	O(n·m) join	Index on the join column
Rows examined ≫ returned	Low selectivity	Better/composite index

1.4 Metrics Worth Watching

Metric (PostgreSQL)	Good	Warning	Bad
Seq Scan rows	< 100	100–10,000	> 10,000
Sort rows	< 1,000	1,000–50,000	> 50,000
Hash Build rows	< 10,000	10,000–100,000	> 100,000
Buffers shared hit	> 90 %	50–90 %	< 50 %
Execution time	< 10ms	10–100ms	> 100ms

If the plan looks right but ANALYZE row estimates are wildly off, the statistics are stale — run ANALYZE orders; (and keep autovacuum healthy) before adding indexes to compensate for a planner that’s working with bad data.

2. Index Strategy

Indexes are the fix for the majority of slow reads — and a tax on every write. The checklist below covers the audit cycle: verify what’s being used, add what’s missing, remove what’s dead weight.

2.1 Index Audit Checklist

- [ ] Check existing indexes on the table
  - PostgreSQL: \d orders or SELECT * FROM pg_indexes WHERE tablename = 'orders';
  - MySQL: SHOW INDEX FROM orders;

- [ ] Verify the query actually uses an index
  - Run EXPLAIN — look for Index Scan, not Seq Scan
  - Index on WHERE clause columns
  - Index on JOIN columns
  - Index on ORDER BY columns

- [ ] Add missing indexes (see 2.2 for examples)
  - Single-column for simple equality
  - Composite for multi-column filters
  - Partial index for a common filter subset
  - Covering index to avoid table lookups

- [ ] Remove unused indexes
  - PostgreSQL: SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;
  - MySQL: SELECT * FROM sys.schema_unused_indexes;
  - Every unused index still costs writes and space

- [ ] Check index selectivity
  - High selectivity (many distinct values): good candidate
  - Low selectivity (boolean, status enum): usually skip
  - SELECT COUNT(DISTINCT col)::float / COUNT(*) FROM orders;
  - Selectivity > 0.3 is a decent candidate

2.2 Index Creation Examples

-- Composite index: equality columns first, range last — order matters
CREATE INDEX idx_orders_customer_status_date
  ON orders(customer_id, status, created_at);

-- PostgreSQL partial index: only the rows you'll actually filter
CREATE INDEX idx_orders_active_customer
  ON orders(customer_id)
  WHERE status = 'active';

-- PostgreSQL covering index: INCLUDE columns are stored, not searched
CREATE INDEX idx_orders_customer_cover
  ON orders(customer_id)
  INCLUDE (total, status, created_at);

-- MySQL composite index
CREATE INDEX idx_orders_customer_status_date
  ON orders(customer_id, status, created_at);

-- MySQL functional index (8.0+)
CREATE INDEX idx_orders_lower_email
  ON orders((LOWER(email)));

-- PostgreSQL expression index
CREATE INDEX idx_orders_lower_email
  ON orders(LOWER(email));

Don’t index foreign keys blindly either — PostgreSQL does not auto-index the referencing side of a FK, which is a classic cause of slow joins and cascade deletes.

3. N+1 Query Detection

The N+1 pattern is silent in a single-query test — it only shows up as hundreds of roundtrips under real data. One query fetches a list; the code then fires one query per row for the related data.

3.1 The Pattern

Problem:
  1 query to fetch 100 orders
  100 queries to fetch each customer's name
  Total: 101 queries

Solution:
  1 query with a JOIN, or 2 queries with a batched IN (...)

3.2 Detection

-- PostgreSQL: log every query for a controlled test window
ALTER SYSTEM SET log_min_duration_statement = 0;
SELECT pg_reload_conf();

-- MySQL: capture everything briefly
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0;

-- In the log, look for the same query shape repeated
-- with different parameters — that's your N+1

Don’t leave long_query_time = 0 on — log-flood a production box and you’ll create the outage you were trying to debug. Reset it after the test.

3.3 Fixing It

-- Before: 1 query + N lookups (ORM-generated)
-- Query 1: SELECT * FROM orders WHERE status = 'active';
-- Query 2..101: SELECT * FROM customers WHERE id = ?;

-- After: a single JOIN
SELECT
  o.id,
  o.total,
  o.created_at,
  c.name AS customer_name,
  c.email AS customer_email
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'active';

-- Or batch when a JOIN doesn't fit (e.g. different services)
SELECT id, customer_id, total FROM orders WHERE status = 'active';
SELECT id, name, email FROM customers WHERE id IN (1, 2, 3, 4, 5, ...100);

3.4 ORM-Specific Fixes

# Django: select_related (JOIN) for FK, prefetch_related for M2M/reverse
# Before (N+1):
orders = Order.objects.filter(status='active')
for order in orders:
    print(order.customer.name)  # one extra query per order

# After (single JOIN):
orders = Order.objects.filter(status='active').select_related('customer')
for order in orders:
    print(order.customer.name)  # no extra queries

# After (batch via prefetch_related for reverse FK):
customers = Customer.objects.prefetch_related('order_set').all()
for customer in customers:
    for order in customer.order_set.all():  # no extra queries
        print(order.total)

# Rails: includes / preload / eager_load
# Before (N+1):
orders = Order.where(status: 'active')
orders.each { |o| puts o.customer.name }

# After:
orders = Order.includes(:customer).where(status: 'active')
orders.each { |o| puts o.customer.name }

4. Join Optimization

Joins are where good queries go to die — usually because of an unindexed join column or a filter applied too late. Check these before rewriting the whole query.

4.1 Join Checklist

- [ ] Verify join columns are indexed on both sides
  - PostgreSQL does not auto-index FKs — check \d child_table

- [ ] Use the correct join type
  - INNER JOIN: only matching rows (default, fastest)
  - LEFT JOIN: all left rows + matches — expensive on big right tables
  - CROSS JOIN: cartesian product — never accidental

- [ ] Filter early
  - WHERE conditions should shrink rows before the join
  - Push filters into subqueries or CTEs for clarity

- [ ] Don't select what you don't need
  - SELECT only needed columns, not SELECT *
  - Covering indexes avoid extra table lookups

- [ ] Sanity-check join order
  - Most selective filter applied first
  - The optimizer is usually right — force order only with evidence

4.2 Examples

-- Before: joins every customer row before filtering
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'active' AND o.created_at > '2026-01-01';

-- After: filter orders first, then join the small result
SELECT c.name, active_orders.total
FROM customers c
INNER JOIN (
  SELECT customer_id, total
  FROM orders
  WHERE status = 'active' AND created_at > '2026-01-01'
) active_orders ON c.id = active_orders.customer_id;

-- Same thing, more readable as a CTE
WITH active_orders AS (
  SELECT customer_id, total
  FROM orders
  WHERE status = 'active' AND created_at > '2026-01-01'
)
SELECT c.name, ao.total
FROM customers c
INNER JOIN active_orders ao ON c.id = ao.customer_id;

5. Pagination Patterns

OFFSET feels free until page 500 — every page reads and discards all the rows before it. Keyset (cursor) pagination reads only what it returns.

5.1 Avoid OFFSET

-- Problem: scans 10,020 rows, returns 20 — 10,000 rows wasted
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 10000;

-- Keyset: starts where the last page ended, reads exactly 20 rows
SELECT * FROM orders
WHERE created_at < '2026-06-15 10:30:00'
ORDER BY created_at DESC
LIMIT 20;

5.2 Keyset Implementation

-- Page 1
SELECT id, created_at, total
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- Page 2: last row's (created_at, id) becomes the cursor
SELECT id, created_at, total
FROM orders
WHERE (created_at, id) < ('2026-06-15 10:30:00', 5000)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- The index must match the sort order exactly
CREATE INDEX idx_orders_created_id
  ON orders(created_at DESC, id DESC);

The trade-off: keyset gives up random page jumps (no “go to page 50”). If the UI genuinely needs numbered pages, precompute page boundaries or cache the result set — don’t make the database pay for OFFSET on every request.

6. Connection Pooling

Slow queries aren’t always slow queries — sometimes they’re fast queries waiting 30s for a free connection. Check pool exhaustion before blaming the planner.

6.1 Pool Configuration

Setting	Recommended	Why
Pool size	~(core × 2) + spindles	More isn’t faster — see the classic PostgreSQL sizing rule
Min idle	= pool size	Keep connections warm
Max lifetime	30 min	Prevent stale/zombie connections
Connect timeout	30s	Fail fast on exhaustion
Idle timeout	10 min	Close genuinely idle connections
Validation query	SELECT 1	Health check before handout

6.2 PostgreSQL: PgBouncer

# pgBouncer — transaction pooling for apps that open/close often
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
pool_mode = transaction
max_client_conn = 200
default_pool_size = 20
min_pool_size = 5
server_idle_timeout = 600
server_lifetime = 3600

pool_mode = transaction is the right default for most apps — but remember it breaks session-level state (prepared statements across queries, SET session variables). Use session mode if you rely on those.

6.3 Application-Level Pooling

# SQLAlchemy pool config
engine = create_engine(
    'postgresql://user:pass@localhost/mydb',
    pool_size=20,
    max_overflow=10,
    pool_timeout=30,
    pool_recycle=1800,   # recycle before server kills the conn
    pool_pre_ping=True,  # drop dead connections transparently
)

7. Query Caching

Once the query itself is fast, the cheapest execution is the one you never run — cache results before tuning the plan again.

7.1 Cache Layers

Layer	TTL	When to use
Application	5–60s	Hot reads that tolerate staleness
Redis/Memcached	1–60min	Shared across app instances
Database	Session/refresh	Materialized views, result cache
CDN	1–24h	API responses, semi-static data

7.2 PostgreSQL Materialized Views

-- Expensive aggregation, computed once
CREATE MATERIALIZED VIEW order_summary AS
SELECT
  customer_id,
  COUNT(*) AS total_orders,
  SUM(total) AS total_spent,
  MAX(created_at) AS last_order_date
FROM orders
GROUP BY customer_id;

-- Index it like a table
CREATE INDEX idx_order_summary_customer
  ON order_summary(customer_id);

-- REFRESH blocks reads...
REFRESH MATERIALIZED VIEW order_summary;

-- ...unless you refresh CONCURRENTLY (needs a unique index)
REFRESH MATERIALIZED VIEW CONCURRENTLY order_summary;

8. Slow Query Log Triage

Indexes fix one query; the slow log finds the queries worth fixing. Enable it, rank by total time (not single-run time), and work the top of the list.

8.1 Enabling the Log

-- PostgreSQL: log everything over 100ms
ALTER SYSTEM SET log_min_duration_statement = 100;
SELECT pg_reload_conf();

-- MySQL: same idea
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.1;

8.2 Ranking the Offenders (pg_stat_statements)

-- Install once per database
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Top queries by total time — the real cost, not the slowest single run
SELECT
  calls,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(total_exec_time::numeric, 0) AS total_ms,
  left(query, 120) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Prioritize total_ms: a 50ms query called 2,000 times a minute costs more than a 5s report that runs nightly. The MySQL equivalent is performance_schema.events_statements_summary_by_digest.

See Also

• Complete Guide to SQL Query Optimization
• Database Indexing — Create Effective Indexes
• Read Replicas: Scale Reads Without Changing Application
• Complete Guide to PostgreSQL Tuning
• PostgreSQL EXPLAIN documentation
• pg_stat_statements documentation