Query Optimisation — Slow Query Patterns and Fixes

Understanding indexes and reading EXPLAIN output is most valuable when applied to real slow query patterns. Most production database performance problems fall into a small number of categories: functions on indexed columns that prevent index use, type mismatches that force implicit casting, OR conditions that create multiple sequential scans, overly-broad LIKE patterns, and the statistics staleness that causes the planner to make catastrophically wrong decisions. This lesson covers each pattern with its diagnosis and fix.

Function Calls on Indexed Columns #

-- ── Pattern: function wraps the indexed column ────────────────────────────────
-- The index stores original values, not the function results
-- Wrapping a column in a function makes the index unusable

-- ❌ Slow — cannot use index on email:
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
SELECT * FROM posts WHERE DATE(created_at) = '2025-08-06';
SELECT * FROM posts WHERE EXTRACT(YEAR FROM created_at) = 2025;
SELECT * FROM posts WHERE LENGTH(title) < 50;

-- ✅ Fast alternatives:

-- Option A: Expression index (most flexible)
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';   -- ✓ uses expression index

-- Option B: Rewrite to avoid the function
-- Instead of DATE(created_at) = '2025-08-06':
SELECT * FROM posts
WHERE created_at >= '2025-08-06'
  AND created_at <  '2025-08-07';   -- range without function ✓

-- Instead of EXTRACT(YEAR FROM created_at) = 2025:
SELECT * FROM posts
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';   -- ✓

-- Option C: Store the computed value as a column
ALTER TABLE users ADD COLUMN email_lower TEXT GENERATED ALWAYS AS (LOWER(email)) STORED;
CREATE INDEX idx_users_email_lower ON users(email_lower);

OR Conditions and Index Use #

-- OR conditions can prevent efficient index use
-- (depends on whether both conditions can use the same index)

-- ❌ Potentially slow: OR between different columns
SELECT * FROM posts
WHERE status = 'published' OR author_id = 5;
-- May do Bitmap Index Scan on two separate indexes and combine
-- Or may do a sequential scan if the combined selectivity is low

-- ✅ Rewrite as UNION ALL (often faster)
SELECT * FROM posts WHERE status = 'published' AND author_id != 5
UNION ALL
SELECT * FROM posts WHERE author_id = 5;

-- ❌ Slow: OR with IS NULL check
SELECT * FROM posts WHERE published_at IS NULL OR published_at < '2025-01-01';

-- ✅ Use COALESCE to avoid OR:
SELECT * FROM posts
WHERE COALESCE(published_at, 'epoch'::TIMESTAMPTZ) < '2025-01-01';

-- ✅ Or use union approach:
SELECT * FROM posts WHERE published_at < '2025-01-01'
UNION ALL
SELECT * FROM posts WHERE published_at IS NULL;

LIKE Pattern Optimisation #

-- B-tree index supports ONLY prefix LIKE patterns
-- ❌ Leading wildcard — index not used:
SELECT * FROM posts WHERE title LIKE '%fastapi%';   -- full sequential scan!
SELECT * FROM posts WHERE title ILIKE '%fastapi%';  -- also sequential scan

-- ✅ Prefix LIKE — B-tree index works:
SELECT * FROM posts WHERE title LIKE 'FastAPI%';    -- uses B-tree index ✓
SELECT * FROM posts WHERE title LIKE 'fastapi%';    -- uses B-tree index ✓

-- ✅ For substring search: use pg_trgm trigram index
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_posts_title_trgm ON posts USING GIN (title gin_trgm_ops);

-- Now LIKE '%fastapi%' and ILIKE '%fastapi%' use the GIN index:
SELECT * FROM posts WHERE title ILIKE '%fastapi%';   -- ✓ uses trigram GIN index

-- ✅ For full-text search: use tsvector
CREATE INDEX idx_posts_fts ON posts
    USING GIN (to_tsvector('english', title || ' ' || body));

SELECT * FROM posts
WHERE to_tsvector('english', title || ' ' || body) @@ plainto_tsquery('english', 'fastapi tutorial');
-- Uses the GIN full-text index ✓

Common Slow Query Patterns and Fixes #

Common Mistakes #

Mistake 1 — Using LIKE ‘%term%’ expecting it to use an index #

❌ Wrong — sequential scan on every search:

SELECT * FROM posts WHERE title LIKE '%fastapi%';   -- scans every row!

✅ Correct — install pg_trgm and create a GIN index:

CREATE EXTENSION pg_trgm;
CREATE INDEX idx_trgm_title ON posts USING GIN (title gin_trgm_ops);
SELECT * FROM posts WHERE title ILIKE '%fastapi%';   -- ✓ uses GIN index

Mistake 2 — Casting mismatches #

❌ Wrong — compares integer ID to string:

SELECT * FROM posts WHERE id = '42';   -- implicit cast may prevent index

✅ Correct — match literal type to column type:

SELECT * FROM posts WHERE id = 42;   -- integer literal matches BIGINT column ✓

Mistake 3 — Not using EXPLAIN ANALYZE regularly in development #

❌ Wrong — writing queries and assuming they are fast:

posts = db.query(Post).filter(Post.title.like(f"%{term}%")).all()
# This generates LIKE '%term%' — sequential scan, but no one checks

✅ Correct — run EXPLAIN ANALYZE on every new query during development to verify index use.