Query optimization — anti-patterns to avoid

Most slow queries are slow for the SAME few reasons. Master this checklist and you'll outperform 90% of 'just throw hardware at it' advice.

1. Function on an indexed column in WHERE: WHERE YEAR(date) = 2026 defeats the index. Rewrite as range: WHERE date >= '2026-01-01' AND date < '2027-01-01'.

**2. SELECT *** — bandwidth waste, schema-drift risk, defeats index-only scans. List the columns you need.

3. Implicit casts: joining VARCHAR to INT, or comparing 'date' text to a real date, forces a cast on every row → full scan. Keep types consistent.

**4. N+1 queries**: a loop in the app that does `SELECT * FROM orders WHERE customer_id = ?` once per customer, 1000 times. Replace with one join or `WHERE customer_id IN (...)`. Your ORM has 'eager loading' for a reason.

**5. Deep OFFSET pagination**: `LIMIT 10 OFFSET 100000` — engine scans + discards 100k rows. Use keyset pagination (`WHERE id > :last_seen ORDER BY id LIMIT 10`) — O(log N).

6. OR on different columns: WHERE a = 1 OR b = 2 can't use an index on a OR b cleanly. Sometimes a UNION of two indexed queries is faster.

**7. Missing statistics**: the optimizer uses stats (ANALYZE in Postgres, UPDATE STATISTICS in SQL Server) to pick plans. Stale stats → bad plans → slow queries.

8. Forgetting EXPLAIN: always EXPLAIN ANALYZE a slow query. Diagnose before guessing.

9. COUNT(*) on huge tables for pagination: SELECT COUNT(*) FROM big_table is O(N). Cache approximate counts (pg_class.reltuples, information_schema estimates) or show 'more than 10k' instead.

**10. Unnecessary DISTINCT**: often a sign of a duplication bug elsewhere (wrong join cardinality). Investigate before slapping DISTINCT on the query.