Top SQL Performance Anti-Patterns in PostgreSQL and MySQL (2026 Guide)

By DevUtils Database Team • November 11, 2026 • 7 min read

As modern web applications scale, database bottlenecks are frequently caused by subtle SQL query anti-patterns that prevent the database optimizer from using indexes. Regardless of whether you run PostgreSQL 16+ or MySQL 8.4+, these persistent coding habits force full table scans, exhaust buffer pools, and trigger transaction timeouts.

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

A SARGable (Search Argument Able) query enables the database engine to perform an efficient index seek. Applying a function or calculation directly to an indexed column invalidates the index: 

-- ❌ Anti-Pattern: Forces full table scan
SELECT id, email, created_at 
FROM users 
WHERE EXTRACT(YEAR FROM created_at) = 2026;

-- ✅ High-Performance Fix: Range filter seeks directly into B-tree index
SELECT id, email, created_at 
FROM users 
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

2. The Deep OFFSET Pagination Problem

Using large OFFSET values in APIs or UI infinite scrolls causes quadratic overhead: 

-- ❌ Anti-Pattern: Scans and discards 100,000 rows before returning 20
SELECT * FROM transactions 
ORDER BY id DESC 
LIMIT 20 OFFSET 100000;

-- ✅ Keyset Pagination (Seek Method): Constant O(1) performance
SELECT * FROM transactions 
WHERE id < 452910 
ORDER BY id DESC 
LIMIT 20;

3. Implicit Type Casting

When a query compares a column of one data type against an incompatible literal, the engine automatically converts every row in the column, disabling index scans: 

-- Assuming account_number is VARCHAR
-- ❌ Forces full scan due to integer-to-string cast per row
SELECT * FROM accounts WHERE account_number = 10048291;

-- ✅ Type-aligned: Instant index lookup
SELECT * FROM accounts WHERE account_number = '10048291';

4. Leading Wildcards in LIKE Searches

A leading wildcard (LIKE '%query') prevents the B-Tree index from traversing branches from left to right.

  • For prefix matches: Use trailing wildcards only: WHERE username LIKE 'dev%'.
  • For substring search: Use PostgreSQL GIN indexes with pg_trgm or MySQL FULLTEXT indexes.

5. The N+1 Query ORM Hazard

ORMs (Prisma, TypeORM, Hibernate) often query parent records, then fire $N$ individual queries in a loop to retrieve related children. Always utilize explicit eager loading (JOIN FETCH or batch IN (...) queries) to collapse round-trips into a single statement.

Format and Validate SQL Queries Client-Side

Need to beautify raw SQL queries, inspect table structures, or validate syntax across PostgreSQL, MySQL, and SQLite without leaking proprietary queries to third-party APIs? Use our free in-browser SQL Formatter & Validator Tool.