Advanced SQL Anti-Patterns: Identifying and Refactoring Common Query Mistakes That Kill Performance at Scale

Introduction

You've been staring at a query for twenty minutes. It worked perfectly in development against ten thousand rows. Now it's running in production against fifty million rows and your DBA is sending you Slack messages with increasingly concerning emoji. The query isn't wrong — it returns correct results — but it's burning CPU, holding locks, and causing downstream jobs to pile up behind it like a traffic jam on a Monday morning.

This is the silent killer of data systems: queries that are logically correct but structurally catastrophic at scale. Most SQL anti-patterns don't announce themselves. They hide behind SELECT * and correlated subqueries and implicit type casts, behaving perfectly on a laptop and catastrophically on production data. The gap between "works" and "scales" is where senior data professionals earn their keep.

By the end of this lesson, you'll be able to look at a slow query and diagnose why it's slow — not just guess, but actually read execution plans, identify the structural issue, and refactor it into something that performs correctly at scale. We'll cover eight of the most damaging SQL anti-patterns, walk through real refactoring scenarios, and give you a framework for reviewing your own queries before they become someone else's incident report.

What you'll learn:

• How to read query execution plans to pinpoint the actual performance bottleneck
• Why implicit type coercions, SELECT *, and OR conditions silently destroy index utilization
• How correlated subqueries and NOT IN with NULLs create hidden O(n²) operations
• When CTEs hurt more than they help, and how window functions replace subquery-heavy logic
• How to use EXPLAIN ANALYZE output to validate your refactoring actually worked

---

Prerequisites

This lesson assumes you're comfortable writing complex SQL queries — joins, subqueries, aggregations, and basic indexing concepts. You should have access to PostgreSQL 14+ (most examples will work in MySQL 8+ and SQL Server 2019+ with minor syntax adjustments; we'll note deviations). Familiarity with the concept of a query execution plan is helpful but not required — we'll cover what you need as we go.

---

Reading Execution Plans Before Touching a Single Line of Code

Here's the single most important rule for query optimization: diagnose before you prescribe. Developers who jump straight into "optimizing" a query without reading the execution plan are the SQL equivalent of a doctor prescribing medication before running tests. You might get lucky. You might make things dramatically worse.

In PostgreSQL, you have two tools:

-- EXPLAIN shows the planned execution without running the query
EXPLAIN SELECT customer_id, sum(order_total)
FROM orders
WHERE status = 'completed'
  AND created_at >= '2024-01-01'
GROUP BY customer_id;

-- EXPLAIN ANALYZE actually runs the query and shows real vs. estimated rows
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT customer_id, sum(order_total)
FROM orders
WHERE status = 'completed'
  AND created_at >= '2024-01-01'
GROUP BY customer_id;

Warning: EXPLAIN ANALYZE runs the query. Don't use it on INSERT, UPDATE, or DELETE in production without wrapping it in a transaction you'll roll back. Use BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;.

The output will look intimidating at first. Here's what to focus on:

Seq Scan vs. Index Scan: A sequential scan means Postgres is reading every row in the table. This is fine for small tables or when you're fetching most of the data anyway. It's catastrophic when you expect a small result set from a large table.

Rows Estimate vs. Actual Rows: When these diverge significantly — say the planner estimated 100 rows and got 500,000 — your statistics are stale or you have a data distribution problem. Run ANALYZE orders; to refresh statistics.

Cost numbers: The (cost=startup_cost..total_cost) values are in arbitrary planner units. What matters is relative cost — if one node shows cost=0..150000 and another shows cost=0..1200, the first one is doing the heavy lifting.

Loops: A node executing 50,000 times shows up as loops=50000. This is the signature of a correlated subquery or a nested loop join on a large table. We'll come back to this.

Get comfortable running EXPLAIN ANALYZE on every slow query before and after your changes. The plan is your ground truth.

---

Anti-Pattern 1: The Implicit Type Cast That Kills Your Index

This one is subtle, insidious, and responsible for more performance disasters than developers realize. Consider this schema:

CREATE TABLE user_events (
    event_id    BIGINT PRIMARY KEY,
    user_id     VARCHAR(36),  -- Stores UUIDs as strings: '550e8400-e29b-41d4-a716-446655440000'
    event_type  VARCHAR(50),
    occurred_at TIMESTAMPTZ,
    payload     JSONB
);

CREATE INDEX idx_user_events_user_id ON user_events(user_id);

A developer writes this query, passing the user_id as an integer because that's how it's stored in the application layer's cache:

SELECT event_type, occurred_at
FROM user_events
WHERE user_id = 550440000;  -- Passing an integer against a VARCHAR column

Now look at the execution plan: you'll see a sequential scan on user_events despite the index existing. PostgreSQL has to cast every row's user_id to an integer to compare it, which prevents index usage entirely.

The same problem appears more subtly in date comparisons:

-- This silently casts occurred_at to DATE for every row, killing the index
WHERE DATE(occurred_at) = '2024-06-15'

-- This works with an index on occurred_at
WHERE occurred_at >= '2024-06-15 00:00:00'
  AND occurred_at < '2024-06-16 00:00:00'

The rule is: functions applied to indexed columns on the left side of a condition disable that index. This includes UPPER(), LOWER(), TRIM(), DATE(), CAST(), and any arithmetic.

The fix is to move the transformation to the right side of the comparison, or use a functional index:

-- Option 1: Transform the right side
WHERE occurred_at >= '2024-06-15'::date
  AND occurred_at < ('2024-06-15'::date + INTERVAL '1 day')

-- Option 2: Create a functional index if you truly need to query by DATE()
CREATE INDEX idx_user_events_date ON user_events(DATE(occurred_at));

-- Now this query can use the functional index
WHERE DATE(occurred_at) = '2024-06-15'

In SQL Server, this manifests differently. Implicit conversions between NVARCHAR and VARCHAR cause the same problem, and the execution plan will show a warning icon on the affected operator. SQL Server's execution plans are more explicit about conversion warnings — use them.

---

Anti-Pattern 2: Correlated Subqueries Creating Hidden O(n²) Operations

A correlated subquery is one that references columns from the outer query, forcing it to re-execute for every row of the outer query. Here's a classic example — finding each customer's most recent order:

-- The anti-pattern: correlated subquery
SELECT
    c.customer_id,
    c.email,
    (
        SELECT MAX(o.created_at)
        FROM orders o
        WHERE o.customer_id = c.customer_id  -- correlated reference
    ) AS last_order_date
FROM customers c
WHERE c.account_status = 'active';

If you have 200,000 active customers and 5,000,000 orders, this query executes the subquery 200,000 times. Each execution scans the orders table for matching rows. Even with a good index on orders.customer_id, you're looking at 200,000 index lookups. The execution plan will show loops=200000 on the subquery node.

The fix is almost always a window function or a JOIN with aggregation:

-- Option 1: Window function (cleaner, more readable)
SELECT
    c.customer_id,
    c.email,
    MAX(o.created_at) OVER (PARTITION BY o.customer_id) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.account_status = 'active';

-- Option 2: Pre-aggregate and JOIN (often faster for large aggregations)
SELECT
    c.customer_id,
    c.email,
    latest.last_order_date
FROM customers c
LEFT JOIN (
    SELECT customer_id, MAX(created_at) AS last_order_date
    FROM orders
    GROUP BY customer_id
) latest ON c.customer_id = latest.customer_id
WHERE c.account_status = 'active';

The second option pre-aggregates the entire orders table once, then does a single hash join. The database does one pass over orders instead of 200,000 passes. The execution plan will show the subquery executing once with loops=1.

Tip: Window functions don't always beat pre-aggregation JOINs on performance. Window functions often require the engine to sort or partition the full joined dataset, which can be expensive when the join produces a lot of rows before aggregation. Test both and read the plan.

Now for a harder variant — finding the full row of the most recent order per customer, not just the date:

-- Anti-pattern: N+1 correlated subquery for full row
SELECT *
FROM orders o
WHERE o.created_at = (
    SELECT MAX(o2.created_at)
    FROM orders o2
    WHERE o2.customer_id = o.customer_id
);

This is the classic "greatest-N-per-group" problem. The correlated subquery runs once per row in the outer scan. The correct approach uses a window function with DISTINCT ON (PostgreSQL) or ROW_NUMBER():

-- PostgreSQL: DISTINCT ON for greatest-N-per-group
SELECT DISTINCT ON (customer_id)
    order_id, customer_id, created_at, order_total, status
FROM orders
ORDER BY customer_id, created_at DESC;

-- ANSI SQL: ROW_NUMBER() works in PostgreSQL, MySQL 8+, SQL Server
SELECT order_id, customer_id, created_at, order_total, status
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY created_at DESC
        ) AS rn
    FROM orders
) ranked
WHERE rn = 1;

The ROW_NUMBER() approach scans orders exactly once, applies the partitioning and ranking in a single pass, and filters the result. No loops.

---

Anti-Pattern 3: NOT IN With Nullable Columns — The Silent Data Disappearance

This is one of the most dangerous anti-patterns because it doesn't cause a performance problem — it causes a correctness problem that only surfaces with certain data distributions.

-- Find customers who have never placed an order
SELECT customer_id, email
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id FROM orders
);

If every row in orders.customer_id is NOT NULL, this query is correct. But the moment a single NULL exists in orders.customer_id — perhaps from a guest checkout row or a data import issue — this query returns zero rows.

Here's why: NOT IN is evaluated using three-valued logic. SQL doesn't just evaluate TRUE or FALSE — it also evaluates UNKNOWN. When you compare any value to NULL, the result is UNKNOWN. Because NOT IN requires all comparisons to be NOT EQUAL (i.e., TRUE), a single UNKNOWN poisones the entire set, and the WHERE clause filters out every row.

-- Demonstration of the problem
SELECT 1 WHERE 5 NOT IN (1, 2, NULL);
-- Returns zero rows. 5 is not in {1, 2}, but the NULL makes the whole thing UNKNOWN.

This is a silent failure mode. The query runs, returns results, and the results are wrong. You might not notice for weeks.

The correct replacements:

-- Option 1: NOT EXISTS (handles NULLs correctly, often faster)
SELECT c.customer_id, c.email
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

-- Option 2: LEFT JOIN / IS NULL (the most widely understood)
SELECT c.customer_id, c.email
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

-- Option 3: If you know the column is NOT NULL, add that guarantee
SELECT customer_id, email
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);

NOT EXISTS is almost always the right choice. It short-circuits on the first matching row (it doesn't need to scan the entire subquery set), it handles NULLs correctly by design, and modern optimizers can convert it to an efficient anti-join.

Warning: The performance profile of NOT EXISTS vs. LEFT JOIN / IS NULL varies by database and version. In PostgreSQL 14+, both are typically optimized to the same anti-join plan. In older MySQL versions, NOT EXISTS was sometimes executed as a correlated subquery. Always check the plan.

---

Anti-Pattern 4: SELECT * in Production Queries

SELECT * feels harmless. It's a convenience for exploration and development. In production at scale, it's a consistent source of unnecessary I/O, network overhead, and optimizer confusion.

The core problem is that SELECT * retrieves every column, including wide columns you don't need. Consider a product catalog table:

CREATE TABLE products (
    product_id      BIGINT PRIMARY KEY,
    sku             VARCHAR(50),
    name            VARCHAR(200),
    description     TEXT,        -- Often kilobytes per row
    category_id     INT,
    price           DECIMAL(10,2),
    inventory_count INT,
    specifications  JSONB,       -- Can be hundreds of KB per row
    images          JSONB,       -- Array of image URLs and metadata
    created_at      TIMESTAMPTZ,
    updated_at      TIMESTAMPTZ,
    search_vector   TSVECTOR     -- Full-text search vector, also wide
);

A query that only needs to display a product listing — name, SKU, price, inventory — but uses SELECT * will pull the entire description, specifications, images, and search_vector columns for every matched row. If you're paginating through 10,000 products, you might be transferring megabytes of data you immediately discard.

Beyond I/O, there's an index coverage problem. PostgreSQL can satisfy queries entirely from an index (called an index-only scan) when all requested columns are present in the index. SELECT * makes this impossible because no index covers all columns.

-- Anti-pattern
SELECT *
FROM products
WHERE category_id = 42
  AND inventory_count > 0
ORDER BY name;

-- Better: Only the columns you need
SELECT product_id, sku, name, price, inventory_count
FROM products
WHERE category_id = 42
  AND inventory_count > 0
ORDER BY name;

-- Even better: Create a covering index for this access pattern
CREATE INDEX idx_products_category_listing
ON products(category_id, inventory_count)
INCLUDE (sku, name, price);

-- Now the query can be satisfied entirely from the index

The INCLUDE clause (PostgreSQL 11+, SQL Server) adds columns to the index leaf pages without including them in the index B-tree structure. This means they can be returned without a heap fetch, but they don't affect index ordering.

There's also a schema evolution risk with SELECT *. When a new column is added to the table, every SELECT * query silently starts returning it. If your application code maps query results to objects, this can break deserialization. Explicit column lists are a contract between your query and the table schema.

---

Anti-Pattern 5: OR Conditions That Prevent Index Usage

OR conditions are another silent index killer. Consider a query on an events table with indexes on both user_id and session_id:

-- Planner often chooses a Seq Scan for OR conditions across different columns
SELECT event_id, event_type, occurred_at
FROM user_events
WHERE user_id = 'usr_abc123'
   OR session_id = 'sess_xyz789';

The optimizer faces a challenge here. It could use the index on user_id and get some rows, then use the index on session_id and get more rows, then combine them (a BitmapOr operation in PostgreSQL). But if the selectivity is low — if these two conditions together match a large fraction of the table — the planner might decide a sequential scan is cheaper.

Even when the planner does use a BitmapOr, it's doing more work than it needs to. The pattern that consistently performs better is UNION ALL (or UNION if you need deduplication):

-- Refactored: Each branch can use its own index efficiently
SELECT event_id, event_type, occurred_at
FROM user_events
WHERE user_id = 'usr_abc123'

UNION ALL

SELECT event_id, event_type, occurred_at
FROM user_events
WHERE session_id = 'sess_xyz789'
  AND user_id != 'usr_abc123';  -- Prevent duplicates manually when you know the structure

Or more cleanly, if duplicates are possible and you want them eliminated:

SELECT DISTINCT event_id, event_type, occurred_at
FROM (
    SELECT event_id, event_type, occurred_at
    FROM user_events
    WHERE user_id = 'usr_abc123'

    UNION ALL

    SELECT event_id, event_type, occurred_at
    FROM user_events
    WHERE session_id = 'sess_xyz789'
) combined;

OR within the same column against multiple values is different — use IN instead:

-- Less efficient
WHERE status = 'pending' OR status = 'processing' OR status = 'queued'

-- More efficient, single index lookup
WHERE status IN ('pending', 'processing', 'queued')

---

Anti-Pattern 6: CTEs as Optimization Fences

Common Table Expressions (CTEs) were designed for readability and recursive queries. In PostgreSQL before version 12, every CTE was automatically materialized — meaning it was computed once, results stored in a temporary result set, and the query planner could not push predicates into it or optimize across the CTE boundary. This made CTEs an inadvertent optimization fence.

PostgreSQL 12+ changed the default to inline CTEs when they're safe to do so, but the behavior still trips people up in three ways:

Problem 1: Pre-12 PostgreSQL (still common in enterprise environments)

-- In PG 11 and earlier, this CTE is materialized regardless of outer predicates
WITH recent_orders AS (
    SELECT order_id, customer_id, order_total, created_at
    FROM orders
    -- No filter here: computes ALL orders into a temp set
)
SELECT *
FROM recent_orders
WHERE created_at >= '2024-01-01';  -- This filter cannot be pushed into the CTE

In PostgreSQL 11, this scans the entire orders table into memory, then filters the result. The fix is to move the filter inside:

WITH recent_orders AS (
    SELECT order_id, customer_id, order_total, created_at
    FROM orders
    WHERE created_at >= '2024-01-01'  -- Filter pushed inside the CTE
)
SELECT *
FROM recent_orders;

Problem 2: Forcing materialization when you actually need it

Sometimes you want materialization — when the CTE's result is referenced multiple times and computing it once is cheaper than computing it N times. In PostgreSQL 12+, you can force materialization:

WITH expensive_aggregation AS MATERIALIZED (
    SELECT
        customer_id,
        COUNT(*) AS order_count,
        SUM(order_total) AS lifetime_value,
        AVG(order_total) AS avg_order_value
    FROM orders
    GROUP BY customer_id
)
-- Reference this twice without recomputing
SELECT c.email, ea.order_count, ea.lifetime_value
FROM customers c
JOIN expensive_aggregation ea ON c.customer_id = ea.customer_id
WHERE ea.order_count > 10

UNION ALL

SELECT 'SUMMARY', COUNT(*), SUM(lifetime_value)
FROM expensive_aggregation
WHERE order_count > 10;

Problem 3: CTE chains that obscure expensive operations

-- Multi-step CTE that looks clean but hides a problem
WITH
user_sessions AS (
    SELECT user_id, session_id, MIN(occurred_at) AS session_start
    FROM user_events
    GROUP BY user_id, session_id
),
session_durations AS (
    SELECT
        user_id,
        session_id,
        session_start,
        LEAD(session_start) OVER (PARTITION BY user_id ORDER BY session_start) AS next_session_start
    FROM user_sessions
),
active_sessions AS (
    SELECT *
    FROM session_durations
    WHERE next_session_start - session_start < INTERVAL '30 minutes'
)
SELECT user_id, COUNT(*) AS session_count
FROM active_sessions
GROUP BY user_id;

This looks elegant. But look at what's happening: user_sessions aggregates billions of events, session_durations applies a window function over millions of sessions, and only then does filtering happen. If you know most sessions are long (over 30 minutes), you're doing enormous work to discard most of it. Filter earlier when possible.

Tip: The NOT MATERIALIZED hint (PostgreSQL 12+) is the explicit equivalent of the old default pre-12 behavior for inlining. Use WITH cte AS NOT MATERIALIZED (...) when you want to ensure the planner can push predicates into the CTE. Use MATERIALIZED when you're referencing the CTE multiple times and want to guarantee a single computation.

---

Anti-Pattern 7: OFFSET Pagination on Large Tables

Offset-based pagination feels natural:

-- Page 1
SELECT order_id, customer_id, created_at, order_total
FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 0;

-- Page 100
SELECT order_id, customer_id, created_at, created_at, order_total
FROM orders
ORDER BY created_at DESC
LIMIT 20 OFFSET 1980;

The problem is that OFFSET 1980 doesn't mean "start at row 1980." It means "read 2000 rows, discard the first 1980, return the last 20." Every page request reads more data than the previous one. By page 500 (OFFSET 9980), you're reading and discarding 9,980 rows to return 20. At scale, with millions of orders, late pages are catastrophically slow.

The execution plan will always show a sequential read (or index scan traversal) up to OFFSET + LIMIT rows, even if only LIMIT rows are returned.

The fix is keyset pagination (also called cursor-based pagination):

-- Page 1: No cursor needed
SELECT order_id, customer_id, created_at, order_total
FROM orders
ORDER BY created_at DESC, order_id DESC  -- order_id breaks ties deterministically
LIMIT 20;

-- Page 2+: Use the last row's values as the cursor
-- Assume the last row from page 1 had created_at = '2024-06-15 14:23:01' and order_id = 500123
SELECT order_id, customer_id, created_at, order_total
FROM orders
WHERE (created_at, order_id) < ('2024-06-15 14:23:01', 500123)
ORDER BY created_at DESC, order_id DESC
LIMIT 20;

With a composite index on (created_at DESC, order_id DESC), this query does exactly the work needed: it seeks to the cursor position in the index and reads the next 20 entries. No rows are discarded. Page 500 is just as fast as page 1.

The trade-offs are real:

• You can't jump to an arbitrary page number (page 47 of 200). Keyset pagination only supports "next" and "previous."
• The cursor must include enough columns to be unique (hence the order_id tiebreaker).
• If records are inserted or deleted between pages, the cursor naturally handles it — you won't skip or double-count rows, unlike offset pagination.

For most real-world use cases — API responses, infinite scroll, report exports — keyset pagination is strictly superior. Offset pagination is appropriate when random page access (user navigating to page 47) is a genuine requirement and the table is small enough that performance is acceptable.

---

Anti-Pattern 8: Unbounded Aggregations on Wide Windows

This last anti-pattern is common in analytics and reporting queries. It occurs when you need rolling aggregations — things like 30-day moving averages, cumulative sums, or period-over-period comparisons — and implement them in ways that force the database to re-scan huge amounts of data repeatedly.

Consider a query for a 7-day rolling revenue sum:

-- Anti-pattern: Correlated subquery for rolling aggregation
SELECT
    d.sale_date,
    d.daily_revenue,
    (
        SELECT SUM(d2.daily_revenue)
        FROM daily_revenue_summary d2
        WHERE d2.sale_date BETWEEN d.sale_date - INTERVAL '6 days' AND d.sale_date
    ) AS rolling_7day_revenue
FROM daily_revenue_summary d
ORDER BY d.sale_date;

If daily_revenue_summary has 1,000 rows (about 3 years of daily data), this correlated subquery runs 1,000 times, scanning up to 7 rows each time. At this scale it's tolerable. At the event level — with millions of rows — this is untenable.

The correct approach uses window functions with a frame specification:

-- Window function with explicit frame: clean and efficient
SELECT
    sale_date,
    daily_revenue,
    SUM(daily_revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7day_revenue,
    AVG(daily_revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_7day_avg,
    SUM(daily_revenue) OVER (
        ORDER BY sale_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_revenue
FROM daily_revenue_summary
ORDER BY sale_date;

The ROWS BETWEEN 6 PRECEDING AND CURRENT ROW frame means "include only the 7 rows before and including the current row." The database computes this in a single sorted pass, maintaining a running window without re-scanning.

Tip: Use ROWS rather than RANGE for most rolling calculations. RANGE BETWEEN 6 PRECEDING AND CURRENT ROW means "rows where the ORDER BY value is within 6 units of the current row," which can produce surprising behavior with ties and requires the ORDER BY column to be numeric or date. ROWS BETWEEN is based purely on physical row position in the sorted result, which is almost always what you want.

For period-over-period comparisons (year-over-year revenue, month-over-month growth), a self-join or LAG is usually the right tool:

-- Year-over-year comparison using LAG
SELECT
    sale_date,
    daily_revenue,
    LAG(daily_revenue, 365) OVER (ORDER BY sale_date) AS revenue_prior_year,
    ROUND(
        100.0 * (daily_revenue - LAG(daily_revenue, 365) OVER (ORDER BY sale_date))
            / NULLIF(LAG(daily_revenue, 365) OVER (ORDER BY sale_date), 0),
        2
    ) AS yoy_growth_pct
FROM daily_revenue_summary
ORDER BY sale_date;

Note the NULLIF(..., 0) in the denominator — this prevents division by zero when prior year revenue was zero, returning NULL instead of an error.

---

Hands-On Exercise

For this exercise, you'll work with a realistic e-commerce schema. Create these tables and populate them with sample data:

-- Schema setup
CREATE TABLE customers (
    customer_id  SERIAL PRIMARY KEY,
    email        VARCHAR(255) NOT NULL,
    account_type VARCHAR(20) NOT NULL DEFAULT 'standard',
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    country_code CHAR(2)
);

CREATE TABLE orders (
    order_id      SERIAL PRIMARY KEY,
    customer_id   INT,          -- Intentionally no FK for this exercise
    status        VARCHAR(20) NOT NULL,
    order_total   DECIMAL(10,2),
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    shipped_at    TIMESTAMPTZ,
    payment_ref   VARCHAR(100)
);

CREATE TABLE order_items (
    item_id    SERIAL PRIMARY KEY,
    order_id   INT NOT NULL,
    product_id INT NOT NULL,
    quantity   INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL
);

-- Generate sample data (PostgreSQL)
INSERT INTO customers (email, account_type, created_at, country_code)
SELECT
    'user_' || i || '@example.com',
    CASE WHEN i % 10 = 0 THEN 'premium' ELSE 'standard' END,
    NOW() - (random() * INTERVAL '730 days'),
    CASE (i % 5)
        WHEN 0 THEN 'US'
        WHEN 1 THEN 'GB'
        WHEN 2 THEN 'DE'
        WHEN 3 THEN 'CA'
        ELSE 'AU'
    END
FROM generate_series(1, 100000) AS i;

INSERT INTO orders (customer_id, status, order_total, created_at, shipped_at)
SELECT
    (random() * 99999 + 1)::INT,
    CASE (i % 6)
        WHEN 0 THEN 'pending'
        WHEN 1 THEN 'processing'
        WHEN 2 THEN 'shipped'
        WHEN 3 THEN 'delivered'
        WHEN 4 THEN 'cancelled'
        ELSE 'refunded'
    END,
    (random() * 500 + 10)::DECIMAL(10,2),
    NOW() - (random() * INTERVAL '365 days'),
    CASE WHEN i % 6 IN (2, 3) THEN NOW() - (random() * INTERVAL '300 days') ELSE NULL END
FROM generate_series(1, 500000) AS i;

-- Introduce some NULLs in customer_id to simulate a real data problem
UPDATE orders SET customer_id = NULL WHERE order_id % 10000 = 0;

Exercise 1: Diagnose and fix the NOT IN NULL trap

Run this query and observe the result count, then fix it:

-- How many customers have never placed an order?
SELECT COUNT(*)
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

Expected findings: Because we inserted NULL values into orders.customer_id, this query should return 0 or an unexpectedly low number. Rewrite it using NOT EXISTS and LEFT JOIN / IS NULL and compare the results. Confirm all three give different answers and understand why.

Exercise 2: Fix the correlated subquery

Rewrite this query to eliminate the correlated subquery:

SELECT
    c.customer_id,
    c.email,
    c.account_type,
    (SELECT MAX(o.created_at) FROM orders o WHERE o.customer_id = c.customer_id) AS last_order,
    (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count,
    (SELECT SUM(o.order_total) FROM orders o WHERE o.customer_id = c.customer_id) AS lifetime_value
FROM customers c
WHERE c.account_type = 'premium';

Use EXPLAIN ANALYZE before and after. Compare the total execution time and the loops= value on the subquery node.

Exercise 3: Implement keyset pagination

Implement a paginated query that returns orders in descending date order, 25 per page, using keyset pagination. Demonstrate how to:

• Retrieve page 1
• Use the results of page 1 to construct the page 2 query
• Confirm the approach is O(1) regardless of page depth using EXPLAIN ANALYZE

Exercise 4: Rolling aggregation

Calculate a 30-day rolling sum of order_total for delivered orders, grouped by day. Do it first with a correlated subquery, then refactor to a window function. Measure the difference.

---

Common Mistakes & Troubleshooting

"I added an index but the query is still slow."

A few possibilities: First, the index might not be being used — check the execution plan. If the planner chooses a sequential scan despite your index, it might be because your query condition involves a function on the indexed column (implicit cast, LOWER(), etc.), the table statistics are stale (run ANALYZE tablename), or the planner estimates that the condition will match more than roughly 5-15% of the table, making a sequential scan cheaper. Second, the index exists but is the wrong type — a B-tree index doesn't help with LIKE '%keyword%' searches (use GIN/full-text search). Third, you have a bloated index with a high dead tuple ratio — run VACUUM ANALYZE and check pg_stat_user_indexes to see if the index is actually being scanned.

"My refactored query is faster in development but not in production."

Likely a data distribution problem. Development data is often uniform; production data has hot spots. If 40% of your production orders have status = 'shipped', an index on status won't help for queries filtering on that value — but it works great in dev where statuses are evenly distributed. Also check that your table statistics are fresh on both environments. The planner's decisions are only as good as its statistics.

"The window function query uses more memory than the correlated subquery."

This is a real trade-off. Window functions require the database to sort or hash the input dataset before applying the window, which can require significant working memory (controlled by work_mem in PostgreSQL). If work_mem is too low, the sort spills to disk, negating the performance gain. Correlated subqueries use minimal memory (they process one row at a time) but create more I/O. For very large datasets with memory-constrained environments, sometimes a pre-aggregated JOIN is better than either approach.

"UNION ALL produced duplicate rows I didn't expect."

When you refactor OR to UNION ALL, you need to explicitly handle the case where a row matches both conditions. Design your UNION so the conditions are mutually exclusive, or use UNION (without ALL) to deduplicate, or add a WHERE clause to the second branch excluding rows matched by the first.

"The CTE materialization behavior changed after a PostgreSQL upgrade."

PostgreSQL 12 changed the default CTE behavior from always-materialize to inline-when-safe. If you upgraded from 11 to 12+ and queries changed behavior — sometimes faster, sometimes the query structure matters more — explicitly annotate your CTEs with MATERIALIZED or NOT MATERIALIZED to make the behavior deterministic across versions. Don't rely on the default.

"NOT EXISTS is still slow."

NOT EXISTS should translate to an efficient anti-join. If it's not, the subquery inside might not have a usable index on the correlation column. In the classic example WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id), make sure there's an index on orders.customer_id. Without that index, the inner subquery scans the entire orders table for each customer.

---

The Query Review Checklist

Before promoting any complex query to production, run it through this checklist:

1. Run EXPLAIN ANALYZE — are there any sequential scans on large tables? Any loops= values in the thousands?
2. Check for SELECT * — replace with explicit column lists.
3. Check every WHERE clause condition — is there a function wrapping an indexed column on the left side?
4. Check every NOT IN — can the subquery return NULLs? If so, switch to NOT EXISTS.
5. Check for correlated subqueries — any subquery that references outer query columns should be a refactoring candidate. Is a JOIN or window function more appropriate?
6. Check OR conditions — especially those spanning different columns. Consider UNION ALL.
7. Check pagination — is it offset-based on a large table? Consider keyset pagination.
8. Check CTEs — are filters inside or outside? Is materialization behavior what you expect?
9. Check window function frames — is ROWS vs. RANGE correct for your use case?

---

Summary & Next Steps

SQL anti-patterns are almost never introduced by carelessness. They're introduced by code that was correct in the context where it was written and then hit the wall of scale. The developer who wrote a correlated subquery on a 10,000-row table made a reasonable decision. The problem surfaces when that table grows by two orders of magnitude and nobody revisits the query.

The framework you've built here is diagnostic, not dogmatic. Every anti-pattern has a context where it's the right tool — OFFSET pagination is fine for small datasets where random page access is required, correlated subqueries are sometimes the most readable solution for simple lookups, SELECT * is fine for exploratory analysis. The skill isn't memorizing rules; it's knowing when the rules apply and why.

What you can do now:

• Find three slow queries in your current system and run EXPLAIN ANALYZE on each one. Look for the patterns we covered.
• Audit your NOT IN usages for nullable columns. This is the highest-priority fix — it's a correctness issue, not just a performance issue.
• Refactor one correlated subquery to a window function or pre-aggregated JOIN and measure the difference.

Where to go next:

• Study partial indexes and index-only scans — the next level of index optimization after the basics
• Learn query-level locking behavior and how long-running analytical queries affect OLTP performance
• Explore materialized views for queries that are expensive but whose results don't need to be perfectly real-time
• Dig into PostgreSQL's pg_stat_statements extension to automatically identify your highest-cost queries across the system

The best SQL engineers don't write faster queries by intuition. They write fast queries by understanding what the database actually does when it executes SQL — and that understanding starts with the execution plan.