Writing Clean SQL: 7 Anti-Patterns That Murder Query Performance

Most application developers interact with relational databases through ORMs like Hibernate, Prisma, or SQLAlchemy. While ORMs provide immense development velocity, they also generate abstracted SQL queries that look harmless in development but cripple production performance under load.

Even when writing raw SQL, subtle anti-patterns can trick query planners into discarding indexes, inflating memory buffers, and scanning millions of disk blocks.

Here are the most dangerous SQL anti-patterns and how to fix them for instant query optimization.

1. Wrapping Indexed Columns in Functions (Non-SARGable Queries)

The Anti-Pattern:

SELECT id, email FROM users WHERE EXTRACT(YEAR FROM created_at) = 2026;

Why it fails: When you wrap a column inside a function (EXTRACT(), LOWER(), DATE()), PostgreSQL cannot traverse the standard B-Tree index on created_at. It must evaluate the function on every single row in the table via a sequential scan.

The Clean Fix: Make the condition Search-Argument-able (SARGable) by keeping the column bare:

SELECT id, email 
FROM users 
WHERE created_at >= '2026-01-01 00:00:00+00' 
  AND created_at < '2027-01-01 00:00:00+00';

Now, PostgreSQL can execute a fast index range scan in sub-milliseconds.

2. The SELECT * Trap

The Anti-Pattern:

SELECT * FROM orders WHERE status = 'SHIPPED';

Why it fails:

• It prevents Index-Only Scans. If an index contains (status, order_date, total_amount), PostgreSQL could answer queries purely from RAM without ever touching the heap table. SELECT * forces PostgreSQL to perform random I/O heap fetches for every matching row.
• Storing large TEXT or JSONB columns in the table wastes network bandwidth and application memory when only 2 fields are needed.

The Clean Fix: Explicitly project only required attributes:

SELECT id, customer_id, total_amount FROM orders WHERE status = 'SHIPPED';

3. Implicit Type Casting

The Anti-Pattern:

-- Assume phone_number is a VARCHAR(20) column with a B-tree index
SELECT id, name FROM customers WHERE phone_number = 9876543210;

Why it fails: The constant 9876543210 is parsed as a numeric integer. PostgreSQL will not cast the constant to string; instead, it casts the column on every row: WHERE phone_number::bigint = 9876543210. The index is instantly discarded!

The Clean Fix: Always match types strictly:

SELECT id, name FROM customers WHERE phone_number = '9876543210';

4. Leading Wildcards with LIKE '%term'

The Anti-Pattern:

SELECT id, title FROM articles WHERE title LIKE '%concurrency%';

Why it fails: A B-tree index is sorted from left to right. A leading wildcard means any character can appear first, rendering index traversal impossible.

The Clean Fix: For substring and full-text searches, create a Trigram (trgm) or GIN index:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_articles_title_trgm ON articles USING GIN (title gin_trgm_ops);

SELECT id, title FROM articles WHERE title ILIKE '%concurrency%';

5. Pagination with High-Offset OFFSET 100000

The Anti-Pattern:

SELECT id, title FROM products ORDER BY id LIMIT 20 OFFSET 100000;

Why it fails: To skip 100,000 rows, the database must generate all 100,020 rows, sort them, and throw away the first 100,000. As users click deeper into pagination, latency spikes linearly.

The Clean Fix: Use Keyset Pagination (Cursor Pagination):

SELECT id, title 
FROM products 
WHERE id > 100000 
ORDER BY id ASC 
LIMIT 20;

This performs a single direct B-Tree index seek, returning in less than 1 millisecond regardless of whether you are on page 1 or page 50,000.