Lesson 2.5: Putting It Together - Query Performance

Example: Optimizing a Slow Query

Initial query (slow):

SELECT
    d.content,
    d.title,
    d.category,
    e.vector <=> query_vector AS similarity
FROM documents d
JOIN embeddings e ON d.id = e.document_id
WHERE d.user_id = 123
    AND e.vector <=> '[...]'::vector < 0.8
ORDER BY similarity
LIMIT 10;

-- Time: 30 seconds (unacceptable)

EXPLAIN analysis:

EXPLAIN (ANALYZE, BUFFERS) [query];

-- Output shows:
-- Seq Scan on embeddings (1,000,000 rows scanned)
-- Nested Loop join
-- No index on user_id

Optimization Steps

1. Add indexes:

-- Index on documents.user_id
CREATE INDEX documents_user_id_idx ON documents(user_id);

-- Index on embeddings.vector
CREATE INDEX embeddings_vector_idx ON embeddings
USING ivfflat (vector vector_cosine_ops);

-- Time: 5 seconds (better, but still slow)

2. Rewrite query (push down filter):

-- Filter documents FIRST, then join
WITH filtered_docs AS (
    SELECT id, content, title, category
    FROM documents
    WHERE user_id = 123  -- Uses index, filters to 100 rows
)
SELECT
    d.content,
    d.title,
    d.category,
    e.vector <=> query_vector AS similarity
FROM filtered_docs d
JOIN embeddings e ON d.id = e.document_id
WHERE e.vector <=> '[...]'::vector < 0.8
ORDER BY similarity
LIMIT 10;

-- Time: 500ms (60x faster)

3. Add covering index:

-- Include title, category in index (avoid heap lookup)
CREATE INDEX documents_user_cover_idx ON documents(user_id)
INCLUDE (title, category);

-- Time: 150ms (200x faster than original)

Performance Checklist for AI Queries

Index checklist:

• WHERE clause columns indexed
• JOIN columns indexed
• ORDER BY columns indexed (or in SELECT index)
• Vector columns have appropriate index (IVFFlat/HNSW)

Query checklist:

• Filter early (push down WHERE clauses)
• Limit result set (use LIMIT)
• Avoid SELECT * (only fetch needed columns)
• Use covering indexes when possible

Schema checklist:

• Foreign keys indexed (for joins)
• Appropriate data types (BIGINT vs INT, TEXT vs VARCHAR)
• JSONB for semi-structured data (not TEXT)
• Partitioning for very large tables

Module 2 Summary

Key Concepts

1. Storage engines determine how data is physically stored (heap, B-tree, LSM, columnar)
2. B-trees are the fundamental index structure—understand them deeply
3. Query planner generates execution plans based on statistics
4. EXPLAIN is your best friend for debugging slow queries
5. Caching happens at multiple levels: shared buffers, OS cache, application layer
6. Optimization is iterative: measure, add indexes, rewrite query, measure again

What You Learned

• How PostgreSQL stores data on disk (MVCC, TOAST)
• How B-tree indexes work and when to use them
• How to read and interpret EXPLAIN output
• How caching speeds up queries
• How to optimize slow queries systematically

What's Next

Module 3 dives deeper into indexing strategies for AI workloads:

• Covering indexes for index-only scans
• Partial indexes to reduce size
• Expression indexes for computed columns
• GIN/GiST for full-text and JSONB
• Vector indexes (IVFFlat, HNSW) for similarity search

---

Continue to Module 3: Indexing for AI Era