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