A query can return fast and still be wrong. A query can return correct rows and still scan 80 million of them to find ten. Both failures trace back to a small set of repeatable SQL anti-patterns. Most of them pass code review because the syntax is valid and the test dataset is small. This guide covers 12 anti-patterns, grouped by whether they hurt performance or correctness, with a fix for each and a script to find them in production.
Quick Takeaways
- Non-sargable predicates (functions on columns, leading wildcards, implicit casts) stop the optimizer from using indexes. Rewrite the predicate, not the index.
- NULL semantics and join logic cause silent wrong answers.
NOT IN,LEFT JOINplus aWHEREfilter, andDISTINCTover fan-out joins are the usual culprits. - Pagination and row-by-row access patterns (
OFFSET, N+1) degrade linearly with data size. Use keyset pagination and set-based queries. - Verify every fix with EXPLAIN (ANALYZE, BUFFERS) rather than intuition.
| Category | Anti-Patterns | Typical Symptom |
|---|---|---|
| Index defeat | #2, #3, #4 | Seq Scan on indexed column |
| Excess work | #1, #5, #6, #7, #8, #12 | High I/O, slow at scale |
| Wrong results | #9, #10, #11 | Missing or duplicated rows |
Code examples use PostgreSQL syntax. The principles apply to MySQL, SQL Server, and other engines, with small dialect differences.
Performance Anti-Patterns
1. Using SELECT * in Production Queries
SELECT * pulls every column, including wide TEXT/JSONB fields. It blocks index-only scans, inflates network transfer, and breaks downstream code when schemas change.
-- Anti-pattern
SELECT * FROM orders WHERE customer_id = 4821;
-- Fix: name the columns you need; a covering index can now serve the query
SELECT order_id, order_date, total_amount
FROM orders
WHERE customer_id = 4821;
-- Covering index (PostgreSQL 11+)
CREATE INDEX idx_orders_cust_cover
ON orders (customer_id) INCLUDE (order_date, total_amount);
With the covering index, the planner can return an Index Only Scan and skip the heap entirely.
2. Wrapping Indexed Columns in Functions
A predicate is sargable (Search ARGument ABLE) when the engine can use an index seek on it. Applying a function to the column forces a full scan, because the index stores raw values, not function outputs.
-- Anti-pattern: index on order_date is ignored
SELECT order_id FROM orders
WHERE DATE_TRUNC('month', order_date) = '2026-10-01';
-- Fix: rewrite as a range on the raw column
SELECT order_id FROM orders
WHERE order_date >= '2026-10-01'
AND order_date < '2026-11-01';
If you cannot rewrite the predicate, build an expression index: CREATE INDEX ON users (LOWER(email));.
3. Leading Wildcards in LIKE
LIKE '%smith' cannot use a B-tree index because the search has no fixed prefix. LIKE 'smith%' can.
| Pattern | B-tree Index Usable | Alternative |
|---|---|---|
LIKE 'smith%' |
Yes | None needed |
LIKE '%smith' |
No | Index on REVERSE(col) |
LIKE '%smith%' |
No | pg_trgm GIN index or full-text search |
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_customers_name_trgm
ON customers USING gin (name gin_trgm_ops);
-- '%smith%' now uses the trigram index
4. Implicit Type Conversions
Comparing a VARCHAR column to a numeric literal (or mismatched types in a join) makes the engine cast every row. The index on the column goes unused.
-- Anti-pattern: phone is VARCHAR, literal is INT
SELECT * FROM contacts WHERE phone = 5551234567;
-- Fix: match the column type
SELECT contact_id FROM contacts WHERE phone = '5551234567';
Check join keys too. A BIGINT joined to an INT or a UUID stored as text produces silent casts. In SQL Server, the plan shows a CONVERT_IMPLICIT warning.
5. Deep OFFSET Pagination
OFFSET 500000 LIMIT 20 reads and discards 500,000 rows before returning 20. Cost grows linearly with page depth. Keyset pagination seeks directly to the position.
-- Anti-pattern
SELECT order_id, created_at FROM orders
ORDER BY created_at DESC, order_id DESC
LIMIT 20 OFFSET 500000;
-- Fix: keyset (pass the last row's values from the previous page)
SELECT order_id, created_at FROM orders
WHERE (created_at, order_id) < ('2026-09-14 10:22:01', 981234)
ORDER BY created_at DESC, order_id DESC
LIMIT 20;
| Method | Page 1 Cost | Page 25,000 Cost | Random Page Jump | Stable Under Inserts |
|---|---|---|---|---|
| OFFSET | Low | Very high | Yes | No |
| Keyset | Low | Low | No | Yes |
Back the keyset query with a composite index on (created_at DESC, order_id DESC).
6. Correlated Subqueries in the SELECT List
A correlated subquery can execute once per outer row. Modern optimizers sometimes flatten it, but you cannot rely on that.
-- Anti-pattern: runs a lookup per customer
SELECT c.customer_id,
(SELECT SUM(o.total_amount)
FROM orders o
WHERE o.customer_id = c.customer_id) AS lifetime_value
FROM customers c;
-- Fix: aggregate once, then join
SELECT c.customer_id, COALESCE(o.lifetime_value, 0) AS lifetime_value
FROM customers c
LEFT JOIN (
SELECT customer_id, SUM(total_amount) AS lifetime_value
FROM orders
GROUP BY customer_id
) o USING (customer_id);
7. The N+1 Query Problem
The application fetches a list, then fires one query per item. At 1,000 items, that is 1,001 round trips. Network latency dominates, not query time.
# Anti-pattern: 1 + N queries
orders = conn.execute("SELECT order_id FROM orders LIMIT 1000").fetchall()
for (order_id,) in orders:
items = conn.execute(
"SELECT * FROM order_items WHERE order_id = %s", (order_id,)
).fetchall()
# Fix: one set-based query using ANY() with a parameter array
order_ids = [row[0] for row in orders]
items = conn.execute(
"""
SELECT order_id, product_id, quantity
FROM order_items
WHERE order_id = ANY(%s)
""",
(order_ids,),
).fetchall()
In ORMs, look for eager loading options (selectinload in SQLAlchemy, includes in Rails, select_related in Django).
8. Filtering Rows in HAVING Instead of WHERE
HAVING filters after aggregation. Conditions that do not depend on aggregates belong in WHERE, where they cut rows before the expensive grouping step.
-- Anti-pattern: groups all regions, then discards
SELECT region, SUM(revenue) AS total
FROM sales
GROUP BY region
HAVING region IN ('EMEA', 'APAC');
-- Fix: filter first
SELECT region, SUM(revenue) AS total
FROM sales
WHERE region IN ('EMEA', 'APAC')
GROUP BY region;
Reserve HAVING for aggregate conditions such as HAVING SUM(revenue) > 10000.
Correctness Anti-Patterns
9. NOT IN with Nullable Columns
If the subquery returns even one NULL, NOT IN evaluates to UNKNOWN for every row and returns zero results. This bug is silent.
-- Anti-pattern: returns nothing if any blocked.customer_id is NULL
SELECT customer_id FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM blocked);
-- Fix: NOT EXISTS handles NULLs correctly and optimizes to an anti-join
SELECT c.customer_id FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM blocked b WHERE b.customer_id = c.customer_id
);
The same logic explains why col = NULL never matches. Use IS NULL, or IS NOT DISTINCT FROM for null-safe equality.
10. Filtering the Right Table of a LEFT JOIN in WHERE
A WHERE condition on the right-side table discards the NULL rows the LEFT JOIN produced. The query silently becomes an INNER JOIN.
-- Anti-pattern: customers with no 2026 orders vanish
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-01-01';
-- Fix: move the condition into the ON clause
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
AND o.order_date >= '2026-01-01';
11. Masking Join Fan-Out with DISTINCT
Joining two one-to-many tables multiplies rows. Adding DISTINCT hides the duplicates but not the damage. Aggregates computed over the inflated rows are wrong, and the sort or hash for DISTINCT is expensive.
-- Anti-pattern: each order repeats once per payment, so SUM is inflated
SELECT o.customer_id, SUM(oi.price * oi.qty) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN payments p ON p.order_id = o.order_id
GROUP BY o.customer_id;
-- Fix: pre-aggregate each branch to one row per order, then join
WITH items AS (
SELECT order_id, SUM(price * qty) AS item_total
FROM order_items GROUP BY order_id
),
pay AS (
SELECT order_id, SUM(amount) AS paid
FROM payments GROUP BY order_id
)
SELECT o.customer_id, SUM(i.item_total) AS revenue, SUM(pay.paid) AS paid
FROM orders o
JOIN items i ON i.order_id = o.order_id
LEFT JOIN pay ON pay.order_id = o.order_id
GROUP BY o.customer_id;
Rule of thumb: if you reach for DISTINCT to fix duplicates, find the join that created them.
12. Using UNION When UNION ALL Is Enough
UNION deduplicates, which forces a sort or hash across the entire combined result. If the inputs cannot overlap, that work is wasted.
-- Anti-pattern: partitions never overlap, but dedup still runs
SELECT * FROM sales_2025
UNION
SELECT * FROM sales_2026;
-- Fix
SELECT order_id, amount FROM sales_2025
UNION ALL
SELECT order_id, amount FROM sales_2026;
Real-World Scenarios
Scenario 1: Dashboard timeouts on an e-commerce orders table (180M rows). A “monthly revenue” panel used DATE_TRUNC on order_date (#2) inside a correlated subquery (#6). Rewriting it as a range predicate plus a pre-aggregated join let the planner use the date index. The Seq Scan became an Index Range Scan, cutting runtime from minutes to under a second.
Scenario 2: Churn report undercounts inactive customers. An analyst joined customers to subscriptions with a LEFT JOIN, then filtered WHERE subscriptions.status = 'cancelled' (#10). Customers who never subscribed disappeared from the denominator. Moving the filter to ON restored them, and the churn rate corrected itself.
Scenario 3: Inflated revenue in a finance export. A report joined orders to line items and shipments, then wrapped everything in DISTINCT (#11). Totals were off by 20–40% on multi-shipment orders. Pre-aggregating each child table fixed both accuracy and runtime.
Detect Anti-Patterns in Production
Do not guess which queries hurt. Rank them with pg_stat_statements, then run EXPLAIN (ANALYZE, BUFFERS) on the worst offenders.
import psycopg # psycopg 3
TOP_QUERIES_SQL = """
SELECT
LEFT(query, 120) AS query_snippet,
calls,
ROUND(total_exec_time::numeric, 1) AS total_ms,
ROUND(mean_exec_time::numeric, 2) AS mean_ms,
rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;
"""
def top_slow_queries(dsn: str) -> list[tuple]:
"""Return the 15 queries consuming the most cumulative execution time."""
with psycopg.connect(dsn) as conn:
with conn.cursor() as cur:
cur.execute(TOP_QUERIES_SQL)
return cur.fetchall()
def explain(dsn: str, query: str) -> str:
"""Run EXPLAIN ANALYZE; inspect output for Seq Scan, Sort spills, and row-estimate gaps."""
with psycopg.connect(dsn) as conn:
with conn.cursor() as cur:
cur.execute(f"EXPLAIN (ANALYZE, BUFFERS) {query}")
return "\n".join(row[0] for row in cur.fetchall())
if __name__ == "__main__":
for row in top_slow_queries("postgresql://user:pass@localhost/mydb"):
print(row)
Enable the extension first with CREATE EXTENSION pg_stat_statements; and add it to shared_preload_libraries. Sort by total_exec_time to find cumulative cost, and by mean_exec_time to find individually slow statements. A high-call, low-latency query (typical of N+1) shows up only in the first ranking.
In plans, watch for these signals:
- Seq Scan on a large table with a selective filter points to anti-patterns #2, #3, #4.
- Rows Removed by Filter in the millions points to #5 or #8.
- Estimated rows vs. actual rows off by 10x or more means stale statistics. Run
ANALYZE. - Sort Method: external merge means the sort spilled to disk, often caused by #11 or #12.
FAQ
What is a SQL anti-pattern?
A SQL anti-pattern is a query design that looks valid but degrades performance or produces incorrect results. Common examples are SELECT *, functions on indexed columns, and NOT IN against nullable subqueries.
Why is my query slow even though the column has an index?
The predicate is probably non-sargable. Functions on the column, leading wildcards in LIKE, and implicit type casts all prevent index use. Rewrite the condition so the raw column is compared directly, or create an expression index.
Is SELECT DISTINCT bad for performance?
It adds a sort or hash aggregation over the full result. It also hides join fan-out bugs that corrupt aggregates. Use it when you truly need unique values, not to repair duplicates from a faulty join.
How do I find slow SQL queries in production?
Enable pg_stat_statements (PostgreSQL), Query Store (SQL Server), or the slow query log with Performance Schema (MySQL). Rank by total execution time, then inspect plans with EXPLAIN ANALYZE.



