Free Workshop

Query Optimization Workshop

A self-paced, practical workshop covering how to find, diagnose, and fix slow database queries — with real SQL examples throughout.

6 Modules
~100 min
Real SQL Examples
M1

How the Query Planner Works

~15 min

Before you can fix slow queries, you need to understand how PostgreSQL and SQL Server decide how to execute them. This module covers the fundamentals that everything else builds on.

  • What the query planner does and why it matters
  • Statistics: how the planner estimates row counts
  • Seq scan vs index scan vs index-only scan — when each is chosen
  • Hash join vs nested loop vs merge join — the planner's join strategies
  • How outdated statistics lead to bad plans

Core tool — always start here:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, c.name, o.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'open'
ORDER BY o.created_at DESC
LIMIT 50;

Key Takeaway

You cannot fix what you cannot measure. EXPLAIN ANALYZE is your most important tool — learn to read it before anything else.

M2

Reading EXPLAIN ANALYZE Output

~20 min

EXPLAIN ANALYZE output looks intimidating. This module breaks it down node by node so you can identify exactly where time is being lost in any query.

  • Reading the execution tree from innermost to outermost node
  • Actual vs estimated rows: spotting planner errors
  • Identifying the most expensive node by actual time
  • What 'Buffers: shared hit / read' tells you about disk vs cache
  • Spotting full sequential scans on large tables
  • Understanding 'loops' in nested loop joins

Example: reading a slow join

-- Look for nodes where actual rows >> estimated rows
-- That's where the planner is wrong and making bad decisions

-- Also look for:
-- -> Seq Scan on large_table (cost=0.00..48291.00 rows=2000000)
-- This is your problem node.

Key Takeaway

Find the node with the highest 'actual time' and the biggest gap between 'rows=X' (estimated) and 'actual rows=Y'. That is your starting point.

M3

Indexing for Speed

~20 min

Most performance problems can be solved with the right index. This module covers exactly when and how to create indexes that actually get used.

  • B-Tree indexes: the default and when to use them
  • Composite indexes: column order matters (selectivity first)
  • Partial indexes: index only the rows you query
  • Covering indexes (INCLUDE): eliminate heap fetches entirely
  • BRIN indexes for time-series data
  • Finding missing indexes via pg_stat_statements
  • Dropping unused indexes that slow down writes

Find missing index candidates:

-- Queries with high total_time and seq scans
SELECT query, calls, total_exec_time, rows,
  total_exec_time / calls AS avg_ms
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_%'
ORDER BY total_exec_time DESC
LIMIT 20;

Key Takeaway

Build indexes to match your WHERE, JOIN ON, and ORDER BY clauses. Column order in a composite index is not arbitrary — put the most selective column first.

M4

Eliminating N+1 and Rewriting Bad Queries

~15 min

Index tuning can't fix fundamentally broken query patterns. This module covers the query rewrites that deliver the biggest performance gains.

  • What N+1 is and how to identify it in query logs
  • Rewriting N+1 with a single JOIN or WHERE IN batch
  • Converting correlated subqueries to LEFT JOIN equivalents
  • Using CTEs (WITH clauses) for readable, plannable queries
  • Moving filtering to WHERE instead of HAVING
  • Avoiding SELECT * and fetching only columns you need

N+1 pattern → single query fix:

-- BAD: N+1 — one query per order
SELECT * FROM orders WHERE customer_id = $1;
-- (repeated 1000 times in a loop)

-- GOOD: batch in one query
SELECT o.*, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.customer_id = ANY($1::bigint[]);

Key Takeaway

If your application makes more than one database round-trip to produce a single output row, you almost certainly have an N+1 problem. One query, one result set.

M5

Tuning Memory and Configuration

~15 min

Query rewrites and indexes fix the immediate problems. Correct configuration makes everything faster by default. This module covers the settings that have the most impact.

  • shared_buffers: how much RAM PostgreSQL uses for caching (target 25% of RAM)
  • work_mem: memory per sort and hash operation (tune carefully — it multiplies)
  • effective_cache_size: tells the planner how much OS cache is available
  • max_connections and when to add PgBouncer connection pooling
  • Autovacuum tuning for high-write tables
  • checkpoint_completion_target and wal_buffers for write throughput

Check current cache hit ratio:

SELECT
  round(
    100.0 * sum(heap_blks_hit) /
    nullif(sum(heap_blks_hit) + sum(heap_blks_read), 0),
    2
  ) AS cache_hit_ratio_pct
FROM pg_statio_user_tables;
-- Target: > 95%

Key Takeaway

shared_buffers is the single highest-impact configuration setting. If your cache hit ratio is below 95%, increase it. But don't chase configuration until you've fixed query and index problems first.

M6

Building a Performance Monitoring Habit

~15 min

One-time tuning doesn't last. Slow queries appear as data grows and workloads change. This module shows you how to catch problems early with minimal overhead.

  • Enabling and using pg_stat_statements for query-level profiling
  • Setting up a weekly slow query review (top 20 by total time)
  • Alerting on p95 query latency, connection pool usage, and replication lag
  • Using auto_explain to log plans for queries over a threshold
  • Tracking index bloat and table bloat quarterly
  • pgBadger for log-based query analysis

Enable auto_explain for slow queries:

-- In postgresql.conf:
shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on

Key Takeaway

Review pg_stat_statements weekly. A query that ran in 50ms last month and now runs in 500ms is telling you something changed — catch it before it becomes an incident.

Want Us to Apply This to Your Database?

Book a free diagnostic call. We'll run these exact techniques against your specific query workload and deliver a prioritised fix list.

BOOK FREE DIAGNOSTIC