1. Leading wildcard LIKE ('%value') forces a full table scan — avoid for large tables.

2. Trailing wildcard LIKE ('value%') CAN use a B-tree index — sargable.

3. NOT IN with a subquery that may return NULL returns zero rows — use NOT EXISTS instead.

4. OR conditions on different indexed columns prevent index usage — consider UNION ALL.

5. IN lists with many values (hundreds+) may be slower than a JOIN to a temp table.

6. IS NULL checks can use partial indexes in PostgreSQL for sparse nullable columns.

7. NOT operator often negates index usage — rewrite as positive conditions where possible.

8. BETWEEN on indexed date columns is sargable and efficient.

Trailing wildcard LIKE 'val%' can use a B-tree index

NOT IN with nullable subquery returns 0 rows if any NULL exists — use NOT EXISTS

OR on different indexed columns → consider UNION ALL

BETWEEN on an indexed column performs an efficient index range scan

IN with a large value list may be slower than a JOIN to a temp/CTE

Using = NULL (always UNKNOWN) instead of IS NULL

Forgetting parentheses with OR — AND silently takes precedence

NOT IN silently returning 0 rows due to NULL in subquery

Leading wildcard LIKE causing unexpected full table scans