Lesson 3.2: Covering Indexes (Index-Only Scans)
The Problem: Heap Fetches
-- Query
SELECT title, category FROM documents WHERE user_id = 123;
-- With regular index on user_id:
-- 1. Index scan finds 100 matching rows
-- 2. 100 random heap page reads to get title, category
-- Time: ~50ms (random I/O is slow)
The Solution: Covering Index
-- Include frequently accessed columns
CREATE INDEX documents_user_cover_idx ON documents(user_id)
INCLUDE (title, category);
-- Now query uses Index-Only Scan:
-- 1. Index scan finds matching rows
-- 2. title, category already in index (no heap access)
-- Time: ~2ms (25x faster)
EXPLAIN output:
EXPLAIN (ANALYZE, BUFFERS)
SELECT title, category FROM documents WHERE user_id = 123;
-- With covering index:
Index Only Scan using documents_user_cover_idx on documents
Index Cond: (user_id = 123)
Heap Fetches: 0 ← No heap access!
Buffers: shared hit=12
When to Use Covering Indexes
Good use cases:
-- 1. Frequent queries with specific column access
CREATE INDEX orders_user_cover_idx ON orders(user_id)
INCLUDE (created_at, total, status);
SELECT created_at, total FROM orders WHERE user_id = 123;
-- 2. Sorting + filtering
CREATE INDEX products_cat_cover_idx ON products(category)
INCLUDE (name, price);
SELECT name, price FROM products
WHERE category = 'electronics'
ORDER BY price DESC
LIMIT 10;
Bad use cases (don't do this):
-- Including too many columns (index becomes huge)
CREATE INDEX bad_idx ON users(email)
INCLUDE (name, address, phone, bio, preferences); -- Bad!
-- Including large columns
CREATE INDEX bad_vector_idx ON documents(category)
INCLUDE (embedding); -- 1536 floats per row? Bad!
Rule: Include small, frequently accessed columns only.
Key Takeaways
- Covering indexes include extra columns to avoid heap fetches
- Index-Only Scans can be 25x faster than regular index scans
- Only include small, frequently queried columns
- Check EXPLAIN for "Heap Fetches: 0" to verify index-only scans
- Tradeoff: Larger index size for faster queries
- Most effective for queries that access few columns repeatedly
التالي · الدرس 17 من 45
Partial Indexes
قراءة 1 دقيقة
أنهيت 16 من 45 درسًا

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

