SQL Tuning & Query Optimization

Most backend performance problems aren't caused by slow servers, bad code, or too much traffic. They're caused by one bad SQL query. A single missing index can make a query read every single row in a 10-million-row table instead of jumping straight to the 47 rows it needs. That's the difference between 89 milliseconds and 0.1 milliseconds. That's 699 times faster — from changing one line.

This page is the most hands-on page in the entire System Guide. Every section has real SQL you can copy, paste, and run. Real EXPLAIN ANALYZE output. Real slow query logs. Real before/after benchmarks. By the end, you'll know how to take any slow query, diagnose exactly where the time is going, and fix it — often in under 5 minutes.

You're on call for an e-commerce platform. It's Tuesday morning. Your P95 latencyThe 95th percentile response time — 95% of requests are faster than this number. If your P95 is 200ms, then 95 out of 100 requests complete in under 200ms. The remaining 5% are slower. has been a comfortable 50ms for months. You grab your coffee, open Grafana, and see this:

The deploy log shows a new feature went out Monday night: an "Order History" page that lets users see all their past orders. The feature works perfectly — it shows the right data, the UI looks great, the tests all pass. But behind that innocent-looking page is this query:

SELECT o.id, o.total, o.created_at, o.status,
       oi.product_name, oi.quantity, oi.price
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.user_id = $1
ORDER BY o.created_at DESC
LIMIT 20;

Looks completely reasonable, right? A simple join, a WHERE clause, a LIMIT. Every junior developer would write exactly this. And it runs fine in development with 500 rows. But production has 10 million orders and 45 million order items. Without an index on orders.user_id, the database has to read every single row in the orders table to find the ones belonging to user 42. That's a sequential scanAlso called a "full table scan." The database reads the table from the first page to the last page, checking every row against your WHERE condition. On a 10-million-row table, this means reading ~6,300 disk pages. Even with caching, it's orders of magnitude slower than an index scan. on 10 million rows. Every. Single. Request.

The answer: 99.9995% of the work is wasted. The database reads 10 million rows to return 47. And with a B-tree index, it would read about 3 index pages (to traverse the tree) plus ~47 data pages — roughly 50 page reads instead of 6,334. That's what we'll fix in this page.

What Made This Hard to Catch

This query passed every code review, every test, every staging environment check. Why? Because the staging database had 500 orders. At 500 rows, a sequential scan takes 0.05ms — perfectly fast. The problem only appears when the table has millions of rows. This is the fundamental trap of SQL performance: queries that are correct and fast on small data can be catastrophically slow on production data.

Your API is slow. Your instinct — and the instinct of every team under pressure — is to scale horizontally. Add more app servers. Bump the instance count from 4 to 8. Double the capacity, halve the problem, right?

So you spin up four more API servers behind your load balancer. Each server now handles half the requests it used to. Your CPU utilizationThe percentage of processing power being used. If your app servers show 30% CPU, the bottleneck isn't the app server — it's somewhere else (usually the database). on the app servers drops from 80% to 40%. You check the dashboard. Response times are... still 3 seconds.

Why didn't it work? Because the app servers weren't the bottleneck. They were sitting at 30% CPU, happily waiting for the database to respond. The database was the bottleneck. Every one of those 200 requests per second triggers the same sequential scan on the same 10-million-row table. Adding more app servers just means more connections all hammering the same slow database.

This is one of the most common mistakes in backend engineering: scaling the wrong layer. The app servers are fine. The network is fine. The load balancer is fine. The problem is a single SQL query doing a full table scan, and no amount of horizontal scaling on the app tier will fix it.

Read replicas don't fix it either — not really. Each replica has the exact same table without an index. Each one still does a sequential scan on 10 million rows. You've spread the load across 3 databases instead of 1, so each handles ~67 requests per second instead of 200. That buys you some breathing room, but each request still takes 89ms of pure scan time. You've spent $3,000/month on three new database instances to treat a symptom instead of fixing the cause.

The Real Numbers

Let's look at exactly what you're paying for the "throw hardware at it" approach versus fixing the actual query:

One line of SQL — CREATE INDEX idx_orders_user_id ON orders (user_id); — does more than $2,000/month of additional hardware. This is the entire argument for SQL tuning in one table: fix the query, not the infrastructure.

The "add servers" approach failed because the problem was never the servers. It was the database. Specifically, it was one (or more) of four problems that cause 95% of all database performance issues. Let's meet all four — because you'll encounter every single one of them in your career.

Horseman #1: Sequential Scan on a Big Table

We've already met this one. When you write WHERE user_id = 42 and there's no index on user_id, the database has no choice but to read the entire table. It starts at page 1 and reads every page until the end. On a 10-million-row table, that's thousands of disk pages.

Here's what that looks like in real EXPLAIN ANALYZE output — the exact output PostgreSQL gives you:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 42;

-- Output:
-- Seq Scan on orders  (cost=0.00..18334.00 rows=50 width=96)
--                     (actual time=0.031..89.442 rows=47 loops=1)
--   Filter: (user_id = 42)
--   Rows Removed by Filter: 999953
--   Buffers: shared hit=6334
-- Planning Time: 0.089 ms
-- Execution Time: 89.521 ms

Let's read that output like a doctor reads a blood test. Every line tells you something:

The smoking gun is Rows Removed by Filter: 999953. The database read a million rows and threw away 999,953 of them. That's like reading an entire phone book to find one person's number.

Horseman #2: The N+1 Query Problem

This one is sneaky because it doesn't show up as one slow query. It shows up as a thousand fast queries where you only needed two.

Here's the classic example. You want to show a list of users and how many orders each one has. The "obvious" code does this:

# BAD: N+1 — fires 1001 queries for 1000 users
users = User.all                    # Query 1: SELECT * FROM users
users.each do |user|
  puts user.orders.count            # Query 2..1001: SELECT COUNT(*)
end                                 #   FROM orders WHERE user_id = ?
# Total: 1 + 1000 = 1001 queries.
# At 1ms per query = 1001ms = over 1 SECOND just for DB calls.

# FIXED: eager loading — 2 queries total
users = User.includes(:orders).all  # Query 1: SELECT * FROM users
                                    # Query 2: SELECT * FROM orders
                                    #          WHERE user_id IN (1,2,3...)
users.each do |user|
  puts user.orders.size             # No query — already loaded
end
# Total: 2 queries. ~3ms.

# BAD: N+1
users = User.objects.all()           # 1 query
for user in users:
    print(user.order_set.count())    # N queries

# FIXED: prefetch_related
users = User.objects.prefetch_related('order_set').all()  # 2 queries
for user in users:
    print(len(user.order_set.all())) # No query — already loaded

-- BAD: N+1 (what the ORM generates)
SELECT * FROM users;                           -- query 1
SELECT COUNT(*) FROM orders WHERE user_id = 1; -- query 2
SELECT COUNT(*) FROM orders WHERE user_id = 2; -- query 3
SELECT COUNT(*) FROM orders WHERE user_id = 3; -- query 4
-- ... 997 more queries ...

-- FIXED: One query with JOIN
SELECT u.id, u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

The N+1 problem is the most common performance bug in web applications using ORMsObject-Relational Mappers — libraries like ActiveRecord (Rails), Django ORM, SQLAlchemy (Python), or Entity Framework (.NET) that let you write code instead of SQL. They're convenient but can generate inefficient queries if you're not careful.. It's called "N+1" because you fire 1 query to get N records, then 1 more query for EACH record. For 1,000 users, that's 1,001 database round trips instead of 2.

Horseman #3: Missing or Wrong Indexes

Sometimes you have an index, but the query can't use it. This is more frustrating than having no index at all, because you think you've solved the problem.

The three most common ways your index becomes useless:

-- You created this index:
CREATE INDEX idx_orders_created ON orders (created_at);

-- ❌ BROKEN: Function call on indexed column
-- The index stores raw dates, not YEAR(dates). Can't use the index.
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- ✅ FIXED: Rewrite to use the column directly
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- ❌ BROKEN: Implicit type cast
-- user_id is an integer column, but you pass a string.
-- Some databases cast the COLUMN to match, disabling the index.
SELECT * FROM orders WHERE user_id = '42';

-- ✅ FIXED: Use the correct type
SELECT * FROM orders WHERE user_id = 42;

-- ❌ BROKEN: Wrong column order in composite index
-- Index is (status, created_at) but you query by created_at only.
-- A composite index is like a phone book sorted by (last_name, first_name).
-- You can't look up by first_name alone — the book isn't sorted that way.
CREATE INDEX idx_status_date ON orders (status, created_at);
SELECT * FROM orders WHERE created_at > '2024-01-01'; -- Can't use index!

-- ✅ FIXED: Create index with the column you actually filter on FIRST
CREATE INDEX idx_date ON orders (created_at);
-- Or: query includes the leading column
SELECT * FROM orders WHERE status = 'shipped' AND created_at > '2024-01-01';

Horseman #4: Connection Exhaustion

Every database has a maximum number of simultaneous connections. PostgreSQL defaults to max_connections = 100. That sounds like a lot until you realize that slow queries hold connections open longer. If each query takes 89ms instead of 0.1ms, connections are occupied 890x longer. Your pool drains instantly.

Here's the real config you'd see in a PostgreSQL setup with PgBouncerA lightweight connection pooler for PostgreSQL. It sits between your app and the database, maintaining a small pool of real DB connections and multiplexing thousands of app connections through them. Essential for any production PostgreSQL deployment.:

[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp_production

[pgbouncer]
listen_port = 6432
max_client_conn = 1000     # App servers can open up to 1000 connections
default_pool_size = 25     # But only 25 real DB connections are open
reserve_pool_size = 5      # 5 extra for emergencies
pool_mode = transaction    # Return connection to pool after each transaction

# The math: 1000 app connections funnel into 25 DB connections.
# Each DB connection handles ~40 short queries per second.
# 25 × 40 = 1000 queries/sec capacity.
# Without PgBouncer: 1000 app connections = 1000 DB connections = PostgreSQL OOM.

Without a connection pooler, if you have 8 app servers each opening 125 connections, that's 1,000 connections to PostgreSQL. Each connection consumes about 10 MB of memory on the database server. That's 10 GB just for connection overhead — before your database stores a single byte of data. PgBouncer funnels those 1,000 app connections through just 25 real database connections, cutting memory usage by 97%.

All four horsemen have one thing in common: you can't fix what you can't see. You can stare at your SQL all day and it looks fine. The query is correct. It returns the right data. The problem is invisible in the code — it's in the execution plan the database chose.

That's what EXPLAIN ANALYZE gives you. It's like an X-ray for your query. Instead of just running the query and giving you results, the database also tells you exactly how it found those results — which tables it scanned, which indexes it used (or didn't), how many rows it read, and how long each step took.

The fix is two commands. One to create the index, and one to verify the database uses it:

-- Step 1: Create the index (takes ~5 seconds on 10M rows)
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders (user_id);
-- CONCURRENTLY = don't lock the table while building the index.
-- Without it, your table is locked for writes during index creation.

-- Step 2: Verify the fix
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 42;

-- Output (AFTER index):
-- Index Scan using idx_orders_user_id on orders
--   (cost=0.42..52.18 rows=50 width=96)
--   (actual time=0.028..0.089 rows=47 loops=1)
--   Index Cond: (user_id = 42)
--   Buffers: shared hit=50
-- Planning Time: 0.113 ms
-- Execution Time: 0.128 ms

Read those two outputs side by side. Same query. Same data. Same server. The only difference is one CREATE INDEX statement. The planner went from "I guess I'll read everything" to "I know exactly where that data is." From 89.5ms to 0.128ms. From 6,334 page reads to 50.

Try It Yourself — Right Now

This is the most important hands-on exercise on this page. Copy these commands, run them, and see the difference with your own eyes:

# 1. Start PostgreSQL (Docker)
docker run --name pg-tuning -e POSTGRES_PASSWORD=test \
  -p 5432:5432 -d postgres:16

# 2. Connect
docker exec -it pg-tuning psql -U postgres

# 3. Create table with 1M rows (takes ~3 seconds)
CREATE TABLE orders (
  id         SERIAL PRIMARY KEY,
  user_id    INT NOT NULL,
  total      NUMERIC(10,2),
  status     TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO orders (user_id, total, status)
SELECT
  (random() * 20000)::int,          -- 20K distinct users
  (random() * 500 + 5)::numeric(10,2),
  (ARRAY['pending','shipped','delivered','cancelled'])[floor(random()*4+1)::int]
FROM generate_series(1, 1000000);

-- 4. Run BEFORE (no index) — note the Seq Scan and time
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 42;

-- 5. Create the index
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- 6. Run AFTER (with index) — note the Index Scan and time
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 42;

-- 7. Compare the two outputs. You'll see:
--    Seq Scan → Index Scan
--    ~80ms   → ~0.1ms
--    ~6000 buffers → ~50 buffers

Finding Your Slow Queries in Production

You can't run EXPLAIN ANALYZE on queries you don't know about. In production, you need to find the slow ones first. Here are the real tools:

-- Method 1: Slow query log
-- In postgresql.conf:
-- log_min_duration_statement = 100
-- This logs every query that takes more than 100ms.
-- Check your PostgreSQL log file for the output.

-- Method 2: pg_stat_statements (MUCH better)
-- Enable it first:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Then find your 10 slowest queries RIGHT NOW:
SELECT
  query,
  calls,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round(total_exec_time::numeric, 2) AS total_ms,
  rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- This shows you EXACTLY which queries are slow,
-- how often they run, and how much total time they consume.
-- The "total_ms" column is the most important —
-- a query averaging 5ms that runs 1M times
-- is worse than a query averaging 500ms that runs once.

-- Enable slow query log (run as admin):
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.1;  -- log queries > 100ms
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Check it's enabled:
SHOW VARIABLES LIKE 'slow_query%';

-- Find slow queries:
-- The log file is at: /var/lib/mysql/hostname-slow.log
-- Or use mysqldumpslow to summarize:
-- mysqldumpslow -s t /var/lib/mysql/hostname-slow.log

-- MySQL equivalent of pg_stat_statements:
-- Enable Performance Schema (usually on by default):
SELECT
  DIGEST_TEXT AS query,
  COUNT_STAR AS calls,
  ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms,
  SUM_ROWS_EXAMINED AS rows_scanned,
  SUM_ROWS_SENT AS rows_returned
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

You now know the four problems and the breakthrough tool (EXPLAIN ANALYZE). This section is the reference manual — six essential topics, each with real commands you can run. Think of these as the six tools in your SQL tuning toolbox.

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, oi.product_name, oi.quantity
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.user_id = 42
ORDER BY o.created_at DESC
LIMIT 20;

-- Limit  (cost=298.15..298.20 rows=20 width=52)
--        (actual time=0.192..0.198 rows=20 loops=1)
--   -> Sort  (cost=298.15..298.28 rows=50 width=52)
--            (actual time=0.190..0.193 rows=20 loops=1)
--         Sort Key: o.created_at DESC
--         Sort Method: top-N heapsort  Memory: 27kB
--         -> Nested Loop  (cost=0.85..297.05 rows=50 width=52)
--                         (actual time=0.038..0.158 rows=47 loops=1)
--               -> Index Scan using idx_orders_user_id on orders o
--                  (cost=0.42..52.18 rows=50 width=24)
--                  (actual time=0.025..0.055 rows=47 loops=1)
--                     Index Cond: (user_id = 42)
--                     Buffers: shared hit=50
--               -> Index Scan using idx_order_items_order_id on order_items oi
--                  (cost=0.43..4.87 rows=1 width=36)
--                  (actual time=0.001..0.002 rows=1 loops=47)
--                     Index Cond: (order_id = o.id)
--                     Buffers: shared hit=94
-- Planning Time: 0.324 ms
-- Execution Time: 0.241 ms

-- Selective query (few rows) → Index Scan
EXPLAIN SELECT * FROM orders WHERE user_id = 42;
-- Index Scan using idx_orders_user_id on orders
-- (the planner knows user_id=42 matches ~50 rows out of 1M)

-- Non-selective query (many rows) → Seq Scan
EXPLAIN SELECT * FROM orders WHERE status = 'shipped';
-- Seq Scan on orders  Filter: (status = 'shipped')
-- (the planner knows 'shipped' matches ~250K rows = 25% of table.
--  Seq Scan is FASTER here because sequential I/O beats random I/O)

-- Index Only Scan — create a covering index:
CREATE INDEX idx_orders_covering ON orders (user_id) INCLUDE (total, created_at);
EXPLAIN SELECT total, created_at FROM orders WHERE user_id = 42;
-- Index Only Scan using idx_orders_covering on orders
-- (everything the query needs is IN the index — no table access!)

-- Simple: index on the column you filter by
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Now this query uses the index:
SELECT * FROM orders WHERE user_id = 42;

-- But this query CANNOT use it:
SELECT * FROM orders WHERE status = 'shipped';
-- (different column — needs its own index)

-- Index on (user_id, status) — order matters!
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

-- ✅ Can use index (leading column):
SELECT * FROM orders WHERE user_id = 42;

-- ✅ Can use index (both columns):
SELECT * FROM orders WHERE user_id = 42 AND status = 'shipped';

-- ❌ CANNOT use index (skips leading column):
SELECT * FROM orders WHERE status = 'shipped';

-- Rule: the "leftmost prefix" rule.
-- Index on (A, B, C) supports:
--   WHERE A = ?          ✅
--   WHERE A = ? AND B = ? ✅
--   WHERE A = ? AND B = ? AND C = ? ✅
--   WHERE B = ?          ❌ (skips A)
--   WHERE C = ?          ❌ (skips A and B)

-- You only ever query "active" orders. Why index the other 90%?
CREATE INDEX idx_active_orders ON orders (user_id)
WHERE status != 'cancelled' AND status != 'archived';

-- Index size: covers ~10% of the table = 90% smaller!
-- Works for:
SELECT * FROM orders WHERE user_id = 42 AND status = 'pending';

-- Doesn't work for:
SELECT * FROM orders WHERE user_id = 42 AND status = 'cancelled';
-- (cancelled rows aren't in the index)

-- Real-world example: soft-deleted rows
CREATE INDEX idx_non_deleted_users ON users (email)
WHERE deleted_at IS NULL;
-- Most queries filter WHERE deleted_at IS NULL anyway.
-- This index is tiny and blazing fast.

-- Your dashboard shows: user's recent orders with totals
SELECT total, created_at FROM orders
WHERE user_id = 42 ORDER BY created_at DESC LIMIT 10;

-- Regular index → Index Scan (looks up index, then reads table):
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- Covering index → Index Only Scan (never touches table):
CREATE INDEX idx_orders_user_covering
ON orders (user_id, created_at DESC) INCLUDE (total);

-- The INCLUDE columns are stored IN the index but not used for sorting.
-- The database finds user_id=42 in the index, grabs total + created_at
-- from the index itself, and returns. Zero table access.

-- Verify with EXPLAIN:
-- "Index Only Scan using idx_orders_user_covering"
-- (If you see "Index Scan" instead, run VACUUM on the table first.
--  PostgreSQL needs visibility info to be sure the index-only path is safe.)

# Gemfile
gem 'bullet', group: :development

# config/environments/development.rb
config.after_initialize do
  Bullet.enable = true
  Bullet.alert  = true   # Browser popup when N+1 detected
  Bullet.console = true   # Console warning
  Bullet.rails_logger = true
end

# Now when you load a page with N+1 queries,
# Bullet pops up a warning:
#
# "N+1 Query detected:
#  User => [:orders]
#  Add to your query: .includes(:orders)"

# pip install nplusone

# settings.py
INSTALLED_APPS += ['nplusone.ext.django']
MIDDLEWARE += ['nplusone.ext.django.NPlusOneMiddleware']
NPLUSONE_RAISE = True  # Raise exception on N+1 (dev only!)

# Output when N+1 detected:
# nplusone.core.exceptions.NPlusOneError:
#   Potential N+1 query detected on `User.order_set`

-- N+1 queries show up as a query with VERY high "calls" count
-- relative to other queries.

SELECT query, calls, mean_exec_time, rows
FROM pg_stat_statements
WHERE query LIKE '%orders%'
ORDER BY calls DESC LIMIT 5;

-- If you see:
--   "SELECT COUNT(*) FROM orders WHERE user_id = $1"
--   calls: 847,293
--   mean_exec_time: 0.4ms
--
-- That's 847K calls doing the same parameterized query.
-- Classic N+1. Total time: 847K × 0.4ms = 339 seconds of DB time.
-- Fix: one JOIN query, ~2ms total.

# Initialize pgbench tables
pgbench -i -s 50 mydb

# Benchmark WITHOUT PgBouncer (direct to PostgreSQL)
pgbench -c 100 -j 4 -T 60 -h localhost -p 5432 mydb
# Result: tps = 1,847 (including connections establishing)
# Many "connection refused" errors at high concurrency

# Benchmark WITH PgBouncer (connect through pooler)
pgbench -c 100 -j 4 -T 60 -h localhost -p 6432 mydb
# Result: tps = 4,213 (including connections establishing)
# Zero connection errors. 2.3x throughput.

-- Without prepared statement: parse + plan EVERY time
SELECT * FROM orders WHERE user_id = 42;   -- plan: 0.1ms + exec: 0.1ms
SELECT * FROM orders WHERE user_id = 99;   -- plan: 0.1ms + exec: 0.1ms
SELECT * FROM orders WHERE user_id = 7;    -- plan: 0.1ms + exec: 0.1ms
-- 10,000 calls = 10,000 × 0.1ms planning = 1 second wasted

-- With prepared statement: plan ONCE, execute many
PREPARE get_orders (int) AS
  SELECT * FROM orders WHERE user_id = $1;

EXECUTE get_orders(42);  -- plan: 0ms (cached) + exec: 0.1ms
EXECUTE get_orders(99);  -- plan: 0ms (cached) + exec: 0.1ms
EXECUTE get_orders(7);   -- plan: 0ms (cached) + exec: 0.1ms
-- 10,000 calls = 0ms planning + 10,000 × 0.1ms = 1 second total
-- Saved: 1 second of pure planning overhead.

-- Most ORMs do this automatically. Check yours:
-- Rails: config.active_record.prepared_statements = true (default)
-- Django: uses server-side prepared statements with psycopg2
-- SQLAlchemy: use_native_hstore=True, use prepared by default

Section 6 gave you the tools. This section explains the engine behind them — the internals that determine why the planner picks one plan over another, why your table gets slower over time even without schema changes, and how to handle tables that outgrow a single machine.

-- These are the knobs the planner uses to estimate costs:
SHOW seq_page_cost;          -- 1.0 (cost of reading one sequential page)
SHOW random_page_cost;       -- 4.0 (cost of reading one random page)
SHOW cpu_tuple_cost;         -- 0.01 (cost of processing one row)
SHOW cpu_index_tuple_cost;   -- 0.005 (cost of processing one index entry)

-- The ratio matters: random_page_cost / seq_page_cost = 4.0
-- This tells the planner: "random I/O is 4x more expensive than sequential I/O."
-- On SSDs, this gap is smaller. Many production setups change it:
SET random_page_cost = 1.1;  -- For SSD-backed databases
-- This makes the planner MORE willing to use Index Scan
-- (because random I/O on SSD is almost as fast as sequential).

-- See what the planner knows about your orders table:
SELECT
  attname AS column,
  n_distinct,        -- How many distinct values (-1 = all unique)
  most_common_vals,  -- Most frequent values
  most_common_freqs, -- How frequent each is
  correlation        -- How physically ordered the data is (1.0 = perfectly sorted)
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'user_id';

-- If n_distinct is wrong, the planner makes bad row estimates,
-- which leads to bad plan choices (e.g., Seq Scan when Index Scan is better).

-- Force a statistics refresh:
ANALYZE orders;

-- For columns with unusual distributions, increase the sample size:
ALTER TABLE orders ALTER COLUMN user_id SET STATISTICS 1000;
-- Default is 100. Higher = more accurate stats, slower ANALYZE.

-- Check how many dead rows your table has:
SELECT
  relname AS table,
  n_live_tup AS live_rows,
  n_dead_tup AS dead_rows,
  round(n_dead_tup::numeric / greatest(n_live_tup, 1) * 100, 1) AS dead_pct,
  last_vacuum,
  last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';

-- If dead_pct > 20%, you have a bloat problem.

-- Manual VACUUM (reclaims dead rows for reuse):
VACUUM orders;

-- VACUUM ANALYZE (also updates planner statistics):
VACUUM ANALYZE orders;

-- Nuclear option — VACUUM FULL (rewrites entire table, locks it):
-- Only use this during maintenance windows!
VACUUM FULL orders;
-- This reclaims disk space. Regular VACUUM just marks dead rows as reusable
-- but doesn't shrink the file on disk.

-- Check autovacuum settings:
SHOW autovacuum_vacuum_threshold;       -- default: 50
SHOW autovacuum_vacuum_scale_factor;    -- default: 0.2
-- Autovacuum runs when: dead rows > threshold + scale_factor × live_rows
-- For a 1M row table: 50 + 0.2 × 1M = 200,050 dead rows before autovacuum.
-- For high-write tables, lower the scale factor:
ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.05);

-- Create a partitioned table (PostgreSQL 10+):
CREATE TABLE orders (
  id          BIGSERIAL,
  user_id     INT NOT NULL,
  total       NUMERIC(10,2),
  status      TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

-- Create monthly partitions:
CREATE TABLE orders_2024_01 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE orders_2024_02 PARTITION OF orders
  FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- ... one per month

-- Now when you query:
SELECT * FROM orders WHERE created_at >= '2024-03-01' AND created_at < '2024-04-01';
-- PostgreSQL ONLY scans the orders_2024_03 partition.
-- The other 11 months are never touched. This is called "partition pruning."

-- Verify pruning is working:
EXPLAIN SELECT * FROM orders WHERE created_at >= '2024-03-01' AND created_at < '2024-04-01';
-- Should show: "Append" with only orders_2024_03 listed.
-- If all partitions appear, pruning failed (check your WHERE clause).

-- This CTE materializes ALL orders first, THEN filters.
-- On PG11 or with MATERIALIZED keyword:
WITH all_orders AS MATERIALIZED (
  SELECT * FROM orders            -- reads ALL 10M rows
)
SELECT * FROM all_orders
WHERE user_id = 42;               -- filters AFTER reading everything

-- EXPLAIN shows:
-- CTE Scan on all_orders
--   Filter: (user_id = 42)
--   Rows Removed by Filter: 999953
-- The CTE read the ENTIRE table. The WHERE clause was NOT pushed down.

-- Option 1: Use a subquery
SELECT * FROM (
  SELECT * FROM orders
) sub
WHERE user_id = 42;
-- Planner pushes the filter INTO the subquery → Index Scan.

-- Option 2: PG12+ non-materialized CTE (default behavior)
WITH user_orders AS (           -- NOT MATERIALIZED (default in PG12+)
  SELECT * FROM orders
)
SELECT * FROM user_orders
WHERE user_id = 42;
-- PG12+ inlines the CTE → same as subquery → Index Scan.

-- Option 3: Just write the simple query
SELECT * FROM orders WHERE user_id = 42;
-- The simplest is almost always the fastest.

Everything so far has been PostgreSQL-focused. But the principles — EXPLAIN ANALYZE, indexes, N+1 detection, connection pooling — apply to every relational database. The syntax changes. The thinking doesn't. Here are the three most common variations you'll encounter.

-- PostgreSQL: tree-structured output
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 42;

-- Key output:
-- Index Scan using idx_orders_user_id on orders
--   Index Cond: (user_id = 42)
--   Buffers: shared hit=50
--   Execution Time: 0.128 ms

-- Pro tip: use FORMAT JSON for programmatic parsing:
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders WHERE user_id = 42;

-- Or paste your plan into: https://explain.dalibo.com/
-- for a beautiful visual breakdown.

-- MySQL: tabular output
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

-- Key columns to read:
-- +----+------+------+---------+------+------+----------+-------+
-- | id | type | key  | key_len | ref  | rows | filtered | Extra |
-- +----+------+------+---------+------+------+----------+-------+
-- |  1 | ref  | idx_user | 4   | const|   47 |  100.00  | NULL  |
-- +----+------+------+---------+------+------+----------+-------+

-- type = "ref" means index lookup (good).
-- type = "ALL" means full table scan (bad).
-- key = which index was used (NULL = no index used).
-- rows = estimated rows to examine.
-- filtered = what % of rows pass the WHERE filter.

-- MySQL 8.0+: EXPLAIN ANALYZE (like PostgreSQL):
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;
-- Shows actual execution times, not just estimates.

-- MySQL-specific: EXPLAIN FORMAT=TREE (cleaner than table):
EXPLAIN FORMAT=TREE SELECT * FROM orders WHERE user_id = 42;

# See the SQL Rails generates:
Order.where(user_id: 42).to_sql
# => "SELECT \"orders\".* FROM \"orders\" WHERE \"orders\".\"user_id\" = 42"

# See EXPLAIN output from Rails:
Order.where(user_id: 42).explain
# => EXPLAIN for: SELECT ...
#    Index Scan using idx_orders_user_id ...

# Add index via migration:
class AddUserIdIndexToOrders < ActiveRecord::Migration[7.1]
  def change
    add_index :orders, :user_id, algorithm: :concurrently
  end
end
# algorithm: :concurrently → non-blocking (requires disable_ddl_transaction!)

# Fix N+1 with includes:
User.includes(:orders).where(active: true).each do |user|
  user.orders.size  # No extra query — already loaded
end

# Fix N+1 with joins (when you need to filter):
User.joins(:orders).where(orders: { status: 'pending' }).distinct

# See the SQL Django generates:
qs = Order.objects.filter(user_id=42)
print(qs.query)
# SELECT ... FROM "orders" WHERE "orders"."user_id" = 42

# See all queries executed in a view (debug toolbar):
# pip install django-debug-toolbar
# Shows every SQL query, timing, and duplicates.

# Add index in the model:
class Order(models.Model):
    user_id = models.IntegerField(db_index=True)  # single column
    # Or composite:
    class Meta:
        indexes = [
            models.Index(fields=['user_id', 'status']),
        ]

# Fix N+1 with select_related (ForeignKey) or prefetch_related (M2M):
users = User.objects.prefetch_related('order_set').filter(active=True)
for user in users:
    len(user.order_set.all())  # No extra query

# Use .only() to select specific columns (avoid SELECT *):
Order.objects.filter(user_id=42).only('id', 'total', 'created_at')

# See the SQL SQLAlchemy generates:
from sqlalchemy import create_engine
engine = create_engine("postgresql://...", echo=True)  # echo=True logs all SQL

# Or compile a query object:
stmt = select(Order).where(Order.user_id == 42)
print(stmt.compile(compile_kwargs={"literal_binds": True}))

# Add index:
from sqlalchemy import Index
Index('idx_orders_user_id', Order.user_id)

# Fix N+1 with joinedload or subqueryload:
from sqlalchemy.orm import joinedload
session.query(User).options(
    joinedload(User.orders)  # JOIN in single query
).filter(User.active == True).all()

# Or selectinload (separate SELECT IN query — often better):
from sqlalchemy.orm import selectinload
session.query(User).options(
    selectinload(User.orders)  # SELECT * FROM orders WHERE user_id IN (...)
).all()

# config/database.yml (Rails 6+):
production:
  primary:
    adapter: postgresql
    host: primary-db.internal
    database: myapp
  primary_replica:
    adapter: postgresql
    host: replica-db.internal
    database: myapp
    replica: true

# Rails automatically routes:
# - GET requests → replica
# - POST/PUT/DELETE → primary
# - After a write, reads stick to primary for 2 seconds
#   (to avoid reading stale data from replica lag)

# Manual control:
ActiveRecord::Base.connected_to(role: :reading) do
  Order.where(user_id: 42)  # Guaranteed to hit replica
end

ActiveRecord::Base.connected_to(role: :writing) do
  Order.create!(user_id: 42, total: 99.99)  # Guaranteed to hit primary
end

The ideal index for that scenario: a composite partial index. Something like: CREATE INDEX idx_events_user_type_date ON events (user_id, event_type, created_at DESC) WHERE created_at > now() - interval '90 days'; This gives you: the leftmost prefix for filtering (user_id, event_type), the sort order in the index (created_at DESC — avoiding a separate sort step), and a partial index that only covers recent data (90% smaller than a full index). If you also INCLUDE the columns your query SELECTs, you get an Index Only Scan — the fastest possible path.

Everything we've covered works great on a single database. But what happens when your product has millions of users and billions of rows? Let's look at four companies that deal with massive SQL workloads every single day — and how they keep things fast.

Some SQL tuning advice gets repeated so often that people assume it's true. It's not. These three "best practices" sound reasonable but will actively hurt you in production. Learn to spot them so you don't fall for them on your next project (or in your next interview).

These aren't hypothetical. These are the mistakes that show up in every production postmortem, every performance review, every "why is the database slow" Slack thread. If you can avoid all six, you're already ahead of 90% of backend developers.

-- Find foreign keys without matching indexes
SELECT conrelid::regclass AS table_name,
       a.attname AS fk_column,
       confrelid::regclass AS referenced_table
FROM pg_constraint c
JOIN pg_attribute a ON a.attnum = ANY(c.conkey)
  AND a.attrelid = c.conrelid
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND a.attnum = ANY(i.indkey)
  );

-- Check when VACUUM and ANALYZE last ran
SELECT relname, n_dead_tup, last_vacuum, last_autovacuum,
       last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

-- Check index sizes for a specific table
SELECT pg_size_pretty(pg_indexes_size('orders')) AS total_index_size;

-- See individual indexes and their sizes
SELECT indexrelname AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
       idx_scan AS times_used
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY pg_relation_size(indexrelid) DESC;

This is the most common database question in system design and backend interviews. It sounds simple, but the depth of your answer reveals your experience level instantly. Here's what interviewers expect at each level — with sample answers you can adapt.

Reading about SQL tuning is useful, but running real queries is what makes it stick. These five exercises go from "run one command" to "design an indexing strategy for a real schema." Try each one before opening the hints.

Six cards with everything you need when you're debugging a slow query, tuning a database, or prepping for an interview. All real commands — copy-paste ready.

-- Basic plan (estimated)
EXPLAIN SELECT ...;

-- Actual timing + buffers
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

-- JSON for tools
EXPLAIN (ANALYZE, FORMAT JSON) SELECT ...;

-- Visual: explain.dalibo.com
-- Key: look for Seq Scan on large tables
-- Key: actual rows ≫ estimated = stale stats

Index Scan        → best for selective queries
Index Only Scan   → even better (no table hit)
Bitmap Index Scan → good for medium selectivity
Seq Scan (small)  → fine on <10K rows
Seq Scan (large)  → RED FLAG — add an index

Nested Loop  → good for small inner table
Hash Join    → good for large equi-joins
Merge Join   → good when both sides sorted

WHERE col = ?    → B-tree on col
WHERE a = ? AND b = ? → composite (a, b)
ORDER BY col DESC → include col in index
LIKE 'abc%'      → B-tree works
LIKE '%abc%'     → GIN + pg_trgm
Partial:  WHERE active = true
Covering: INCLUDE (col1, col2)

Leftmost prefix rule:
  idx(a, b, c) supports:
  WHERE a = ?  ✓
  WHERE a = ? AND b = ?  ✓
  WHERE b = ?  ✗ (can't skip a)

-- Top 5 by total time
SELECT query, calls,
  total_exec_time::int AS total_ms,
  mean_exec_time::int AS avg_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

-- Top 5 by call count
...ORDER BY calls DESC LIMIT 5;

-- Reset stats
SELECT pg_stat_statements_reset();

# pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
reserve_pool_size = 5
reserve_pool_timeout = 3

# transaction mode = best for web apps
# session mode = needed for prepared stmts
# statement mode = most aggressive pooling

# postgresql.conf
log_min_duration_statement = 200
# Logs any query slower than 200ms

# Find currently running slow queries:
SELECT pid, now() - query_start AS duration,
  query, state
FROM pg_stat_activity
WHERE state != 'idle'
  AND now() - query_start > '1 second'::interval
ORDER BY duration DESC;

# Kill a stuck query:
SELECT pg_cancel_backend(pid);

SQL tuning doesn't live in isolation. It connects to almost every part of backend engineering — from how you design schemas to how you scale entire systems. Here's how the skills you've learned here map to the bigger picture.