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
- Storage engines determine how data is physically stored (heap, B-tree, LSM, columnar)
- B-trees are the fundamental index structure—understand them deeply
- Query planner generates execution plans based on statistics
- EXPLAIN is your best friend for debugging slow queries
- Caching happens at multiple levels: shared buffers, OS cache, application layer
- 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
التالي · الدرس 15 من 45
B-Tree Architecture Deep Dive
قراءة 2 دقيقة
أنهيت 14 من 45 درسًا

اسأل Byte عن هذا الدرس
محادثة خاصة حول هذا الدرس. قرأ Byte الدرس كاملًا، ويجيب خلال ثوانٍ، ويُظهر القسم الذي تأتي منه كل إجابة. يمكنك السؤال بأي لغة.
الأسئلة والأجوبة
أسئلة عامة حول هذا الدرس. يجيب AI Tutor فورًا بلغتك، ويراجع فريق FreeAcademy الإجابات، ويمكن لكل المتعلمين قراءتها.

