SQL query tuning

Plan-reading workflow (ASCII)

Typical read order from statement to actionable hypotheses; operator names vary by engine but the layering is the same.

-- EXPLAIN ANALYZE real output example (PostgreSQL)
-- Slow query: full table scan
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 12345 AND status = 'pending';

-- Output without index:
-- Seq Scan on orders  (cost=0.00..4821.00 rows=3 width=128)
--                     (actual time=0.042..23.847 rows=3 loops=1)
--   Filter: ((user_id = 12345) AND (status = 'pending'))
--   Rows Removed by Filter: 98997
-- Planning Time: 0.124 ms
-- Execution Time: 23.923 ms    <- full scan of 99000 rows returning only 3
-- Buffers: shared hit=2257

-- After creating a composite index:
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

-- Optimized output:
-- Index Scan using idx_orders_user_status on orders
--              (cost=0.42..12.43 rows=3 width=128)
--              (actual time=0.024..0.031 rows=3 loops=1)
--   Index Cond: ((user_id = 12345) AND (status = 'pending'))
-- Planning Time: 0.201 ms
-- Execution Time: 0.058 ms    <- down from 23ms to 0.06ms, 400x improvement
-- Buffers: shared hit=5

-- Key signals:
-- 1. Seq Scan + large Rows Removed by Filter -> missing index
-- 2. actual rows vs estimated rows diverge -> stale stats (run ANALYZE)
-- 3. Sort / Hash / Temp -> work_mem insufficient (adjust session-level for testing)
-- 4. Nested Loop with large driver table rows -> possible wrong join order

┌─────────────┐     ┌──────────────┐     ┌─────────────────┐
│ Slow log /  │────▶│ EXPLAIN      │────▶│ Find costliest   │
│ SLO breach  │     │ (ANALYZE)    │     │ subtree: rows…   │
└─────────────┘     └──────────────┘     └────────┬────────┘
                                                  │
                    ┌─────────────────────────────┼─────────────────────────────┐
                    ▼                             ▼                             ▼
            ┌───────────────┐            ┌───────────────┐            ┌───────────────┐
            │ Access path   │            │ Join order /  │            │ Materialize / │
            │ Seq vs Index  │            │ NL / Hash /   │            │ Sort / Agg    │
            └───────┬───────┘            └───────┬───────┘            └───────┬───────┘
                    │                             │                             │
                    └─────────────────────────────┴─────────────────────────────┘
                                                  │
                                                  ▼
                                        ┌──────────────────┐
                                        │ Check stats      │
                                        │ ndv / hist / corr│
                                        └────────┬─────────┘
                                                  │
                                                  ▼
                                        ┌──────────────────┐
                                        │ Index/rewrite    │
                                        │ + reproducible   │
                                        │ validation steps │
                                        └──────────────────┘
            

Index heuristics

Rules of thumb—always confirm against real plans and data skew.

-- Composite index column order: equality first, range next, sort last
-- Query pattern: WHERE user_id = ? AND created_at > ? ORDER BY created_at
-- Correct order (equality column first):
CREATE INDEX idx_orders_uid_created
    ON orders (user_id, created_at DESC);
-- user_id equality filter followed by created_at ordered for ORDER BY (avoids Sort node)

-- Wrong order (range column first):
-- CREATE INDEX idx_orders_created_uid ON orders (created_at, user_id);
-- Result: user_id cannot leverage index ordering after created_at range scan

-- Covering index: avoid bookmark lookups (table heap reads)
-- If SELECT only needs user_id, status, amount, INCLUDE extra columns
CREATE INDEX idx_orders_cover
    ON orders (user_id, status)
    INCLUDE (amount, created_at);
-- Effect: Index Only Scan, no heap read needed

-- Partial index: low-cardinality column + common filter predicate
CREATE INDEX idx_orders_pending
    ON orders (user_id)
    WHERE status = 'pending';
-- Useful when 99% of rows are 'completed' and only 1% are 'pending'

N+1 queries

-- Anti-pattern: N+1 (pseudocode + SQL)
-- Query 1: SELECT id FROM users WHERE active = true  -> 1000 rows
-- Then for each user: SELECT * FROM orders WHERE user_id = ?  -> 1000 times
-- Total: 1001 queries

-- Fix: JOIN to fetch in one query
SELECT u.id, u.name, o.id AS order_id, o.amount, o.status
FROM   users u
LEFT   JOIN orders o ON o.user_id = u.id
WHERE  u.active = true;

-- Fix: IN batch prefetch (DataLoader pattern)
SELECT * FROM orders
WHERE  user_id = ANY(ARRAY[1,2,3,...,1000]);  -- single batch

-- IN vs EXISTS performance comparison
-- IN (efficient when subquery result set is small):
SELECT * FROM orders
WHERE  user_id IN (SELECT id FROM users WHERE plan = 'premium');

-- EXISTS (efficient when outer table is large and subquery short-circuits):
SELECT * FROM orders o
WHERE  EXISTS (
    SELECT 1 FROM users u
    WHERE  u.id = o.user_id AND u.plan = 'premium'
);
-- Rule: small subquery result -> IN; large outer table + indexed subquery -> EXISTS

-- Correlated subquery anti-pattern (executes once per row):
SELECT u.id, (SELECT SUM(amount) FROM orders WHERE user_id = u.id) AS total
FROM   users u;

-- Fix: rewrite as JOIN + GROUP BY (single scan):
SELECT u.id, COALESCE(SUM(o.amount), 0) AS total
FROM   users u
LEFT   JOIN orders o ON o.user_id = u.id
GROUP  BY u.id;

-- PgBouncer connection pool key parameters (pgbouncer.ini)
[databases]
payments = host=127.0.0.1 port=5432 dbname=payments

[pgbouncer]
pool_mode = transaction          ; transaction/session/statement
max_client_conn = 1000           ; max client connections
default_pool_size = 20           ; pool size per (db, user) pair
min_pool_size = 5                ; minimum persistent connections (avoid cold start)
reserve_pool_size = 5            ; emergency reserve connections
reserve_pool_timeout = 5         ; activate reserve after timeout (seconds)
max_db_connections = 100         ; total backend connections per database
server_idle_timeout = 600        ; close idle backend connections (seconds)
client_idle_timeout = 0          ; 0 = disable client timeout
auth_type = md5
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid

; Sizing: QPS=500, p95=50ms -> concurrency = 500 * 0.05 = 25
; Set default_pool_size to 25-30, with ~20% headroom

SQL keywords & rough clause order

Paste SQL below: common keywords highlight in sync, plus a rough relative clause order for reading only—not a real cost model.