Lesson 3.4: Expression Indexes
Indexing Computed Values
-- Problem: Query uses LOWER()
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
-- Regular index won't be used:
CREATE INDEX users_email_idx ON users(email); -- Useless!
-- Solution: Expression index
CREATE INDEX users_email_lower_idx ON users(LOWER(email));
-- Now query uses index
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
Common Expression Index Patterns
1. Case-insensitive searches:
CREATE INDEX products_name_lower_idx ON products(LOWER(name));
-- Fast case-insensitive search
SELECT * FROM products WHERE LOWER(name) = LOWER('iPhone');
2. Date truncation:
-- Index by date (not timestamp)
CREATE INDEX logs_date_idx ON logs(DATE(created_at));
-- Fast daily aggregations
SELECT DATE(created_at), COUNT(*)
FROM logs
GROUP BY DATE(created_at);
3. JSONB field extraction:
-- Index specific JSONB field
CREATE INDEX products_price_idx ON products((metadata->>'price')::numeric);
-- Fast queries on JSON field
SELECT * FROM products
WHERE (metadata->>'price')::numeric < 100;
4. Full-text search (tsvector):
-- Index for full-text search
CREATE INDEX documents_search_idx ON documents
USING GIN(to_tsvector('english', content));
-- Fast text search
SELECT * FROM documents
WHERE to_tsvector('english', content) @@ to_tsquery('AI & agents');
Expression Indexes for AI
Embedding dimensions:
-- Index specific dimensions of vector
CREATE INDEX embeddings_dim_0_10_idx ON embeddings(
(vector[1:10])::vector(10)
);
-- Fast queries on first 10 dimensions (rare use case)
Feature normalization:
-- Index normalized features
CREATE INDEX features_normalized_idx ON user_features(
(feature_value / GREATEST(feature_max, 1))
);
Key Takeaways
- Expression indexes index the result of a function or expression
- Query must use the exact same expression to use the index
- Essential for case-insensitive searches, date truncation, and JSONB queries
- Full-text search requires GIN index on tsvector expressions
- More expensive to maintain (expression computed on every insert/update)
- Useful for AI workloads that transform features or extract JSON fields
التالي · الدرس 19 من 45
GIN and GiST Indexes
قراءة 2 دقيقة
أنهيت 18 من 45 درسًا

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

