OFFSET Pagination: 50ms to 45s — Keyset Fix

A slow query isn’t a bug — it’s a design failure. SQL query optimisation is the process of reshaping how your database retrieves data, not by rewriting logic, but by understanding execution plans, join algorithms, and indexing strategies. Without it, you’re just throwing hardware at a software problem, and eventually even your 128-core server will choke on a table scan at 3 AM.

What SQL Query Optimisation Actually Is

SQL query optimisation is the process of restructuring queries and schema access patterns to minimise the database's work per result. It's not about writing clever SQL — it's about understanding how the engine reads data: full table scans, index seeks, nested loops, sorts, and memory grants. The core mechanic is reducing the number of rows the database must touch before returning your answer.

At its heart, optimisation exploits three properties: selectivity (how many rows an index eliminates), access path (sequential vs. random I/O), and cardinality estimates (the planner's guess at row counts). A query that returns 10 rows from a 10M-row table can take 50ms with a covering index or 45 seconds with a full scan and sort. The difference is not syntax — it's whether the engine can seek directly to the rows or must read and discard millions.

Use optimisation when latency matters under load. In production, the worst queries are not the complex ones — they're the simple ones that accidentally scan large tables because of a missing index or an unfiltered sort. The goal is always: make the database do O(log n) work instead of O(n).

The Optimisation Hierarchy — Always Measure First

Optimisation without measurement is guesswork. The correct workflow: run EXPLAIN ANALYZE on the slow query, identify the most expensive node, apply one targeted fix, run EXPLAIN ANALYZE again to verify the improvement. Never apply multiple changes simultaneously — you need to isolate which change had impact.

The optimisation hierarchy in order of impact and effort:

1. Indexes — adding the right index is the highest-impact, lowest-effort fix. A single index can reduce a 30-second query to 2 milliseconds. Always exhaust indexing options before rewriting queries.
2. Query rewrites — eliminate unnecessary work: replace SELECT * with column lists, eliminate correlated subqueries, rewrite N+1 patterns as single JOINs, switch OFFSET pagination to keyset pagination.
3. Schema changes — denormalisation, partitioning, archiving old data. Higher effort, sometimes necessary for extreme scale, but only after indexing and query rewrites are exhausted.

The tool for all three levels is EXPLAIN ANALYZE — it drives every decision.

-- STEP 1: Identify slow queries with pg_stat_statements
-- (requires pg_stat_statements extension)
SELECT
    query,
    calls,
    total_exec_time / calls AS avg_ms,
    rows / calls             AS avg_rows
FROM pg_stat_statements
ORDER BY avg_ms DESC
LIMIT 10;

-- STEP 2: Run EXPLAIN ANALYZE on the worst offender
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.order_id, c.name, o.total
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL '24 hours';

-- STEP 3: Apply targeted fix based on plan
-- If Seq Scan on orders(status, created_at):
CREATE INDEX idx_orders_status_created
    ON orders(status, created_at DESC)
    WHERE status = 'pending';  -- partial index: only pending rows

-- STEP 4: Verify improvement
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.order_id, c.name, o.total
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL '24 hours';

Common Anti-Patterns and How to Fix Them

Several query patterns consistently cause performance problems at scale. Recognising them by sight lets you fix them immediately.

SELECT * fetches every column from the table, including large text or BLOB columns you may not need. It prevents covering indexes from working (the engine must fetch the heap to get columns not in the index), increases network transfer between database and application, and makes query plans harder to cache effectively. Always list the specific columns you need.

Functions on indexed columns in WHERE silently disable the index. WHERE YEAR(created_at) = 2024 cannot use an index on created_at — the function transforms the value before comparison. The fix is always a range: WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'.

The N+1 problem is the most damaging ORM-related pattern: fetch a list of customers (1 query), then loop through and fetch each customer's orders separately (N queries). On a list of 100 customers, that's 101 queries. On a list of 1000, it's 1001. Fix with a single JOIN or eager loading.

-- ANTI-PATTERN 1: SELECT *
-- BAD: fetches all columns including large blobs, breaks covering indexes
SELECT * FROM orders WHERE customer_id = 42;

-- GOOD: only the columns you need
SELECT order_id, total, status, created_at
FROM orders
WHERE customer_id = 42;

-- ANTI-PATTERN 2: Function on indexed column
-- BAD: YEAR() disables the index on created_at
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- GOOD: range query uses the index
SELECT order_id, total FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at <  '2025-01-01';

-- ANTI-PATTERN 3: N+1 queries
-- BAD: one query for customers, then one per customer for orders
-- SELECT * FROM customers;  -- returns 1000 customers
-- then for each customer:
-- SELECT * FROM orders WHERE customer_id = :id;  -- 1000 separate queries!

-- GOOD: single JOIN fetches everything in one query
SELECT
    c.customer_id, c.name,
    o.order_id, o.total, o.created_at
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE c.customer_id IN (1, 2, 3, ..., 1000);  -- or use a subquery

-- ANTI-PATTERN 4: OFFSET pagination
-- BAD: OFFSET 50000 scans and discards 50000 rows
SELECT order_id, total FROM orders ORDER BY order_id LIMIT 20 OFFSET 50000;

-- GOOD: keyset pagination -- O(1) regardless of position
SELECT order_id, total FROM orders
WHERE order_id < :last_seen_id  -- last ID from previous page
ORDER BY order_id DESC
LIMIT 20;

Indexing Strategy for Query Optimisation

Most query optimisation problems are indexing problems. Before rewriting any query, verify that the right indexes exist and are being used.

The process: identify the slow query, run EXPLAIN ANALYZE, look for Seq Scan on the WHERE columns, create the appropriate index, run EXPLAIN ANALYZE again to verify it is used.

Partial indexes are underused but extremely effective: CREATE INDEX idx_pending_orders ON orders(created_at) WHERE status = 'pending'. This index only contains rows where status is 'pending' — it is much smaller than a full index on created_at, fits in cache more easily, and is highly selective for the specific query.

Composite index column order determines usability: put equality conditions first, range conditions last. An index on (status, created_at) supports WHERE status = 'pending' AND created_at > '2024-01-01' using both columns. An index on (created_at, status) would only use the created_at portion for a range query on created_at.

-- DIAGNOSIS: find tables with the most sequential scans
SELECT
    relname         AS table_name,
    seq_scan,
    seq_tup_read,
    idx_scan,
    seq_scan - idx_scan AS scan_gap
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
  AND n_live_tup > 10000
ORDER BY seq_tup_read DESC;

-- Standard composite index: equality first, range last
CREATE INDEX idx_orders_status_created
    ON orders(status, created_at DESC);

-- Partial index: only the rows you query (much smaller)
CREATE INDEX idx_active_sessions
    ON sessions(user_id, last_active)
    WHERE is_active = true;  -- only active sessions indexed

-- Covering index: all SELECT columns in the index
CREATE INDEX idx_order_summary
    ON orders(customer_id, status)
    INCLUDE (order_id, total, created_at);  -- SELECT columns in leaf nodes

-- Index for sort: ORDER BY without a filesort
CREATE INDEX idx_orders_created_desc
    ON orders(created_at DESC);
-- Now: SELECT order_id FROM orders ORDER BY created_at DESC LIMIT 20
-- uses index scan in sorted order -- no Sort node in EXPLAIN

-- Verify index usage after creation
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan DESC;

Join Algorithms — Nested Loop, Hash Join, Merge Join

When you write a JOIN between two tables, the query planner chooses an algorithm to combine rows. Knowing how each algorithm works helps you interpret EXPLAIN output and understand why some joins are orders of magnitude slower than others.

Nested Loop Join: For each row in the outer table, scan the inner table to find matches. If the inner side has an index, it becomes a fast index lookup — ideal when one side is small. Without an index, it degenerates into a full scan per outer row, leading to O(n * m) cost. EXPLAIN shows 'Nested Loop' and the loops count reveals how many times the inner side was scanned.

Hash Join: Build a hash table from the smaller table, then scan the larger table and probe the hash. Works best for equi-joins on large sets when one side fits in memory. The planner chooses this when tables are large and an index is not expected to help. EXPLAIN will show 'Hash Join' with a 'Hash' child node.

Merge Join: Sort both tables on the join key (or exploit existing index order) then merge them in a single pass. Preferable when both sides are already sorted (e.g., by index), or when the join result needs to be sorted. Without pre-sorted data, the sort cost can dominate. EXPLAIN shows 'Merge Join' with optional 'Sort' children.

The planner picks the algorithm based on estimated row counts, available indexes, and system configuration. When you see an unexpected choice, investigate statistics freshness or consider forcing a different join with join_collapse_limit (PostgreSQL) or optimizer hints.

-- Example: small orders table (10 rows) joined to large customers (1M rows)
-- Planner likely chooses Nested Loop with index on customers.customer_id
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.status = 'pending';
-- Plan: Nested Loop
--   -> Seq Scan on orders (cost=... rows=10)
--   -> Index Scan using customers_pkey on customers (cost=... rows=1)
-- loops: each outer row triggers one index lookup = 10 lookups total.

-- Example: large orders (5M) joined to large customers (2M) with no index on fk
-- Planner switches to Hash Join to avoid Nested Loop blowup
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.created_at > '2024-01-01';
-- Plan: Hash Join
--   -> Seq Scan on orders (filter: created_at)  -- build inner table
--   -> Hash
--         -> Seq Scan on customers (full scan to build hash)
-- Then probe hash for each matching order row.

-- When both sides are large and already sorted by the join key:
-- Merge Join avoids building a hash and handles range joins well.
-- Ensure an index exists on the join keys to skip explicit sort.

Sargability — Search Argumentable Conditions

Sargability (Search ARGument ABLE) is the property that determines whether a WHERE condition can use an index. If the column appears inside a function or is wrapped in an expression, the index is unusable. The database must evaluate the function on every row, forcing a full table scan.

Common sargability violations and their fixes:

1. Functions on columns: WHERE YEAR(created_at) = 2024 → fix: WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
2. Arithmetic on columns: WHERE price * 1.1 > 100 → fix: WHERE price > 100 / 1.1
3. Implicit type conversion: WHERE varchar_col = 12345 → fix: WHERE varchar_col = '12345' (use explicit literal type)
4. LIKE patterns with leading wildcard: WHERE name LIKE '%john%' → cannot be made sargable; consider full-text search.
5. Subqueries in WHERE NOT IN: WHERE id NOT IN (SELECT ...) → often rewritten as NOT EXISTS for better performance.

The checklist: before writing a WHERE clause

-- BAD (not sargable): function on column
SELECT * FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2024;

-- GOOD (sargable): range condition
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- BAD: arithmetic on column
SELECT * FROM products WHERE price * 1.1 > 50;

-- GOOD: move arithmetic to constant side
SELECT * FROM products WHERE price > 50 / 1.1;

-- BAD: implicit type conversion (varchar column compared to integer)
SELECT * FROM users WHERE user_id = 12345;  -- user_id is VARCHAR

-- GOOD: match the column type
SELECT * FROM users WHERE user_id = '12345';

-- BAD: leading wildcard (never sargable)
SELECT * FROM customers WHERE name LIKE '%smith%';

-- Alternative: full-text search with GIN index
-- CREATE INDEX idx_customers_name_trgm ON customers USING gin(name gin_trgm_ops);

-- BAD: NOT IN with subquery (often becomes anti-join)
SELECT * FROM orders WHERE customer_id NOT IN (SELECT id FROM suspended_customers);

-- GOOD: NOT EXISTS (often better plan)
SELECT * FROM orders WHERE NOT EXISTS (SELECT 1 FROM suspended_customers WHERE id = orders.customer_id);

Selectivity vs Cardinality — How the Planner Chooses

The query planner lives and dies by its estimates of row counts. Two concepts drive those estimates: cardinality and selectivity.

Cardinality is the number of distinct values in a column. A primary key has cardinality equal to the row count. A boolean column has cardinality 2. High cardinality (many distinct values) means an index scan is likely efficient — few rows match each value. Low cardinality (few distinct values) means an index scan is wasteful because many rows match each value and a sequential scan would be faster.

Selectivity is the fraction of rows that pass a filter. A predicate like status = 'pending' on a table where 90% of rows have status 'pending' has low selectivity (0.9). A predicate like id = 42 on a primary key has high selectivity (1/rowcount). The planner multiplies selectivity by total row count to estimate row output.

The planner uses these estimates to choose join algorithms and access methods: - High selectivity (small fraction of rows): index scan is preferred. - Low selectivity (large fraction of rows): sequential scan is preferred even if an index exists. - For joins, the planner uses estimated cardinalities to choose build vs probe side in hash joins, and whether to sort for merge joins.

When statistics are stale, selectivity estimates are wrong, leading to bad plans. Always run ANALYZE after bulk inserts or after noticing a regression.

-- Check cardinality and selectivity for a column
SELECT
    n_distinct AS distinct_values,
    CASE
        WHEN n_distinct > 0 THEN round(reltuples / n_distinct)::bigint
        ELSE NULL
    END AS avg_rows_per_value,
    reltuples AS total_rows,
    avg_width
FROM pg_class c
JOIN pg_attribute a ON a.attrelid = c.oid
JOIN pg_stats s ON s.tablename = c.relname AND s.attname = a.attname
WHERE c.relname = 'orders' AND a.attname = 'status';

-- Estimate selectivity: fraction of rows matching a value
-- For status = 'pending', if n_distinct = 3 and reltuples = 5,000,000
-- selectivity = 1/3 ≈ 0.33, estimated rows = 1.67M
-- The planner will choose Seq Scan because 33% of table is too many for index.

-- Verify statistics are current:
SELECT relname, n_live_tup, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders';

Avoid `SELECT *` Like It's a Production Pager at 3 AM

SELECT * is the duct tape of SQL. It works until it doesn't. When you blast every column back to the app, you're burning I/O, memory, and network bandwidth on data nobody asked for.

The real cost? Index-only scans die. The moment you add a column that's not in a covering index, the planner falls back to clustered index scans or heap lookups. Your carefully crafted composite index becomes a waste of disk space.

The fix is surgical: name the columns you actually need. If the query changes, update the list. Yes, it's more typing. Delete your ORM magic and write the damn column list.

Bonus: Your code becomes self-documenting. Future you won't have to grep the application layer to figure out what the query actually returns.

// io.thecodeforge — database tutorial

-- Before: 17 columns, 3 MB of data, 480 ms
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2024-01-01';

-- After: 5 columns, 240 KB of data, 62 ms
SELECT o.order_id, o.total, o.status,
       c.customer_name, c.email
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2024-01-01';

Kill Correlated Subqueries Before They Kill Your SLA

Correlated subqueries look elegant in a textbook. In production, they're a row-by-row operations disguised as SQL. The database executes the inner query for every single row in the outer result set. For a table with 100K rows, that's 100K executions.

The fix is almost always a JOIN or a window function. JOINs let the optimizer build a hash or merge plan. Window functions compute aggregates over ordered partitions in a single pass. Both scale logarithmically or linearly, not quadratically.

If you must use a subquery, verify it gets flattened into a JOIN by checking the execution plan. If you see "DEPENDENT SUBQUERY" in EXPLAIN output, you've got a problem.

Pro tip: EXISTS often outperforms IN for correlated subqueries because it short-circuits on the first match. But nothing beats rewriting it as a proper JOIN.

// io.thecodeforge — database tutorial

-- Painful: Correlated subquery, 4.2 seconds
SELECT o.order_id, o.total
FROM orders o
WHERE o.total > (
    SELECT AVG(o2.total)
    FROM orders o2
    WHERE o2.customer_id = o.customer_id
);

-- Lean: Window function, 0.3 seconds
WITH customer_avg AS (
    SELECT order_id, total,
           AVG(total) OVER (PARTITION BY customer_id) AS avg_total
    FROM orders
)
SELECT order_id, total
FROM customer_avg
WHERE total > avg_total;

EXISTS Over IN — The Short-Circuit That Saves Milliseconds

IN with a subquery looks clean. But the database materializes the entire subquery result set into a temporary structure before checking each row. For a subquery returning 50K rows, that's 50K values in memory before the first row comparison.

EXISTS is lazy. It returns TRUE the instant it finds a single matching row. No materialization. No temp table. The planner can also use semi-join strategies, pushing predicates down into the subquery before execution.

The performance gap widens with NULL handling. IN behaves weirdly with NULLs — an empty set combined with NULL returns FALSE, not NULL. EXISTS avoids this entire class of bugs.

Exception: If the inner query has a DISTINCT or aggregate, IN might match your intent better. But even then, consider rewriting as a JOIN with DISTINCT once and test both paths.

// io.thecodeforge — database tutorial

-- Slow: IN materializes 50K IDs
SELECT customer_name, email
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE order_total > 1000
);

-- Fast: EXISTS short-circuits
SELECT customer_name, email
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
    AND o.order_total > 1000
);